FIX: Index Incorrectly Chosen When Highest Key Value Used
  
PSS ID Number: Q105347
Article last modified on 10-31-1994
 
4.20
 
OS/2
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2 for OS/2
----------------------------------------------------------------------
 
BUG# OS/2: 1666 (4.2)
 
SYMPTOMS
========
 
A query that uses the highest key value of an index runs very slowly.
Using showplan indicates that the optimizer is not using the index when
it should, or using it when it should not.
 
This only happens when the high value is used in a greater than
comparison (>), or in the high value of a between statement. Using the
highest key in an equality test (=), or a value one above or below will
show the index being used correctly.
 
CAUSE
=====
 
If the highest key value is used in a greater than statement, the
optimizer will incorrectly not use a beneficial index, and perform a
table scan instead.
 
If the highest key value is used as the high value in a between
statement, the optimizer will incorrectly use an index when it should do
a table scan. Using the index, in this case, causes more page I/Os to be
performed than would be in a table scan, causing the query to run much
longer than it should.
 
WORKAROUND
==========
 
For lookup tables and other tables in which the data content is known
beforehand, make sure that the highest value is not used in the
comparison operator.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in SQL Server version 4.2 for
OS/2. This problem was corrected in SQL Server version 4.2b. For more
information, contact your primary support provider.
 
MORE INFORMATION
================
 
The following script demonstrates the behavior.
 
   use pubs
   go
   create table optim (
        number int      not null,
        filler char(10) not null)
   go
   declare @counter int
   select @counter=1
   while @counter < 768
   begin
        insert optim values (@counter, 'some text')
        select @counter=@counter + 1
   end
   go
   create nonclustered index number_index on optim (number)
   go
   set showplan on
   set statistics io on
   go
   select * from optim where number > 766
   go
   select * from optim where number > 767
   go
   select * from optim where number > 768
   go
   /* Remove the noexec on if you want to see the io    */
   /* counts, but at least you will get to see the plan */
   /* without getting 70 rows back.                    */
   set noexec on
   go
   select * from optim where number between 700 and 766
   go
   select * from optim where number between 700 and 767
   go
   select * from optim where number between 700 and 768
   go
   set noexec off
   go
   set showplan off
   set statistics io off
   drop table optim
   go
 
Additional reference words: 4.20 slow
KBCategory: kbprg
KBSubcategory: SSrvServer
 
=============================================================================
 
Copyright Microsoft Corporation 1994.
