Author |
Topic |
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-11-19 : 13:53:41
|
Ok so im having a weird problem. I have a database with multiple tables that have phone rates based on npanxx (npanxx = area code & prefix). I also have a table that has CDRS which are call detail record. So I have a script that looks at the npanxx in the CDRs and goes to the rate tables to find the lowest rate based on the npanxx. This has all work great for a long time now and my sql server 2005 was an eval so i had to reinstall it with a purchased copy. Since then this process has still worked fine until i tried to add new CDRs in the table. The new ones process really slow but the old ones that are still in the database still process fine. For example, Old data with 150,000 rows gets processed in sub 3 sec's. New data with only 5 rows with take 15 seconds. I don't understand why new data is having problems. There is nothing different about the data. I hope someone has run into this problem or has an idea of what could be causing this.Thanks for your help,Nick |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-19 : 21:04:43
|
Try an update statistics and recompile.If that doesn't work try rebuilding indexes.If that doesn't work try a query hint.If you look at the query plan should be clear. I suspect it is doing a table scan rather than using an index.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-20 : 19:32:48
|
Asked for more info by email.Have a look in books onlinesomething likeupdate statistics mytablesp_recompile 'mytable'sp_dbreindex 'mytable' - now alter table ...if that doesn't work get two queries - one that's quick and one that's slow.Now run them in management studio and look at the query plans.You'll probably find one does a scan where the other does a seek - you can get round that with a hint.I don't like doing that but it's often necessary - could also save you having to reindex so often too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-11-20 : 20:24:24
|
The one thing i find odd is:Keep in mind I am using the same sql statement I always have.Table that holds the data has always run fast and then I add more data to that table and the new data runs slow. The Old data still run fast. So let’s say I query apples and it runs fast now I add oranges and query oranges and it runs slow. There is no difference in the data. Does this sound like an indexing problem?quote: Originally posted by nr Asked for more info by email.Have a look in books onlinesomething likeupdate statistics mytablesp_recompile 'mytable'sp_dbreindex 'mytable' - now alter table ...if that doesn't work get two queries - one that's quick and one that's slow.Now run them in management studio and look at the query plans.You'll probably find one does a scan where the other does a seek - you can get round that with a hint.I don't like doing that but it's often necessary - could also save you having to reindex so often too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-20 : 20:30:29
|
It sounds like a statistics problem.Have a look at the query plans.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-11-21 : 04:04:28
|
And another thing; 3 secs for a query with 150k rows seems very slow...unless the query has some fuzzy logic I'd say there's room for substantial improvement.- Lumbago |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-21 : 04:08:40
|
It depends. It seems OP is doing some kind of RBAR or triangular join to get lowest rate.Maybe OP should use the new ROW_NUMBER() function instead?Unless OP provides full query, I don't think we can help. E 12°55'05.63"N 56°04'39.26" |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-11-21 : 14:07:04
|
This query is dynamically generated based on what user selects. Below is the simplest form of the query:SELECT SUM(a.CallDuration) AS CallDurationSum, AVG(CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE flat01_intra END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE flat01_inter END END) AS AvgRate, SUM(CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE flat01_intra * a.CallDuration END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE flat01_inter * a.CallDuration END END) AS Cost, CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE 'Flat 01 Intra' END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE 'Flat 01 Inter' END END AS VendorFROM Data AS a LEFT OUTER JOIN LergData AS b ON b.npanxx = a.OrigNumber LEFT OUTER JOIN LergData AS c ON c.npanxx = a.TermNumber LEFT OUTER JOIN FlatRate_01 ON a.TermNumber = FlatRate_01.flat01_npanxx WHERE (a.CDRS = 'PI')GROUP BY CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE 'Flat 01 Intra' END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE 'Flat 01 Inter' END ENDORDER BY vendor |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-11-21 : 16:25:02
|
This query is dynamically generated based on what user selects.So this how it works:TABLE 1Data - Holds Call Detail Records(CDR's)Fields - CallDuration(length of call) TermNumber(this is the number being called) OrigNumber(this is the number placing the call) CDRS(unique name to identify a group of cdrs)TABLE 2LergData - Holds all npanxx(area code and prefix) for the USFields - npanxx(area code and prefix) state(state npanxx is located in)TABLE 3Flat_01 - This is what hold the rates for each npanxxFields - flat01_npanxx(area code and prefix) flat01_inter(this holds the rate for an interstate call) flat01_intra(this holds the rate for an intrastate call)So what happens is I select a dropdown list the contain the cdr group name and I select what rate deck i want to compare this against. In this case its Flat_01. So now when the query is run it will take each record in the table Data that matches the group i select and take the TermNumber and the OrigNumber and compare it to the LergData to find out which state the TermNumber is in and which state the OrigNumber is in. This is done to determine whether this is an interstate call or intrastate call. When it knows which type of call this is it will go to the flat_01 rate deck and choose the flat01_inter rate or the flat01_intra rate to use. It will use the rate based on TermNumber = flat01_npanxxI hope this all make sense.Below is the simplest form of the query:SELECT SUM(a.CallDuration) AS CallDurationSum, AVG(CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE flat01_intra END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE flat01_inter END END) AS AvgRate,SUM(CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE flat01_intra * a.CallDuration END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULLELSE flat01_inter * a.CallDuration END END) AS Cost,CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE 'Flat 01 Intra' END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE 'Flat 01 Inter' END END AS VendorFROM Data AS a LEFT OUTER JOINLergData AS b ON b.npanxx = a.OrigNumber LEFT OUTER JOINLergData AS c ON c.npanxx = a.TermNumber LEFT OUTER JOINFlatRate_01 ON a.TermNumber = FlatRate_01.flat01_npanxxWHERE (a.CDRS = 'PI')GROUP BY CASE WHEN b.state = c.state THEN CASE WHEN (flat01_intra IS NULL) THEN NULL ELSE 'Flat 01 Intra' END ELSE CASE WHEN (flat01_inter IS NULL) THEN NULL ELSE 'Flat 01 Inter' END ENDORDER BY vendorquote: Originally posted by Peso It depends. It seems OP is doing some kind of RBAR or triangular join to get lowest rate.Maybe OP should use the new ROW_NUMBER() function instead?Unless OP provides full query, I don't think we can help. E 12°55'05.63"N 56°04'39.26"
|
|
|
|