Slow query...bad design or just lots of rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Slow query...bad design or just lots of rows?

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 00:39:36

I have 4 tables, Users, Clients, Lists and Subscribers. A Subscriber belongs to a List who belongs to a Client who belongs to a User. Each table has a unique ID which is the primary key, and all foreign key constraints are enforced. When I run the query below, it takes about 4 seconds to run:

        SELECT
	cl.ClientName,
	l.ListID,
	l.Title,
	TotalSubscribers = (SELECT COUNT(SubscriberID) FROM Subscribers WHERE ListID = l.ListID AND IsActive = 1)
FROM
	Clients cl
	INNER JOIN Lists l ON cl.ClientID = l.ClientID
WHERE
	cl.UserID = 34 AND
	l.IsActive = 1 AND
	l.IsVisible = 1
ORDER BY
	cl.ClientName,
	l.Title

The subscribers table has about 3.5 million records, and I have done a little testing and it's the count that's slowing things down. I have tried changing the query from a subquery to just join the Subscribers table and that didn't speed things up.

I also tried adding an index on the ListID field in the Subscribers table (was already a foreign key) and that didn't seem to make any difference.

Is there anything I should be doing that could speed this up considerably? The options I can think of would be to keep a running total in the List table which would speed up this query but slow down inserts/deletes. Another option is to horizontally partition my Subscriber table so it doesn't get so big?

Any ideas/recommendations?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-07 : 00:46:08

SELECT
cl.ClientName,
l.ListID,
l.Title,
SUM(CASE WHEN s.IsActive = 1 THEN 1 ELSE 0 END) AS TotalSubscribers
FROM
Clients cl
INNER JOIN Lists l ON cl.ClientID = l.ClientID
INNER JOIN Subscribers s ON l.listID = s.ListID
WHERE
cl.UserID = 34 AND
l.IsActive = 1 AND
l.IsVisible = 1
ORDER BY
cl.ClientName,
l.Title

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-07 : 00:53:06

Lose the ORDER BY...Maybe shift that to the presentation tier....
INDEX the ClientID in the List table (Your insert/updates will suffer)

FK won't make a difference in a SELECT...

DavidM

"If you are not my family or friend, then I will discriminate against you"

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 00:54:36

Thanks derrickleggett, this gives slightly better results but the query still takes about 3.5 seconds. I had already tried a similar join query (which I should have posted since its a bit cleaner).

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 00:59:43

Hi byrmol, I tried your suggestions, removed the ORDER BY and added in index on the ClientID field in the List table but the results are still averaging the same, 3.5 seconds.

Thanks for the FK info.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-07 : 01:05:35

How about moving the List WHERE clauses into the join?
Is UserID indexed?

DavidM

"If you are not my family or friend, then I will discriminate against you"

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 01:16:04

I added the index to UserID and moved the List where clauses into the join. I haven't tried moving a where clause to a join before so does this look right?

SELECT
cl.ClientName,
l.ListID,
l.Title,
SUM(CASE WHEN s.IsActive = 1 THEN 1 ELSE 0 END) AS TotalSubscribers
FROM
MA_Clients cl
INNER JOIN SU_Lists l ON cl.ClientID = l.ClientID AND l.IsActive = 1 AND l.IsVisible = 1
INNER JOIN SU_Subscribers s ON l.listID = s.ListID
WHERE
cl.UserID = 34
GROUP BY
cl.ClientName,
l.ListID,
l.Title

If so, it didn't speed things up, still at 3.5 seconds.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-07 : 01:21:07

That looks right...

Well, I am just about out of suggestions...

Are your statistics up to date? sp_updatestats
Are your indexes in good order? DBCC SHOWCONTIG

Try freeing the procedure cache...DBCC DROPCLEANBUFFERS & DBCC FREEPROCCACHE

Oh yeah.. how many rows are returned?

DavidM

"If you are not my family or friend, then I will discriminate against you"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 01:26:57

What I find sometimes helps when few rows are returned from large tables is to use derived tables.
I'm not sure if it will help here,
why not give it a try:

--*********************************
SELECT
cl.ClientName,
l.ListID,
l.Title,
SUM(CASE WHEN s.IsActive = 1 THEN 1 ELSE 0 END) AS TotalSubscribers
FROM
MA_Clients cl
INNER JOIN (SELECT * FROM SU_Lists WHERE IsActive = 1 AND IsVisible = 1) l ON cl.ClientID = l.ClientID
INNER JOIN SU_Subscribers s ON l.listID = s.ListID
WHERE
cl.UserID = 34
GROUP BY
cl.ClientName,
l.ListID,
l.Title

Duane.

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 01:37:41

15 Rows get returned for the query.

byrmol, tried those changes and they didn't make any major difference, still around 3.5 seconds. Thanks for your help.

Hi ditch, I tried that query and it came out pretty much the same as the others, maybe a tiny bit faster at 3.4 seconds.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 01:43:39

Maybe with another derived table?
Try this,
I'm just taking wild guesses here:
--********************************************************
SELECT
cl.ClientName,
l.ListID,
l.Title,
SUM(CASE WHEN s.IsActive = 1 THEN 1 ELSE 0 END) AS TotalSubscribers
FROM
(SELECT * FROM MA_Clients WHERE UserID = 34) cl
INNER JOIN (SELECT * FROM SU_Lists WHERE IsActive = 1 AND IsVisible = 1) l ON cl.ClientID = l.ClientID
INNER JOIN SU_Subscribers s ON l.listID = s.ListID
GROUP BY
cl.ClientName,
l.ListID,
l.Title
--*************************************************************

Duane.

benricho
Yak Posting Veteran

84 Posts

Posted - 2004-04-07 : 01:58:13

Thanks ditch, but that had very similar results, about 3.4 seconds.

I might add that the hardware this is running on isn't amazing (Pentium 4, 2.66Ghz, 512RAM, IDE hard drive, Windows server 2003, MSDE as the database), but I want to make sure when my database grows performance doesn't get killed. As I mentioned before the Subscribers table has 3.5 million rows, would you expect that I might have to break this up into partitioned tables with this many rows or doesn't this become an issue until the database gets much bigger?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 02:03:36

Well I think you should maybe experiment with a partition solution.
Other than that I am also just about out of suggestions.

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-07 : 09:12:42

try this:


SELECT
	cl.ClientName,
	l.ListID,
	l.Title,
        SubscriberCount
FROM
	Clients cl
INNER JOIN 
	Lists l ON cl.ClientID = l.ClientID
INNER JOIN
	(select listID, count(subsriberID) as SubscriberCount 
         from Subsribers 
         where IsActive=1
         group by listID) b
	ON l.listID = b.ListID
WHERE
	cl.UserID = 34 AND
	l.IsActive = 1 AND
	l.IsVisible = 1
ORDER BY
	cl.ClientName,
	l.Title

not sure if it will help, though ...

- Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-07 : 12:40:49

That server isn't super fast. I suspect 3.5 seconds for this query is about as fast as it's going to run on a server like that.

Also, do you have a lot of inactive / invisible rows in your List table?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources