Query running slow - SQL Server Forums

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

Other SQL Server Topics (2005)

Query running slow

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2008-11-19 : 16:00:32

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

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-11-19 : 21:40:05

Share the SQL statement in question along with execution plan for good and bad performance. The problem description you gave talks business specifics, but lacks basic technical details.

You can collect it as follows from Management Studio/Enterprise Manager.

SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON

problem stmt

SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-20 : 04:32:21

Sounds like an index problem maybe?

have you tried this :


DBCC DBREINDEX('<your table names here>', '', 0)

For each of the tables -- this will rebuild any indices on the table. It will lock the table for the length of the reindex (shouldn't take a long time unless you've got a huge amount of data). (a few seconds maybe max).

Give that a shot and see if it improves performance.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

nhess80
Yak Posting Veteran

83 Posts

Posted - 2008-11-24 : 14:32:04

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 SimpleSQL

Share the SQL statement in question along with execution plan for good and bad performance. The problem description you gave talks business specifics, but lacks basic technical details.

You can collect it as follows from Management Studio/Enterprise Manager.

SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON

problem stmt

SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-25 : 00:26:26

You didn't include the output from executing the code with the stats turned on so it's hard to say what indexes may or may not be missing, but based on the query, you may need indexes on these columns, if any of these are not indexed then it may be the cause of the slowness:

Data.CDRS
Data.OrigNumber
Data.TermNumber

LergData.npanxx
LergData.npanxx

FlatRate_01.flat01_npanxx

For more specific help, you need to post the full table structures, some sample data, and the results of executing with the stats turned on.

Also, you can replace all those CASEs with coalesce, which will greatly simplify the query, although it won't solve your performance issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources