Icon View Incident Report

Serious Serious
Reported By: David Manton-Hall [Now Figure Pty Ltd]
Reported On: 4/29/2010
For: Version 4.29 Build 3
# 3200 Left Outer Joins with Un-Optimized Non-Join Conditions Executing Slowly

Performance problems with Left Outer Join that is constrained by a logical operation on the table being joined to.

There seems to be a quite serious problem with LOJs that has been introduced between 4.26b3 and 4.29b3.
I have included a zip with simplified tables and an sql file that shows the problem that is taking some 700seconds (was sub-second/immeasurable in 4.26b3).

select TA.SourceID, TB.BID, TB.Quantity, TC.TotalCost
  from Source mC
  join TA on mC.SourceID = TA.SourceID
  join TB on TA.BID = TB.BID
  left outer join TC     on TA.SourceID = TC.SourceID 
  left outer join TB TBI on TB.BID = TBI.FromBID and not TBI.Condition



Comments Comments
This issue was caused by the improvement implemented in incident report #2879 for 4.27 Build 2. The:

not TBI.Condition

expression was the cause of the slowdown.


Resolution Resolution
Fixed Problem on 4/30/2010 in version 4.29 build 4


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