Microsoft KB Archive/140606

= FIX: ORDER BY DESC Queries May Cause High Logical Reads =

Article ID: 140606

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q140606



BUG# NT: 12057 (6.00) 15670 (6.50)



SYMPTOMS
A SELECT query containing an ORDER BY DESC clause can sometimes cause a high number of logical reads in SQL Server 6.0.



CAUSE
This can occur if the optimizer chooses to process the query by using an index on the ordered column and the WHERE clause of the query includes a range search such as the following:

SELECT * FROM inv_table WHERE inv_no BETWEEN 100 AND 500 ORDER BY inv_no DESC

You can determine whether the optimizer has chosen to use an index by using the SET SHOWPLAN ON command and viewing the showplan output as documented in Chapter 23 of the SQL Server "Administrator's Companion." You can view the number of logical reads for the query by using the SET STATISTICS IO ON command.



WORKAROUND
Restructure the query to use an intermediate temporary table such as the following:

SELECT * INTO #tmp_inv FROM inv_table WHERE inv_no BETWEEN 100 AND 500

SELECT * FROM #tmp_inv ORDER BY inv_no DESC

Note that this workaround should only be necessary in rare circumstances. Testing should be done to determine if the workaround uses fewer logical reads than the original query in your environment.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

Additional query words: sql6 performance

Keywords: kbbug kbfix kbprogramming KB140606

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.