Suggestion to possibly improve efficiency of scripts for call data

[#1]

Right, I'm not going to get too detailed, ie talk about the code itself, just a principle.
1) Whilst (most) call charging is based on call durations being rounded up to the next minute, there is no need for this rounding to be done for unconfirmed call data. As the raw data will be supplied showing call duration to the (1/10?) second, this can simply be displayed in the appropriate place, ie extra coding to 'display' rounded durations is not needed here.
2) Running script(s) for confirmed call data would be conditional if/where necessary on the script for unconifrmed call data having been run.
Without anyone going into detail, I appreciate that there may be very good reasons for doing/not doing certain things, and I'm not trying to teach granny to suck eggs, just sometimes there's a case of can't see the wood for the trees, and the main intention is to provoke some thought (which may already have happened).
3) Is there any need to write code to 'display' a rounded duration for confirmed calls. Fixed Fee call duration should never be rounded up to a minute in my view, and I would personally have a preference for none of the durations to be 'displayed' rounded, whilst a rounded figure would be used in the cost calculation of non ff calls.

[#2]

Ok, based on a little (but probably out of date) database knowledge, I suspect that the problem is two fold:
First, there are more and more call records to process each day.
Second, as the database tables grow in size, it takes longer and longer to insert each new record. There is a comment in the service status that mentions archiving call data and I suspect this is an attempt to reduce the size of the main database tables.
I can see why this is going to take some time to sort out although I would have thought that some volume (stress) testing would have been performed prior to the original implementation. This would have provided information that could have been used to predict the limits of the current database and scripts. I suspect that the IT guys were being pushed to deliver this asap and so the stress testing was dropped.
Hindsight is a wonderful thing !
Bob

[#3]

Guys,
Ypu're both pretty close to the mark, with a little added complexity just to make the situation worse!
The database tables do need cleaning up there's no doubt about that.
There has been a project on our roadmap to do this for some time, although as an internal project for the most part it has suffered due to customer facing stuff.
Unfortunately we have to process the unconfirmed call data regardless, as a lot of it is intended to reduce fraud (we make it available to customers simply 'cos we're processing it anyway and it provides a tangible benefit to users to see this data).
As such we need to combine database archiving and improving script efficiency. This is what we're currently working on, but due to the way all of our systems are integrated into Workplace this is not a quick or simple piece of work.
I'll update SS first thing tomorrow.

[#4]

"I'll update SS first thing tomorrow."
Does that mean (as the message was posted after midnight)  SS will be updated on Sunday? Or perhaps first thing when Mand starts a late shift on Saturday?
The SS is still showing that the next update is due at 04/07/2008 @ 18:00 after James hoped to have an update "later today" (i.e. Friday)
I doubt that no news is good news.

[#5]

Quote from: alanf

Does that mean (as the message was posted after midnight)  SS will be updated on Sunday? Or perhaps first thing when Mand starts a late shift on Saturday?

AFAIK, mand isn't in the office this weekend so I suspect she had intentions of updating the Service Status thread from home. Even so, I doubt there'll be much progression made until early next week I'm afraid.

[#6]

out of interest this is not a new problem, I signed up for Home phone Aug 06, and have raised the follow problems with confirmed/unconfirmed calls not  being updated
21106923 18 Jan 07  Prob ID 39395
22387937 18 Jul 07
23405556 17 Nov 07 ID 47947
23559861 4 Dec 07 ID 48248 

[#7]

SS has now been updated, apologies I did not get chance to do this over the weekend.
With regards the problems raised before pierre_pierre, most of those were issues with a particular days' scripts not running. Those issues are fairly easy to fix.

[#8]

Isn't partitioning of tables an option rather than archiving? Depending upon the number of records and the overall database performance wouldn't it make sense to partition the large tables on a monthly (or weekly) basis.
All that is required then is to point the queries at specific partitions to get the data you need more efficiently - whilst maintaining the integrity of the dataset and historic data. The query then just needs expanding to view the whole table rather than a partition if somebody requires historic data.
We have tables with millions of rows which are queried with good performance - I suspect that even monthly partitioning would leave partitions with less than 2m records per partition.
Of course, I'm not sure if this function is available in the database you use though. (Oracle has the feature, none of my SQL DBs have got large enough to look for it!)