November 19, 2007 at 1:15 pm
When querying a single table that is wide and has lots of records, using the TOP keyword can give you a glimpse at the data without affecting database performance.
Example: SELECT TOP 100 * FROM WIDE_AND_LONG_TABLE
However, what happens when you add a join?
Exmaple:
SELECT TOP 100 T1.*
FROM WIDE_AND_LONG_TABLE1 T1, WIDE_AND_LONG_TABLE2 T2
WHERE T1.COLUMN1 = T2.COLUMN1
Is the join executed first? If so and you know that the join itself is a performance killer, does the TOP keyword still make any difference?
November 19, 2007 at 1:24 pm
TOP simply limits the results returned to the client. All processing that has to take place on the server to gather the result set is still done, single table or joins.
This is one of those "there ain't no such thing as a free lunch" moments.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 19, 2007 at 3:17 pm
Well, Its not exactly a free lunch but it does help some. If you use a top 100 SQL will stop running after it finds the first 100 matches (based on your order by clause of course). So for example if you run a query that normaly returns 1,000,000 rows it will considerably faster if you pull the top 100 rows from it.
Just as an example:
select * from sysobjects
full join sysobjects x on 1=1
select top 100 * from sysobjects
full join sysobjects x on 1=1
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 20, 2007 at 5:45 am
I guess I'm wrong, but I thought that the TOP operator strictly applied to what was returned to the client, not what was actually processed on the server. It does limit what goes across the wire, sometimes, in large data sets, the largest part of the operation. However, in terms of query processing on the server side there are 11 steps to getting a result set and TOP is step #11. I really thought that meant that every other bit of data was gathered up by the query engine prior to the top being applied and therefor, you didn't save much on the server using this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 20, 2007 at 7:04 am
Honestly I'm not sure why if its listed last in the plan it behaves the way it does but it does seem to seriously reduce the amount of time on queries. Maybe one of the gurus around here can tell us?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply