FIX: ORDER BY DESC Queries May Cause High Logical Reads

ID: Q140606

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5


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 : kbprg SSrvProg SSrvTran_SQL kbbug6.50 kbbug6.00 kbfix6.50.sp2
Version : 6.0 6.5
Platform : NT WINDOWS
Issue type :