Slow COUNT(*) - 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

Transact-SQL (2005)

Slow COUNT(*)

Author

Topic

cmdcenter
Starting Member

15 Posts

Posted - 2009-06-28 : 05:24:55

I have got a query for which I want to use paging:

DECLARE @Start INT, @Count INT
SET @Start = 1
SET @Count = 8

;WITH Paging AS(
	SELECT news.*, ROW_NUMBER() OVER(ORDER BY news.id) RowNum
	FROM NewsItem news
	CROSS APPLY (SELECT TOP 1 * FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) ItemNet
)

SELECT * FROM Paging WHERE RowNum BETWEEN @Start AND @Start + @Count - 1

The above query runs very fast (in less than a second).

Now I need to calculate the total number of records of the above query:

SELECT COUNT(*)
FROM NewsItem news
CROSS APPLY (SELECT TOP 1 * FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0) ItemNet

This query runs in 8 seconds which is definitely considered very slow for users.

I thought I would rewrite it so that it runs faster:

SELECT COUNT(*) 
FROM NewsItem news
WHERE EXISTS (SELECT 1 FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0)

This time it takes 3 seconds for it to execute. Which I believe is still slow.

There are nearly 1,500,000 rows in each of News and ItemNetwork tables.

Do you have any suggestion how I can make the COUNT(*) query run faster (less than a second)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-28 : 06:16:29

Do the INNER JOIN thingy (with MIN/MAX) I showed you in an earlier post.

E 12°55'05.63"
N 56°04'39.26"

cmdcenter
Starting Member

15 Posts

Posted - 2009-06-28 : 06:43:05

[code]SELECT COUNT(*)
FROM NewsItem AS n
INNER JOIN (
SELECT ItemID,
MAX(NetworkID) NetworkID
FROM ItemNetwork
WHERE ItemType = 0
GROUP BY ItemID
) AS ItemNet ON ItemNet.ItemID = n.ID[/code]

It takes 3 seconds.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-28 : 11:19:37

What about this rewrite?

;WITH Paging
AS (
	SELECT		TOP (@Count)
			*
	FROM		(
				SELECT		TOP (@Start + @Count - 1)
						NewsItems.*
				FROM		NewsItems
				CROSS APPLY	(
							SELECT	TOP 1
								*
							FROM	ItemNetwork
							WHERE	ItemNetwork.ItemID = NewsItems.ID
								AND ItemNetwork.ItemType = 0
						) AS ItemNet
				ORDER BY	NewsItems.ID
			) AS d
	ORDER BY	ID DESC
)

SELECT		*
FROM		Paging
ORDER BY	ID

E 12°55'05.63"
N 56°04'39.26"

cmdcenter
Starting Member

15 Posts

Posted - 2009-06-29 : 03:32:32

Thank you Peter. I appreciate your help

This query produces the same result and runs fast. But what about count(*)? Do you have any suggestion for that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:41:17

Yes. Store the count of records in a "System parameter" table. Do not try to fetch the count each and every time when querying.
A count FORCES as table/index scan and will always be slow.

E 12°55'05.63"
N 56°04'39.26"

cmdcenter
Starting Member

15 Posts

Posted - 2009-06-29 : 04:26:59

The problem is that Count(*) is different for different values in WHERE condition and I can't store and update it for every single condition.

I am thinking of another approach. For paging, I will never show the last page (like google). This way, when user is seeing the first page, I just need to make sure if there are more 8 or 9 pages available to show. I mean, each time, I just need to make sure there are enough items to show for next few pages. Something like this:

SELECT COUNT(*) 
FROM 
(	
	(
		SELECT TOP (@Start + (@Count * 9)) * FROM NewsItem news
		WHERE EXISTS (SELECT 1 FROM ItemNetwork WHERE ItemID = news.ID AND ItemType = 0)
	)
) n

I don't need to make sure there are 1,500,000 records to show. I just need to know that there are something like 212 more records to show.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:30:24

The paging filters are dyanmically built?
The CROSS APPLY or EXISTS part is not fixed?

E 12°55'05.63"
N 56°04'39.26"

cmdcenter
Starting Member

15 Posts

Posted - 2009-06-29 : 04:32:24

Yes. One parameter that I have not mentioned in my query is NetworkID. I filter news by NetworkID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts