View Incident Report

Serious

Reported By: Oliver Bock
Reported On: 10/21/2004
For: Version 4.13 Build 1

# 1881 Certain SQL Joins Performing Much Slower

This query takes 35s on v4.13 and 0.9s on v4.12. The v4.12 query plan declares both joins to be optimized, but on v4.13 it says that the second left outer join is only partially optimized. I think that the first left outer join sets T2.CashFlowID to null where it cannot match anything in the CashFlow table. Unfortunately there are many null CashFlowIDs in StatementDet and thus there is the potential for a cartesian join on null=null when it runs the second join. The 'T3.CashFlowID is not null' used to protect against this situation, but it appears that it no longer helps.

select distinct T1.SourceID
from Source T1
left outer join CashFlow T2 on T1.SourceID = T2.SourceID
left outer join StatementDet T3 on T3.CashFlowID is not null and 
T2.CashFlowID = T3.CashFlowID and T3.Superseded is null

Comments

This was caused by the inclusion of the I/O cost consideration when rewriting join expressions in 4.13. Due to the nature of the cost sampling done for the joins (the first record), it was possible for the optimizer to make the wrong decision. The fix was to remove this behavior and have it be available as an option via the JOINOPTIMIZECOSTS keyword in SQL statements.

Resolution

Fixed Problem on 10/25/2004 in version 4.14 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap