Very slow performance when Count(*) on subquery with

Question

i need to know the total rows returned by a query to fill pagination text in a web page.

Im doing pagination on SQL side to improve performance.

Using the query below, i get 6560 records in 15 seconds, wich is slow for my needs:

1.

SELECT COUNT(*)
FROM dbo.vw_Lista_Pedidos_Backoffice_ix vlpo WITH (NOLOCK)
WHERE dataCriacaoPedido>=DATEADD(month,-6,getdate())

Using this query, i get the same result in 1 second:

2.

SELECT COUNT(*) FROM
    (SELECT *, ROW_NUMBER() over (order by pedidoid desc) as RowNumber
    FROM dbo.vw_Lista_Pedidos_Backoffice_ix vlpo WITH (NOLOCK)
    WHERE   
        dataCriacaoPedido>=DATEADD(month,-6,getdate())
    ) records
WHERE RowNumber BETWEEN 1 AND 6560

If i change the above query (2.) and set the upper limit of RowNumber to a number greater than 6560 (the result of count(*)), the query takes again 15 seconds to run!

So, my questions are: - why is the query 2. takes so less time, even that the limit on RowNumber actualy dont limit any of the rows in the subquery? - is there any way i can use the query 2. on my advantage to get the total rows?

Ty all :)

@techspider [`COUNT(1)` vs `COUNT(*)` has literally no impact on the query plan it uses.](http://stackoverflow.com/questions/1221559/count-vs-count1) — Siyual, Aug 04 '16 at 19:10
Any indexs? Also might be helpful to explain plan these queries so you can see the route it's taking. — Twelfth, Aug 04 '16 at 19:10
Can you please paste sample schema of table involved,indexes — TheGameiswar, Aug 04 '16 at 19:11
Hi I am guessing by the vw_ that you are querying a view. Could we see the SQL for the view? I agree we need to see the indexes for the table or the table(s) involved in the view, if this is a view. If there is no index on that date field that wont help. Also, look at the execution plan for the slow query and the fast query. Whats the difference? Where is the high cost? Run the slow query and the fast query with SET STATISTICS IO,TIME ON before it. Again, whats the difference. Is there a big increase in disk reads on a particular table? — Jonathan Shields, Aug 04 '16 at 19:22
Also, use of NOLOCK is generally not great as you run a small risk of dirty reads. — Jonathan Shields, Aug 04 '16 at 19:25

score 1 · Answer 1 · answered Aug 04 '16 at 19:49

This isn't going to fully answer your question, because the real answer lies in the view definition and optimizing that. This is intended to answer questions about behavior.

The reason why COUNT(*) is slower is because it has to generate all the rows in the view, and then count them. The counting isn't the issue. The generation is.

The reason why ROW_NUMBER() over (order by pedidoid desc) is fast is because an index exists on pedidoid. SQL Server uses the index for ROW_NUMBER(). And, just as important, it can access the data in the view using the same index. So, that speeds the query.

The reason why there is a magic number at 6,561. Well, that I don't know. That has to do with the vagaries of the SQL Server optimizer and your configuration. One possibility has to do with the WHERE clause:

WHERE dataCriacaoPedido >= DATEADD(month, -6, getdate())

My guess is that there are 6,560 matches to the condition. But, SQL Server has to scan the whole table. It scans the table, finds the matching values. However, the engine does not know that it is done, so it keeps searching for rows. As I say, though, this is speculation that explains the behavior.

The really fix the query, you need to understand how the view works.

Very slow performance when Count(*) on subquery with

1 Answers1