TOP clause
in [Programming SQL]
|
From: tshad on 6 Apr 2010 20:30 We have some Select statements that have TOP 10000 in them and they used to have ORDER BY clauses in them but we took them out. We are also taking out the TOP 10000 out as well. I assume this doesn't really affect performance (with or without the clause)? It will never hit 10000. Thanks, Tom
From: Plamen Ratchev on 6 Apr 2010 21:43 Since you have less that 10000 rows then TOP does not make sense. That should not affect performance. Take a look at the execution plan, it simple adds one Top expression. -- Plamen Ratchev http://www.SQLStudio.com
From: --CELKO-- on 6 Apr 2010 22:09 >> I assume this doesn't really affect performance (with or without the clause)? It will never hit 10000. << The proprietary TOP(n).. ORDER BY.. is bad enough since you are mimicking mag tape. The SQL engine does not know that you will never hit 10K; it has to validate that fact without external human knowledge. Every execution. Over and over. Reels of tape have physical limits (Duh! in the words of Homer Simpson). When we wanted to do sorts to multiple reel "results sets", we wanted reel #1 to precede reel #2, etc in sorted order. The algorithms for this are really neat! Ever see Poly-phase Merge run? Elevator sort? From all of your postings here, it would be well worth it to get a good foundation in RDBMS and start thinking in sets. Are you familiar with the "mechanical -> electro-mechanical -> electronic" paradigm shift in computer engineering engineering? Ghod! There is a good book in this!
From: tshad on 7 Apr 2010 03:45 "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:5a2aaf2a-0118-4fef-9a3e-bb9344909fea(a)i37g2000yqn.googlegroups.com... >>> I assume this doesn't really affect performance (with or without the >>> clause)? It will never hit 10000. << > > The proprietary TOP(n).. ORDER BY.. is bad enough since you are > mimicking mag tape. The SQL engine does not know that you will never > hit 10K; it has to validate that fact without external human > knowledge. Every execution. Over and over. > Since I didn't write this I can only guess at what they were doing. They needed to page and need to order the data. The TOP number had to be a number they would never hit since they wanted all the rows. > Reels of tape have physical limits (Duh! in the words of Homer > Simpson). When we wanted to do sorts to multiple reel "results sets", > we wanted reel #1 to precede reel #2, etc in sorted order. > > The algorithms for this are really neat! Ever see Poly-phase Merge > run? Elevator sort? > > From all of your postings here, it would be well worth it to get a > good foundation in RDBMS and start thinking in sets. > First of all, I didn't write this code. 2nd of all, this is being done with sets. This is part of paging (in SQL 2000). To use an Order By in a derived table you had to also have a TOP statement. This statement would do an Order By and then grab the nth number of Sets. > Are you familiar with the "mechanical -> electro-mechanical -> > electronic" paradigm shift in computer engineering engineering? Ghod! > There is a good book in this!
From: Gert-Jan Strik on 7 Apr 2010 14:31
tshad wrote: > > We have some Select statements that have TOP 10000 in them and they used to > have ORDER BY clauses in them but we took them out. > > We are also taking out the TOP 10000 out as well. > > I assume this doesn't really affect performance (with or without the > clause)? It will never hit 10000. > > Thanks, > > Tom Tom, Removing the ORDER BY can improve performance. Obviously, the meaning changes dramatically if there are more rows than the TOP clause specifies. Removing the TOP 10000 clause does not affect performance if there are fewer than 10000 qualifying rows and the optimizer successfully estimates (based on the table statistics) that there are fewer than 10000 rows. When I look at your post called "inner join on itself", then I am not entirely sure that the optimizer will be able to estimate the number of resulting rows acurately. You would have to check the row estimates in the query plan to verify this. -- Gert-Jan |