SQL with INNER JOIN run very slow

[Tianjin]

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]

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.