HXTT JDBC Products: MS Access JDBC, Cobol JDBC, DBF JDBC, MS Excel JDBC,CSV JDBC, Text JDBC, Corel Paradox JDBC, PDF JDBC, Word JDBC, XML JDBC

Poor DBF Left Outer Join Performance	Martin Ross	2006-02-24 00:00:00
This query performs terribly.... As in more than 10 minutes... select count(*) from YM_INV this_ left outer join YM_DOC yorkmedica2_ on this_.DOCTOR=yorkmedica2_.CODE left outer join YM_PAT yorkmedica3_ on this_.ID=yorkmedica3_.ID where this_.SERV_DATE>'2005-01-01' These tables can individually be accessed rather quickly. Let me know if you want the schema for the tables. Thanks, Martin
Re:Poor DBF Left Outer Join Performance	Martin Ross	2006-02-24 00:00:00
It seems to be stuck in this part of the stack trace Thread [main] (Stepping) cp.a(int, bp[], bu, g, int, boolean, int, String) line: not available dg.a(int, boolean, int, String) line: not available cp.a(boolean, cx, aa, int, boolean, boolean, int, String) line: not available u(ba).a(aa, int, b9, m, Object) line: not available cq(aa).a(int, b9, m, Object) line: not available cq.executeQuery() line: not available [local variables unavailable] NonBatchingBatcher(AbstractBatcher).getResultSet(PreparedStatement) line: 139 CriteriaLoader(Loader).getResultSet(PreparedStatement, boolean, boolean, RowSelection, SessionImplementor) line: 1669 CriteriaLoader(Loader).doQuery(SessionImplementor, QueryParameters, boolean) line: 662 CriteriaLoader(Loader).doQueryAndInitializeNonLazyCollections(SessionImplementor, QueryParameters, boolean) line: 224 CriteriaLoader(Loader).doList(SessionImplementor, QueryParameters) line: 2145 CriteriaLoader(Loader).listIgnoreQueryCache(SessionImplementor, QueryParameters) line: 2029 CriteriaLoader(Loader).list(SessionImplementor, QueryParameters, Set, Type[]) line: 2024 CriteriaLoader.list(SessionImplementor) line: 94 SessionImpl.list(CriteriaImpl) line: 1552 CriteriaImpl.list() line: 283 YorkMedicalDocDB(DocDBGeneric).getServices(Criteria) line: 47 BridgeDoctorDBCommand.execute() line: 278 BridgeDoctor.main(String[]) line: 736
Re:Re:Poor DBF Left Outer Join Performance	Martin Ross	2006-02-24 00:00:00
By stuck I mean that the cp.a method calls lots of other methods as it executes, but driver is basically looping in this method.
Re:Re:Re:Poor DBF Left Outer Join Performance	HXTT Support	2006-02-28 00:00:00
HXTT DBF had to do full table search since there's no suitable index for your tables. Please download the latest package which will uses automatic temporary index to quicken join sql. According to our test: YM_INV (20000 rows), YM_DOC(500 rows), and YM_PAT (2000 rows) without any prefined index. Your sql will get a result in 2s which includes building temporary index operation.
Re:Re:Re:Re:Poor DBF Left Outer Join Performance	Martin Ross	2006-02-28 00:00:00
Thanks! Will it always create temporary tables for joins or will it do a cost estimation first?
Re:Re:Re:Re:Re:Poor DBF Left Outer Join Performance	HXTT Support	2006-02-28 00:00:00
It will only create only the necessary index. For your sql, it will create inde x on yorkmedica3_.ID and yorkmedica2_.CODE. If you is using inner join, not left out join, it will transform your sql to YM_INV this_, YM_DOC yorkmedica2_ , YM_PAT yorkmedica3_ where this_.DOCTOR=yorkmedica2_.CODE and this_.ID=yorkmedica3_.ID and this_.SERV_DATE>'2005-01-01', then it will adjust table order sometimes, and choose the suitable index. If you run many join sqls in a short period, the following sql will utilize the temporary index created by the first join sql.

Email:

webmaster@hxtt.com