Icon View Incident Report

Serious Serious
Reported By: Nenad Steric
Reported On: 9/27/2009
For: Version 4.29 Build 1
# 3077 Certain LEFT OUTER JOIN Queries Can Experience Lower Performance Than Necessary

I have a query which is quite fast with an older version of dbisam, and with the current version embeded but really slow with c/s with Dbisam 4.29b1

There are 2 tables : a short table A which has 12 IDs, their Names and a active flag and a much larger Table B and C which have compound keys (can't change this) which consists of (ID1, ID2, ID_from_table_A) and some data e.g. Nr

I want to get all the items from A with an indication if there is a record matching in B.

"1" would be a parameter of the query

This is quite fast with dbisam 4.26 Build 2 even with C/S, on 4.26 Build 3 is very slow..

The difference it seems is this from the Plan

The expression:
B.ID1 = 1 and B.ID2 = 1 and a.ID = B.ID_from_table_A is PARTIALLY-OPTIMIZED

and in the new version :

The expression:
a.ID = B.ID_from_table_A is UN-OPTIMIZED

The expression:
B.ID1 = 1 and B.ID2 = 1 is UN-OPTIMIZED

select
a.*,B.Nr as p1, C.Nr as p2 from TableA A
left join TableB B on
(B.ID1 =1
and B.ID2 =1
and a.ID=B.ID_from_table_A)
left join TableC C on
(C.ID1 =1
and C.ID2 =1
and a.ID=C.ID_from_table_A)
where (a.active=1 or A.Nr<>null)



Comments Comments
This would only occur if the LEFT OUTER JOIN contained non-join expressions, and those expressions were optimized. DBISAM was converting them to an un-optimized join scan condition even though they should have been left alone. This issue was caused by the improvement implemented in incident report #2879 for 4.27 Build 2.


Resolution Resolution
Fixed Problem on 10/1/2009 in version 4.29 build 2


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 ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image