2010/3/20 Bryan Pendleton <bpendleton.de...@gmail.com>: > Witold Szczerba wrote: >> >> 2010/3/19 Bryan Pendleton <bpendleton.de...@gmail.com>: >>>> >>>> SELECT count(*) >>>> FROM contract co >>>> LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND >>>> insured.id=co.insured_id >>> >>> In this query, how does the result (the returned count) change when you >>> change LEFT JOIN to JOIN? >> >> The numbers are almost always the same, it is possible for some >> contracts not to have insured subjects temporally. >> Right now the results are: 17667 (left join) and 17666 (inner join). > > Then it doesn't sound like the performance problem is due to a different > amount of data in the outer join case. > > Perhaps it is that the presence of the outer join is preventing some > optimization and thus Derby is choosing an inferior query plan. > > Have you compared the query plans of the two queries in the empty table > case? > > Also, have you experimented with updating the statistics or compressing > the tables in your full-size database? If the statistics are out of date, > Derby has been known to choose a bad query execution plan. > > thanks, > > bryan > >
Finally, I have both query plans, one for: SELECT count(*) FROM contract co JOIN user_contract uc ON co.id=uc.contract_id JOIN ContractSubject insured ON insured.contract_id=co.id AND insured.id=co.insured_id (Executed successfully in 0.959 s.) http://witoldsz.strefa.pl/queryplan-1.txt and second: SELECT count(*) FROM contract co JOIN user_contract uc ON co.id=uc.contract_id LEFT JOIN ContractSubject insured ON insured.contract_id=co.id AND insured.id=co.insured_id (Executed successfully in 2,098.677 s.) http://witoldsz.strefa.pl/queryplan-2.txt The problem is that I look at those plans - it is like black magic to me, maybe you can see something? I issued SYSCS_COMPRESS_TABLE for all tables involved, but have not tried SYSCS_UPDATE_STATISTICS as this procedure does not exist in Derby 10.4. I will upgrade to 10.5 and try that. Thanks, Witold Szczerba
