================================================================================
SQL statement (Executed with 4.21 Build 11)
================================================================================

select * from Cardop where sequenceID not in (select SequenceID from
Cardophistory)

--------------------------------------------------------------------------------
Sub-query
--------------------------------------------------------------------------------

select SequenceID from Cardophistory

Tables Involved
---------------

Cardophistory (Cardophistory) table opened shared, has 4621751 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

SequenceID ASC

--------------------------------------------------------------------------------

Tables Involved
---------------

Cardop (Cardop) table opened shared, has 3 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Scan Expression Execution
-------------------------

The expression:

sequenceID not in select SequenceID from Cardophistory

is UN-OPTIMIZED and will be applied to each candidate row in the result set 
as
the result set is generated

Sub-Query Execution
-------------------

The expression:

sequenceID not in select SequenceID from Cardophistory

is OPTIMIZED and is estimated to cost 13 bytes per candidate row

================================================================================
>>>>> 0 rows affected in 329.64 seconds
================================================================================

---

 Comments

---

The problem was that the optimizer was not using the available descending index on the SequenceID column for optimizing the sub-query search, but instead was building a temporary index on the SequenceID column in the canned result set of the sub-query. This was much slower than simply using the descending index for the search of the live result set of the sub-query, which is what it should be doing.

---

 Resolution

---

Fixed Problem on 4/5/2005 in version 4.22 build 1

---

 Products Affected

---

DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source