May 7, 2008 at 2:48 pm
I know this may sound strange.... I'm not sure I believe it....but I tested it several times
Currently I have a query where the natural order when run has Record A first. When I add a 'TOP 1' to the query, record B is first. I can't explain that, but for my purposes here it didn't matter.
The other problem is that the TOP 1 changes the run time on the query from 2 seconds to well over 45 seconds.
It doesn't sound like a lot but when run 250,000 times it makes a HUGE difference.
Any ideas what else I can use other than TOP?
FYI: I tried setting the return values to single variables and reversing the sort order so that the variables brought back the LAST VALUES, giving me a 'TOP 1' but it only shaved off maybe 10 seconds....
WHY IS 'TOP' so slow!!!!!! (MSSQL 2K)
May 7, 2008 at 3:12 pm
Perhaps you could share some code?
Table and clustered index and which data you think is where.
May 7, 2008 at 3:14 pm
There's no such thing as 'natural order'. If an order by is not specified, the order that the rows return in is not defined and may change.
How do the execution plans compare? If you like, save them as .sqlplan files (I assume you're using SQL 2005, since this is the SQL 2005 forum), zip them and attach them to your post.
My guess, sQL's using a different execution plan for the TOP 1 and, for whatever reason, it's a much slower option. Without seeing the plans, it's hard to say more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2008 at 9:14 pm
It's in SQL server 2005 because from the main page, that's the closest there is. Is there a SQL server 2000 forum? I'm pretty new so perhaps I just haven't found it?
This is in 2000
The execution plan differs in that for the 'top 1' the table seek goes from 40% of the execution time to 100%
And before you suggest something obvious, no I can't just add indexes. This is text in many rows that I have to compare. If I index every row I compare, the inserts updates and deletes will take forever. Not a solution.
I'm not used to TSQL, I'm used to oracle, Mysql and such....strange things like this keep annoying me!
and on the natural order thing.....so you're saying that the order changes? there is no natural order to a table based on table, database and indexing settings? it doesn't naturally order in some fashion related to settings or insert order?
I'm having troubles believing that, there must be a natural order to tables it doesn't make sense how you'd store a table that had no natural order...... although I've been wrong before!
May 7, 2008 at 9:49 pm
Each index (not heap) has a "natural order" ie the order in which it was defined, but you just don't get a guarantee that the results will be returned in that order. It's only an order for storage purposes and the optimiser will return the records in the fastest way possible regardless of order unless you specify otherwise.
May 7, 2008 at 11:04 pm
A table is defined as an unordered set. While the physical implementation of the table does give the data an order stored on disk, there is no guarantee that the rows will be returned in that order.
Bottom line, if you want a guaranteed order, use an order by.
Now, on to the query. Since it's SQL 2000, please do the following:
Run SET SHOWPLAN_ALL ON
Run both of your queries. Use Query analyser and the Grid mode. They will not return data, but you will get the execution plan back.
Copy the plans into excel, save file, zip, attach to post.
Also, if we can see the query and the table and index definitions it would help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2008 at 2:13 pm
Without the code of the query, and probably the underlying tables, the best bet I can give you is, instead of using Top 1, set something in your Where clause that gets you the row you want.
On the subject of the "natural order" of the data, yes, there is, based on the clustered index, but SQL Server just uses that for the storage. Per the basic definition of relational databases (Codd, et al), the order of the rows is immaterial, unless you tell the database otherwise.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 9, 2008 at 6:41 am
adrian_townsend (5/7/2008)
I know this may sound strange.... I'm not sure I believe it....but I tested it several timesCurrently I have a query where the natural order when run has Record A first. When I add a 'TOP 1' to the query, record B is first. I can't explain that, but for my purposes here it didn't matter.
The other problem is that the TOP 1 changes the run time on the query from 2 seconds to well over 45 seconds.
It doesn't sound like a lot but when run 250,000 times it makes a HUGE difference.
Any ideas what else I can use other than TOP?
FYI: I tried setting the return values to single variables and reversing the sort order so that the variables brought back the LAST VALUES, giving me a 'TOP 1' but it only shaved off maybe 10 seconds....
WHY IS 'TOP' so slow!!!!!! (MSSQL 2K)
You can try using SET ROWCOUNT 1.
This will give you slightly better performance
Another option is to select the data into a variable.
For example if you have table 'Pets' and you want any one PetID
you can say:
DECLARE @PetID
SELECT @PetID = PetID
FROM Pets
This will return only one record -- the last one.
If you wanted the first one you would need to add an ORDER BY.
May 9, 2008 at 3:43 pm
Functionally, TOP and SET ROWCOUNT do the same thing. However, SET ROWCOUNT will affect any subsequent queries in a connection. To remove the row count option, use SET ROWCOUNT 0. Another thing to remember is that SET ROWCOUNT overrides a SELECT TOP only if the SET ROWCOUNT value is less.
- Zahran -
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply