Click here to Skip to main content
15,921,716 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi In paging data we need to number of records count.

Then I can use two method:
FIRST: execute a count sql query in separate command.
I use C# and Transaction for this.

SECOND: execute count over() in above query.

Which of this is efficient and faster?

thanks.

What I have tried:

FIRST: execute a count sql query in separate command

*
SQL
SELECT count(*) from tbl


*
SQL
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (id) AS RowNumber from tbl
) t where RowNumber Between 1 AND 50



SECOND: execute count over() in above query.

SQL
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (id) AS RowNumber, COUNT(*) OVER() As RowsCount from tbl
) t where RowNumber Between 1 AND 50
Posted
Updated 30-Oct-22 9:35am
v6

My gut feeling is that the second one would probably be better, since it's only one query. But the only way to be sure is to measure the performance of both queries against your data, which we can't do.
 
Share this answer
 
Comments
maysamfth 28-Oct-22 4:30am    
thanks for reply. Its true and i can test queries with timespan, but how i can ensure that system performance (ram, cpu etc) is equal in tests?
Richard Deeming 28-Oct-22 4:34am    
It would be better to include the actual execution plan, and compare the plans for the two-query solution to the one-query solution.

Execution Plans - SQL Server | Microsoft Learn[^]
I personally looking that your need to decide it which query is better for the performance. This is optimizer-specific, DBMS-version specific, and query-specific.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900