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
    
If you tag "performance" and "optimisation" you should generally tag the RDBMS you are using too. –  Martin Smith Sep 13 at 16:50
    
Why are you inserting at all, instead of just querying to the base tables? Do you really have the tens of millions of Orders rows stored that would justify such denormalization/redundancy? –  Pieter Geerkens Sep 14 at 4:27
    
Orders is coming from System A, Deliveries from System B and the data is being merged and stored in a Data warehouse. That way the merged data can be queried without putting a strain on the two source systems. And there are tens of millions of rows. We've got the query working in such a way that its performance is acceptable, I was just wondering if there was a general rule for these types of scenarios or an explanation as to why the one performed better than the other. –  Chani_Of_Dune Sep 15 at 14:48

2 Answers 2

When Orders and Deliveries are both properly indexed, the second query will generally perform better. There are two main considerations here:

Generally speaking, and without seeing the details of your tables - what probably happens with the INSERT/UPDATE construct is that it will first allocate a number of pages in the database in the initial INSERT. But when you UPDATE those records, the added new information has to fit inside those existing rows in the OrderInformation table's pages, which causes page splits, which are very I/O intensive and can fragment your table and/or indexes.

The second query (single INSERT using LEFT JOIN) is considerably better practice, since it minimizes the locking requirements. Imagine if another simultaneous process creates a new row in Orders and then a related row in Deliveries between the time of the INSERT and the UPDATE statements in the first example - you'll get a foreign key constraint violation unless you've put all of this in a potentially large transaction, which itself implies a bit of overhead.

share|improve this answer

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
1  
A lookup index on OrderInformation will only improve performance on the UPDATE statement, but leaves a number of other performance bottlenecks unsolved, including transactional integrity, page splits with resulting table/index fragmentation and the simple fact that you're running two statements instead of one. –  Daniel Hutmacher Sep 13 at 16:23

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.