Don't do work you don't have to do!

Thanks to my Hotsos colleague, Jan van Mourik, who sent me this example. The original SQL statement appeared to be spending alot of time doing sum(case...) constructs to count the number of times an order was created for each hour of a given day. The SQL was rewritten to simply get the counts by day and by hour and then pivot the results into the display format desired. The response time improvement was spectacular (as measured in two different production environments)!

Production Environment 1
OLD -- Elapsed: 00:00:07.01
NEW -- Elapsed: 00:00:00.06

Production Environment 2
OLD -- Elapsed: 00:00:19.06
NEW -- Elapsed: 00:00:01.06

Notice in the test output below how the LIO statistics for both the original and new SQL tests are identical. The plans are very similar as well. But, the big difference appears to be the time saved by removing the extra work required by the sum(case...) constructs.

The moral of the story here is that when writing SQL, you need to consider many different things and always make sure that you attempt to write the code to do the least amount of work possible to get the result you desire.

I say this to students in class all the time - Don't do work you don't have to do!

You can find the details of the tests (conducted on a 10.1 database) here.