Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Query running slow

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.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-20 : 19:32:48
Asked for more info by email.
Have a look in books online

something like

update statistics mytable
sp_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.
Go to Top of Page

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 online

something like

update statistics mytable
sp_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.

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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 Vendor

FROM 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 END
ORDER BY vendor
Go to Top of Page

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 1
Data - 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 2
LergData - Holds all npanxx(area code and prefix) for the US
Fields - npanxx(area code and prefix)
state(state npanxx is located in)

TABLE 3
Flat_01 - This is what hold the rates for each npanxx
Fields - 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_npanxx

I 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 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 Vendor

FROM 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 END
ORDER BY vendor



quote:
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"


Go to Top of Page
   

- Advertisement -