One of the things I like to do when a new version of a database engine is released is to compare the performance of different query techniques. Doing this comparison results in information that helps in understanding the optimizations that have been introduced with each release. I find it helpful to dispel assumptions of how certain syntax typically performs under certain circumstances. It turns out that the typical performance is not the same from one version to the next.
For example, in older versions of SQL Server I would prefer using the EXISTS operator over IN when testing for a record in TABLEA that has one or more matching records in TABLEB. If you run a query using these two different syntaxes in SQL Server 2008 and SQL Server 2014 the results are different. In SQL Server 2014, the query plan using either syntax is identical. This is not the case in 2008.
Again, using SQL Server as an example, I would compare the use of Memory Variables, Hash Temp Tables, and CTEs for building intermediate sets in a stored procedure. All of these three temporary memory objects have been available since SQL Server 2005. However, the performance characteristics have not been the same. In earlier versions the Hash Temp table method often out performed CTE or Table Variables for performance. In fact, it was true even for memory variables with unique constraints meeting the requirements of join criteria of filter criteria. Today, this is no longer the case.
Hierarchies are another kind of query that have shown optimization over time. Not all SQL engines support hierarchies using native SQL Code. Still, this is something that has demonstrated improved performance from one release over the next for those engines that do support hierarchical queries.
Queries using data structures with other than standard date and time values or spatial data types have continued to improve over time.
I’m thinking about creating a suite of standard tests one could use to measure performance of an engine against itself from one release to the next. The scenarios I have listed could be a starting point for a series of tests. What would you add? Why not leave your suggestions in our comments and see what comes up.
Cheers,
Ben