Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in Deliveries (digital media, warranties, etc), but if an order does have a delivery, it will have only one. I am trying to combine data from these two tables into a third table (OrderInformation) to be used for reporting.

I can create the final table one of two ways:

  • Inserting all orders into the OrderInformation table first and then updating only the rows that have a matching Deliveries record in a second statement.

    OR

  • Inserting all values into the table at once using a LEFT OUTER JOIN between Orders and Deliveries.

I tested this out on my own data and the 'LEFT OUTER JOIN' query consistently outperformed the 'UPDATE' query by 200%, which surprised me.

Which option would perform better in general? And why does it perform better that way? Is performance contingent on join conditions, the type of data involved, etc?

share|improve this question

1 Answer 1

The first methods slower than the second one most like is because your new table, OrderInformation, does not contain a lookup index for the Order ID column. I believe that if you add an index both of these methods will execute with similar execution times

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.