SQL with INNER JOIN run very slow

Tianjin · Aug 27, 2009

I have two sql script as below that can generate same result. However, for unknow reason, the second one run almost 10 times faster than the one with "INNER JOIN", anybody can explain why? From my understanding,sql script that using INNER JOIN refer the newest sql syntax and it should run faster.
My question is if I have use left join or right join in Sql script, I don't know how to write it by using old syntax as script A shown.

Sql A(slow): SELECT h.ORDNUMBER FROM (OEORDH h INNER JOIN OEORDD d ON h.ORDUNIQ=d.ORDUNIQ)
sql B(fast): SELECT ORDNUMBER FROM OEORDH h, OEORDD d WHERE h.ORDUNIQ=d.ORDUNIQ AND d.COMPLETE=0

Thanks
TianJin

DjangMan · Sep 4, 2009

Pervasive is still BTrieve under the hood. The second option must be being optimized differently and is using the best index for the join. You didn't specify the d.COMPLETE on your first example so assuming that you left it out the detail table doesn't have an index on the COMPLETE field. When you don't have an index the database engine can be forced to perform a table scan where it reads every record in the table.

You can add indexes to the Accpac database - just be sure to remove them before you upgrade and replace them afterwards.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL with INNER JOIN run very slow

Tianjin

Technical User

DjangMan

Programmer

Similar threads

Part and Inventory Search

Sponsor