2

What kind of overhead should I expect when replacing a single complex query with multiple simple queries?

My goal is to improve the readability and portability of all SQL code so I will be favoring simple constructs and replacing database-specific extensions with ANSI SQL whenever possible.

For example:

  • Imagine the client is invoking Dynamic SQL (as opposed to stored procedures)
  • Scenario 1: Client invokes: INSERT INTO employee SELECT name FROM user
  • Scenario 2: Client invokes:
Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

Scenario 1 is not a complex query, but for the sake of argument let's pretend it is. Scenario 2 achieves the same result using multiple (simpler) queries. What kind of overhead can I expect for Scenario 2 versus Scenario 1? Is it something I should worry about or is it negligible?

UPDATE: https://stackoverflow.com/a/14408631/14731 makes a good point. Decomposing queries by hand hard-codes an execution plan instead of allowing the database's optimizer to choose. That being said, it's still not clear whether the overhead is meaningful.

2 Answers 2

3

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios?

Scenario 1 (INSERT .. SELECT)

The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given:

  • The cardinality of the bulk operation
  • The various constraints / triggers that will be invoked
  • The amount of storage that needs to be reserved in the new table
  • etc etc.

Scenario 2 (SELECT, and the N x INSERT)

The database has no clue about the various prepared statements that you are going to send after the SELECT. Even if it were smart enough to collect long-term statistics and heuristics about what's going to come after your SELECT, it would be unwise to assume anything about the subsequent load. So, effectively, this scenario is most often much much worse than the other.

There are some remarks to place, though:

  • Even if you have a good application-design reason to partition INSERTs like that, you should then probably batch-send them to the database
  • One good reason to partition INSERTs is the fact that you can have more fine-grained control over transaction lengths. It is generally not good to insert millions of records in a long-running transaction with logging turned on. So, either turn off logging, or commit after N inserts

Conclusion

The above are remarks regarding your two specific scenarios. Real-world scenarios aren't that simple, but you've also stated that in the question. The point I'm trying to make is that in many cases, you should let the database perform bulk data operations, because that's what it's very good at.

11
  • I ran scenario 1 and 2 against H2 using 1 million rows and was surprised to discover that scenario 2 was twice as fast as scenario 1. I'm going to repeat this test against PostgreSQL and see what I get. I see a lot of theories online about why X or Y is better, but very few people seem to actually test their theories.
    – Gili
    Oct 1, 2014 at 19:02
  • Well, the results are in. Under PostgreSQL 9.3, scenario 1 takes 3 seconds, scenario 2 takes 3.5 minutes without batching, 35 seconds with batching. In other words: scenario 1 is 10-100x more efficient than scenario 2. I guess we're stuck with complex queries for the foreseeable future. Can you suggest any other optimization aside from batching? Changing the default fetch size didn't make a noticeable difference.
    – Gili
    Oct 1, 2014 at 20:02
  • Another interesting point (confirmed by test): CallableStatement (stored procedure) has the same performance as PreparedStatement.
    – Gili
    Oct 1, 2014 at 20:25
  • @Gili: "but very few people seem to actually test their theories": Also, very few people resist in jumping to conclusions about other people's thoughts ;-) You can take my word that I have a bit of experience with Oracle, and that I tend to assume that other databases are as good as Oracle as well. H2 is really not a database on which you should base general benchmarks... Nonetheless, see the caveat in my remarks, which explains that if logging is activated, bulk loads might indeed be a bad idea. Another idea is to commit after N records to keep logging overhead reasonably low
    – Lukas Eder
    Oct 1, 2014 at 21:56
  • 1
    @Gili: Btw, here's a good article for you to read
    – Lukas Eder
    Oct 1, 2014 at 22:01
1

For your specific example scenario 1 keeps all the data on the server. Scenario 2 will require the data to be packaged, sent over the wire to the client, where is must be buffered (and, perhaps, spilled to disk) then un-buffered, re-packaged and sent back to the server, where it will finally be processed. This network time adds up. Do this often enough with large enough rowsets and you will see latency mount. Do it one row at a times (as your example shows) and you'll be ruing your decision for the rest of your natural life. Submitting a batch is usually faster than submitting statements individually.

Scenario 1 is a single statement with an implicit transaction to keep the data consistent. Scenario 2 will require an explicit transaction to achieve the same consistency. Those locks will have to be held for the duration of the round trip to the client, which will cause blocking.

If you split a complex query into simpler statements which are all submitted as a batch there are gains and losses. For example, splitting

select
    <lots of rows>
from <really complex predicate>

into

select
    <some columns>
into #T1
from dbo.Table1 as a
where <whatever>;

select
    <other stuff>
from #T1
inner join dbo.Table2 as b
where <etc.>

Will have the obvious cost of the write to and read from #T1. There may be a benefit, however, if you can index #T1 in a way which helps the second query, perhaps if complex manipulation was performed on Table1's values.

Stored procedures can suffer from parameter sniffing. This can sometimes be rectified by recompiling the procedure at each execution. Recompiles can be expensive. By splitting a complex query into several simpler ones you can put the recompile hint on just the statement(s) that benefits from it.

An optimizer will not search indefinitely for the best execution plan. It will eventually time out and run with the best available at that point in time. The work the optimiser must do is exponentially larger with the complexity of the query. Having simpler queries may allow the optimizer to find the best plan for each individual query, providing a lower-cost execution overall.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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