Icon View Incident Report

Serious Serious
Reported By: Ron Clayton
Reported On: 5/11/2005
For: Version 4.19 Build 1
# 2047 Query Optimizer Using Wrong Costs for Expressions Joined by AND Operator

I am currently evaluating DBISAMS's performance for use in an upcoming project but have come across a strange issue with the query optimizer that I hope you can help me with. I have a main table with about 400,000+ records and indexes on two fields. Here's the strange thing, performing the same query on the indexed fields with different values yields different optimizations and therefore totally different performance. The query is very fast when optimized and not too bad when partially optimized but when the two are "mixed" the query is significantly slower. So I really have two questions, one, why aren't all the queries being fully optimized and two, why is there is significant difference when mixing full and partial optimizations. Here are the details of my data and environment as well as the query plans.

SITEINFO table:
- 52 fields, 431222 records
- SITECITY_INDEX is an index on the SITECITY string field with duplicate byte compression
- SITEZIP_INDEX is an index on the SITEZIP integer field with no compression

Query Results: 
================================================================================ 
SQL statement 
================================================================================ 
select * from siteinfo 
where ( (SITECITY = 'RIO LINDA') AND (SITEZIP = 95673) ) 

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

siteinfo (siteinfo) table opened shared, has 431222 rows 

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

Result set will be live 
Result set will consist of one or more rows 
Result set will be ordered by the primary index for the table siteinfo 

Non-Optimized WHERE Clause Execution 
------------------------------------ 

The expression: 

SITECITY = 'RIO LINDA' AND SITEZIP = 95673 

has been rewritten and is OPTIMIZED, costs 186287904 bytes, and will be applied 
to the siteinfo table (siteinfo) before any joins 
================================================================================ 
 Execution time: 0.043 sec 
================================================================================ 




================================================================================ 
SQL statement 
================================================================================ 
select * from siteinfo 
where ( (SITECITY = 'SACRAMENTO') AND (SITEZIP = 95814) ) 

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

siteinfo (siteinfo) table opened shared, has 431222 rows 

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

Result set will be live 
Result set will consist of one or more rows 
Result set will be ordered by the primary index for the table siteinfo 

Non-Optimized WHERE Clause Execution 
------------------------------------ 

The expression: 

SITECITY = 'SACRAMENTO' AND SITEZIP = 95814 

has been rewritten and is PARTIALLY-OPTIMIZED, costs 2076228 bytes, and will be 
applied to the siteinfo table (info) before any joins 
================================================================================ 
Execution time: 0.812 sec 
================================================================================ 



================================================================================ 
SQL statement 
================================================================================ 
select * from siteinfo 
where ( (SITECITY = 'RIO LINDA') AND (SITEZIP = 95673) ) OR ( (SITECITY = 
'SACRAMENTO') AND (SITEZIP = 95814) ) 

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

siteinfo (siteinfo) table opened shared, has 431222 rows 

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

Result set will be live 
Result set will consist of one or more rows 
Result set will be ordered by the primary index for the table siteinfo 

Non-Optimized WHERE Clause Execution 
------------------------------------ 

The expression: 

SITECITY = 'RIO LINDA' AND SITEZIP = 95673 OR SITECITY = 'SACRAMENTO' AND 
SITEZIP = 95814 

is UN-OPTIMIZED, costs 186287904 bytes, and will be applied to the siteinfo table 
(siteinfo) before any joins 
================================================================================ 
Execution time: 1.64 sec 
================================================================================ 



Comments Comments
The query plan should not indicate the total size of the index as the total cost for the first query, rather it should be much lower. This is the root cause of the major slowdown in the third query - it is using the wrong cost figure for the AND expressions and since it is so high it is simply punting and saying that it is quicker just to scan the entire table in a brute-force fashion.


Resolution Resolution
Fixed Problem on 5/11/2005 in version 4.20 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
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

Image