question

Christophe Vanderhaeghen avatar image
Christophe Vanderhaeghen asked

different ways to use parameterized TOP statement, but which one is the best?

Hi, I want to add a parameter to my stored procedures in which I can set the maximum number of rows to fetch. The following code works great, but ... declare @var as int Set @var = 200 select top (@var) * from dbo.MyTable - Problem 1: I want the parameter to be nullable (so that we can decide to fetch all). Using sp_executesql is not the way I want to go. - Problem 2: I read on a few forums that `top (@parameter)` is really bad for performance. Is this true? Can someone confirm or deny this? Thank you for your help, Christophe Vanderhaeghen
sql-server-2008stored-proceduresparameterstop
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
TOP without an ORDER BY is a very poor choice and will likely lead to performance issues since index selection at that point is somewhat random. It's not completely related, but I did a series of experiments involving TOP, MAX and ROW_NUMBER. They were published in the SQL Server Standard magazine from PASS. It should be [available here][1], but you have to be a PASS member (go ahead and join, it's free and you ought to be a member anyway). The conclusion I came to, ROW_NUMBER works better with small sets of data, TOP worked better with larger sets. You can use MAX in a manner similar to both, but it either converts to a TOP operation inside the optimizer, or it goes to aggregation, so you're better off just using TOP anyway. [1]: http://www.sqlpass.org/LinkClick.aspx?fileticket=F-vFcJUFHJo%3d&tabid=685&mid=803
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Where is the +5 button? Awesome.
0 Likes 0 ·
Ha! Thanks @Matt. I'm going to update that article a bit & republish with Steve or Tony. It's way too hidden back there behind the PASS login wall.
0 Likes 0 ·
True - I'd never seen it before - and it's excellent...
0 Likes 0 ·
Very interesting point of view on the order by. I didn't know it had such a big influence. Could you be a bit more clear on "small sets of data". How do you see 36000 records for instance?
0 Likes 0 ·
I would consider that a larger set. That amount of data also gets into "What the heck are the users doing" types of questions. Because no one looks at 36000 records, so why would you return that many. But that's a discussion for another day. There is no hard and fast number, this much is big, this much is small, but in general, I'd say more than 1,000 rows, probably TOP is beter than ROW_NUMBER. Less than 1,000, probably ROW_NUMBER is better than TOP. But, as in all things, your mileage may vary.
0 Likes 0 ·
Show more comments
Cyborg avatar image
Cyborg answered
I prefer to user ROW_NUMBER() in this case instead of dynamic sql using top, I am not sure whether ROW_NUMBER() function always outperform the TOP operator. Check out this example


CREATE  TABLE dbo.MyTestTable(
	message_id int NOT NULL,
	language_id smallint NOT NULL,
	severity tinyint NULL,
	is_event_logged bit NOT NULL,
	text nvarchar(2048) NOT NULL,
 CONSTRAINT PK_MyTestTable PRIMARY KEY CLUSTERED 
(
	message_id ASC,language_id ASC
)
) ON [PRIMARY]

GO

INSERT INTO MyTestTable
SELECT * FROM Sys.messages 

DECLARE @NoRcrds INT
SET @NoRcrds = NULL

DECLARE @NoRcrds INT
SET @NoRcrds = NULL

SELECT message_id, language_id, severity, is_event_logged, text
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY Message_ID) ID,
       message_id, language_id, severity, is_event_logged, text
FROM MyTestTable
) T
WHERE ID 
Addressing to second part of your question. Its all depends on whether your column on SELECT Query is indexed or not. Because while running Top operator SQL Server performs Sorting, Which definitely is an overhead if the columns on the select list is not a part of index key.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

malpashaa avatar image
malpashaa answered
1- Try this: DECLARE @var AS INT; SET @var = 200; SELECT TOP(ISNULL(@var, 2147483647)) * FROM dbo.MyTable ORDER BY some_columns And do not forget to specify ORDER BY columns, or else the rows would be chosen arbitrary. 2- In my opinion you should not think in optimization until you need to, and there is no silver bullet regarding whether TOP is good or bad.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The isnull function is something that could be used here. good point. Nevertheless I think that I should really think it over, looking at the remarks on the Order by clause.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
Specifically on the point of top @var being bad for performance - that will depend on how the query optimizer chooses to optimise the query. Normally, based on a number of expected rows, a different join operator would be chosen to operate a join - or the query optimiser may decide that a seek is more efficient for a small number of rows whereas a scan might be more effective for a large number of rows. This type of difference in decision by the query optimiser can yield performance variability - and that is where the forum posts you mention come from. Try looking at the actual plan for this simple query: DECLARE @var [int] = 100 SET @var = 10000 SELECT TOP (@var) * FROM [sys].[all_columns] In that, you will see that the estimated number of rows is different from the actual number of rows. That difference can be critical when trying to diagnose query optimiser choices (seeing as the estimated number of rows is what the plan is based on).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Christophe Vanderhaeghen avatar image
Christophe Vanderhaeghen answered
To wrap it up a bit: - Using Row_number works a bit better then TOP with small amounts of data (< 1000), but it's not exact science. - Using TOP is most of the time the best way to go. Most certainly if you have/expect 1000+ records to fetch. - Use TOP together with the ORDER BY clause (else the rows would be chosen arbitrary, which is pointless) - Ad hoc querying to an OLTP system absolutely leads to trouble - If ad hoc querying is needed, keep the following in mind: 1. you better use stored procedures or at least parameterized queries 2. Instead, set up a reporting system where you can generate a more BI friendly schema, add in aggregates, make the database read only, so locking isn't an issue... etc 3. In SQL server 2008, consider looking at the Resource governor for (partly) solving ad hoc queries - Profiling or looking at the query optimizer can also help - Indexing can boost performance (if correct indexing is used of course) Thanks a bunch for those tips all of you. Best regards, Christophe
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow, execelent summary. I'd make one minor change. The next to the last point says "Profiling or looking at the query optimizer can also help, but this is more for specific queries and not ad hoc" is not accurate. It is for ad hoc as well. Other than that, you've nailed it all.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.