×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL with INNER JOIN run very slow

thread318-1566597

SQL with INNER JOIN run very slow

SQL with INNER JOIN run very slow

(OP)
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

RE: SQL with INNER JOIN run very slow

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close