FIX: Query Slow w/ ORDER BY DESC on Table w/ Composite Index (167779)
The information in this article applies to:
This article was previously published under Q167779
BUG #: 16669 (6.5)
SYMPTOMS
An ORDER BY DESC query against a table that has a non-clustered composite
index may take significantly longer to run than the equivalent ASC query.
This difference is especially noticed in cases in which a WHERE clause
contains an equivalency test of an indexed column (including the most
significant) and a constant value.
WORKAROUND
To work around this problem, select the result set (ASC) into a temporary
table, then run the ORDER BY DESC query only on the results of a select
from the temporary table.
Another option, for certain numeric columns, is to multiply the key by -1
and then run the ORDER BY ASC query.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5. This problem has been corrected in U.S. Service Pack 3 for
Microsoft SQL Server version 6.5. For more information, contact your
primary support provider.
| Modification Type: | Major | Last Reviewed: | 10/16/2003 |
|---|
| Keywords: | kbBug kbfix kbProgramming kbusage KB167779 |
|---|
|