Old wine in new wine-skins: taking advantage of new features for old applications

by Stéphane Faroult

It seems that developers are more likely to take advantage of new
database features in new applications than they are while maintaining
and upgrading old applications. It is a pity, because there are
very significant gains to be obtained at a low cost. I'll take a simple
example: a few years ago, I had to improve a program that had to display
on the user's screen both the first rows returned by a query and the
total number of rows found. The query was dynamically built, and was
executed twice: once to return the data, and once to count the rows, by
the mere substitution of a COUNT(*) to the select list. I significantly
improved performance by reworking the count queries and narrowing them
to the tables and conditions that were truly identifying the result set,
as opposed to the numerous joins that were required to return
complementary information. As a result, this query was taking a fraction
of the time of the "real" query, instead of doubling the execution time.

That was the best that could be done at that time.

I have met very recently something quite similar in a JDBC program. But
today, many DBMS products implement window (sometimes known as
analytical or OLAP) functions. Append something such as COUNT(*) OVER ()
to your select list, and your query will magically return the data AND
the total count in each row, at almost no cost if the query already
contains an ORDER BY clause. Any ordering requires to identify the full
result set before returning the data, counting the rows comes free. The
application code is simpler, and the database is queried once were it
was queried twice.
That's a cheap way to make a query almost twice as
fast as before.