SQL Pager which is best? - 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)

SQL Pager which is best?

Author

Topic

fafche
Starting Member

6 Posts

Posted - 2009-12-10 : 06:17:22

Hello all,

I have 2 pager query's and would like to know which one is better for a heavy loaded table with lots of useres

1)


With ListRec As( 
SELECT ROW_NUMBER() OVER(ORDER BY id desc) AS rownum
FROM [Mytable] WHERE id <40000
)
SELECT * 
FROM ListRec
WHERE rownum >= 90 and rownum < 100

OR

2)


SELECT TOP 10 id
FROM [MyTable] 
WHERE (id NOT IN 
(SELECT TOP 90 id 
FROM [MyTable] 
WHERE id <40000) And id <40000

Thanks for any help.

I would also like to know how can I test it in a neutral environment

Sachin.Nand

2937 Posts

Posted - 2009-12-10 : 07:35:06

Of course the first one.But if u are fetching just 10 records everytime I guess it wont impact so much.

PBUH

fafche
Starting Member

6 Posts

Posted - 2009-12-10 : 08:00:48

The problem is I have a table with 2,000,000 records on it
and I am trying to page it for some clients.
when i test the 2 querys I get better results on first hit with the 2nd soultion.
and not the first!! this is unexpected. and I was woundering why it happnes

the test is only checking the time it took to get the result set.

is there a better way to test it?

fafche
Starting Member

6 Posts

Posted - 2009-12-10 : 08:05:42

another thing I was woundering about was the ability to fetch the total record num from the first solution.

is there a way without re running the query that i can get the max(row_NUMBER()) from the batch to a seprate parameter?

in the secound solution I need to run another query that counts all the records in order to get that.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-10 : 08:31:28

With ListRec (myCount,rownum) As(
SELECT
(Select Count(*) from [myTable]) as myCount,
ROW_NUMBER() OVER(ORDER BY id desc) AS rownum
FROM [Mytable] WHERE id <40000
)
SELECT *
FROM ListRec
WHERE rownum >= 90 and rownum < 100

fafche
Starting Member

6 Posts

Posted - 2009-12-10 : 08:45:31

quote:
Originally posted by DP978

With ListRec (myCount,rownum) As(
SELECT
(Select Count(*) from [myTable]) as myCount,
ROW_NUMBER() OVER(ORDER BY id desc) AS rownum
FROM [Mytable] WHERE id <40000
)
SELECT *
FROM ListRec
WHERE rownum >= 90 and rownum < 100

The question is why is it better. and how come I cant see the diffrece on a very big table.
and can I get the total row count out of it...

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-10 : 09:19:29

...(Select Count(*) from [myTable]) as myCount ...

This bit of code did not work?

Sicne you do not have a partition in your Row_Number() I can only assume that 90 - 99 can be anything (ie not the 90-99th per group of something...)

And from what I'm reading CTE's are in most cases no more efficient than other methods.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

Apparently CTE do not create work tables so each time you call one it is re-doing all of the work.

So for cases when calling the CTE once it should be similar to a derived table, in other instances it will have equal or worse performance.

fafche
Starting Member

6 Posts

Posted - 2009-12-10 : 10:17:28

quote:
Originally posted by DP978

...(Select Count(*) from [myTable]) as myCount ...

This bit of code did not work?

Sicne you do not have a partition in your Row_Number() I can only assume that 90 - 99 can be anything (ie not the 90-99th per group of something...)

And from what I'm reading CTE's are in most cases no more efficient than other methods.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

Apparently CTE do not create work tables so each time you call one it is re-doing all of the work.

So for cases when calling the CTE once it should be similar to a derived table, in other instances it will have equal or worse performance.

Hi and thanks,
I have created a work table as suggested.
The query now ran for 5 min insted of 2 in the previouse solutions.
but I am able to query for the "SELECT COUT('id')".
I didnt want to run 2 qurey (one for the count and one for the dataset) when I can only run query and get all the data I need.
Do you think I should use temp tables for a pager functionality?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources