Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1144

Re: Puzzling ORDER BY

Cross-posted to 2 groups.

Followups directed to: comp.databases.ms-sqlserver

Show all headers | View raw

Jeroen Mostert (jmostert@xs4all.nl) writes:
> Not particularly well, since the optimizer doesn't know what order you 
> actually want until the query is executed. You may or may not get
> acceptable performance out of it. I know the author explicitly wrote the
> article as a way of illustrating how to get *away* from dynamic SQL, and
> certainly, if you can get these queries to work well for you they're
> easier than cobbling together ORDER BY clauses as text, but
> unfortunately dynamic SQL often remains the best choice here where
> performance is concerned. The author demonstrates the query on 4 rows,
> but neglects to demonstrate it on 4 million rows. 
 
Well, if you have umpteen sort conditions, the odds are very good that the 
very most of them will call for sorting anyway. But, true, if a sort 
condition is used 80% of the time, and there is a index matching this 
condition dynamic SQL wins.

Although, these days you can avoid the syntax overhead of dynamic SQL
by using OPTION (RECOMPILE). (Requires SQL 2008 SP2 or SQL 2008 R2 SP1.)
Then SQL Server will compile the statement every time, and consider
the parameters as constants.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 09:37 -0700
  Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-21 21:08 +0200
    Re: Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 13:40 -0700
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:24 +0200
        Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-22 11:31 +0200
    Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-21 23:31 +0200
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:46 +0200
  Re: Puzzling ORDER BY rja.carnegie@gmail.com - 2012-07-02 03:36 -0700

csiph-web