Smart Logic for Conditional WHERE Clauses

One of the key features of SQL databases is their support for ad-hoc queries—that is, dynamic SQL. That's possible because the query optimizer (query planner) works at runtime; each SQL statement is analyzed when received to generate a reasonable execution plan. The overhead introduced by runtime optimization can be minimized with bind parameters—Section 3, “Bind Parameter” covers that in detail.

The gist of that recap is that databases are optimized for dynamic SQL—so, use it.

There is, however, one widely used practice to avoid dynamic SQL if favour of static SQL—often because of the "Dynamic SQL is slow" myth. Unfortunately that practice is doing more harm than good.

So, let's imagine an application that queries the employees table (see Appendix C, Example Schema). It allows searching for subsidiary id, employee id and last name (case-insensitive) in any combination. The following statement support all these variants:

SELECT first_name, last_name, subsidiary_id, employee_id

  FROM employees

 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )

   AND ( employee_id      = :emp_id OR :emp_id IS NULL )

   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

The statement uses Oracle style named placeholder for better readability. All possible filter expressions are statically coded in the statement. Whenever a filter isn't needed, the respective search term is NULL so that the particular filter becomes ineffective.

It's a perfectly reasonable SQL statement. The use of NULL is even in line with its definition according to the three valued logic of SQL. Regardless of that, it is still amongst the worst things for performance.

Read more: Use the index, Luke

Posted via email from Jasper-net