Help with optimizing query - 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 2000 Forums

SQL Server Development (2000)

Help with optimizing query

Author

Topic

mark1110
Starting Member

4 Posts

Posted - 2005-03-09 : 16:14:54

Hi,

I have the following query:

SELECT cl.client_id, count(ce.client_id) as lives,
(CASE WHEN count(ce.client_id) BETWEEN 000 AND 049 THEN ' 0 to 49 Lives '
WHEN count(ce.client_id) BETWEEN 050 AND 074 THEN ' 50 to 74 Lives '
WHEN count(ce.client_id) BETWEEN 075 AND 099 THEN ' 75 to 99 Lives '
WHEN count(ce.client_id) BETWEEN 100 AND 149 THEN ' 100 to 149 Lives '
WHEN count(ce.client_id) BETWEEN 150 AND 249 THEN ' 150 to 249 Lives '
WHEN count(ce.client_id) BETWEEN 250 AND 499 THEN ' 250 to 499 Lives '
WHEN count(ce.client_id) BETWEEN 500 AND 999 THEN ' 500 to 999 Lives '
WHEN count(ce.client_id) BETWEEN 1000 AND 2000 THEN '1000 to 2000 Lives '
ELSE 'Greater than 2000 Lives ' END) as ranges
FROM client cl, census ce
WHERE cl.client_id = ce.client_id AND YEAR(orig_eff_dt) = 2004
GROUP BY cl.client_id
ORDER BY cl.client_id;

When I run it it takes over a minute to run. I am not sure what I could do to optimize it. I tried replacing the count function in the case statement with the alias lives, but the compiler didn't like it. I have spend over 2 days trying to figure out what I can do. Could someone please take a look at it and see what I am doing wrong.

The census table is about 5 million records and the client table has about 25,000 records.

Thanks,

Mark

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-09 : 16:23:09

1) get rid of:

YEAR(orig_eff_dt) = 2004

that cannot use an index. Make sure that the orig_eff_dt column is indexed, and then use:

orig_eff_dt >= '1/1/2004' and orig_eff_dt < '1/1/2005'

2) you have two tables in your FROM clause:

FROM client cl, census ce

and I don't see a join between the two. this might be causing a cartesian product which can result in an exponential number of rows being processed that you don't want.

Edit:scratch that, I see it in your WHERE clause .. don't do that! use a JOIN.

3) make sure the clientID column is indexed as well.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-09 : 16:26:21

What Primary Keys and Indexes are on the tables? About what percentage of the census records are in 2004?
Post the execution plan.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-09 : 16:43:52

Whichever table has orig_eff_dt, you can use a covering index of clientID, orig_eff_dt. On the other table, an index on clientID would be all that is needed. If one of these tables has clientID as the primary key, then you've already got an index on it. If the other is a foreign key, then make sure you went back and added an index to it as you don't get this automatically with fks.

Tara

mark1110
Starting Member

4 Posts

Posted - 2005-03-09 : 18:26:35

I replaced the YEAR(orig_eff_dt) = 2004 function with orig_eff_dt >= '1/1/2004' and orig_eff_dt < '1/1/2005' and indexed the table on orig_eff_dt and the stored procedure went from running in 110 seconds to 5 seconds.

Thanks to everyone for your help.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources