Top vs Set Rowcount - 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 2000 Forums

Transact-SQL (2000)

Top vs Set Rowcount

Author

Topic

chenko
Starting Member

24 Posts

Posted - 2007-06-06 : 05:09:39

Generally, we have been using Top here to limit records, with sorting.

Recently one query has been taking "ages" (2.5 seconds) to run.

This is the query run on a table with 11088 records.


SELECT TOP 50 Log_Username, Log_Date, Log_Info, Log_QUERY_STRING
FROM Log
WHERE Log_Type = 'Scan'
ORDER BY Log_Date DESC

If I took out the ORDER BY it would go back to normal speed, but obviously the results are not what we want.

By simply changing to using Rowset it executes in under 50ms


SET ROWCOUNT 50
SELECT Log_Username, Log_Date, Log_Info, Log_QUERY_STRING
FROM Log
WHERE Log_Type = 'Scan'
ORDER BY Log_Date DESC
SET ROWCOUNT 0

Should the original statment take this long? The script that this is on, runs a few SQL statments using both TOP and ORDER BY on this same table and noone of them causes the same issue.

thanks :)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-06 : 05:13:58

Well they should o the same thing. Did you clear the plan cache in case an old plan was being used?
I would guess that the second has spotted that it can use an index to get the rows in the correct order. Have a look at the query plans and consider a hint or recoding the query maybe using a derived table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 11:35:36

I have also noticed that using SET ROWCOUNT is much faster than TOP. Even if there is an ORDER BY, the moment you put TOP it screws you.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 07:52:06

Also you can use

Set rowcount @var
Select.........
set rowcount 0

But Top @var is not possible until you use Dyanmic sql

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-06-11 : 22:38:16

On a million row test table, I got exactly the opposite results... TOP took 2 seconds... RowCount took 10 seconds. Both had an ORDER BY.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:37:46

quote:
Originally posted by Jeff Moden

On a million row test table, I got exactly the opposite results... TOP took 2 seconds... RowCount took 10 seconds. Both had an ORDER BY.

--Jeff Moden

That seems interesting
What is your rows limit?

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-12 : 09:58:34

quote:
Originally posted by Jeff Moden

On a million row test table, I got exactly the opposite results... TOP took 2 seconds... RowCount took 10 seconds. Both had an ORDER BY.

--Jeff Moden

Did you clear the data cache between tests?
Have a look at the query plan to see the difference.
Be nice to know if the estimated query plan was the same as the actual too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mabbj747
Starting Member

2 Posts

Posted - 2009-01-28 : 06:49:44

But Top @var is not possible until you use Dyanmic sql
Madhivanan
Failing to plan is Planning to fail
____________________________________________________________________________________

To answer the above question, you can use TOP with a variable without useing Dynamic SQL
The example could be
DECLARE @countVar INT
SELECT @countVar = 100
SELECT
TOP (@countVar) *
FROM
tblYourTable

In order to use TOP with variable, you need to enclosed the variable in parenthesis "()"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:51:47

quote:
Originally posted by mabbj747

But Top @var is not possible until you use Dyanmic sql
Madhivanan
Failing to plan is Planning to fail
____________________________________________________________________________________

To answer the above question, you can use TOP with a variable without useing Dynamic SQL
The example could be
DECLARE @countVar INT
SELECT @countVar = 100
SELECT
TOP (@countVar) *
FROM
tblYourTable

In order to use TOP with variable, you need to enclosed the variable in parenthesis "()"

Sorry boss you're in wrong forum to state this

you can use top with variables starting from sql 2005 onwards but not before.
so Madhi is right (as always!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources