View Incident Report

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

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

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

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

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap