SQL That Meant Well: Where Functions are Applied

The Situation

This statement is actually a delete. It cascades to at least 300 other tables in a WebSphere Commerce database. The original query here was the default syntax supplied by IBM with WebSphere Commerce back with WCS 6. I’m not sure if it survives in this form in WCS 7 or 8, but the modification here has become one of my go-to things to look for in problem queries that aren’t helped by easily identifiable indexes.

In a WCS database, I call MEMBER “the one table to rule them all”, because it has direct foreign keys (with deletes cascading) to at least 300 tables, and likely others indirectly from those 300.

Additionally, depending on how WCS is configured, “guest users” can be added to the MEMBER table at a high rate. If improperly configured, sometimes literally they are added faster than it is possible to delete them.

Not only was this delete running unreasonably slow – it was also causing lock timeouts on the MEMBER table while running because of the slowness.

Those are a lot of application specific details that you don’t need to understand the underlying concept here, which applies across many SQL statements in the real world.

I am not sharing the explain plans here because you can scroll for days and not reach the end – they are some of the ugliest I have seen.

SQL Statement or Fragment Found in the Wild

delete from member 
where member_id in 
(select users_id 
    from users T1 
    where registertype='G' 
        and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= 30 
        And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') 
        and (users_id > 0))

A Better Way to Get the Same Results

By rewriting the query, in one database, there was a 12X improvement in the speed of the delete, which is HUGE.

delete from member 
where member_id in 
(select users_id 
    from users T1 
    where registertype='G' 
        and prevlastsession < current timestamp - 30 days 
        And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') 
        and (users_id > 0))

The Difference

The difference here is in this clause:

(days(CURRENT TIMESTAMP) - days(prevlastsession)) >= 30

vs.

prevlastsession < current timestamp - 30 days

There is a slight logical difference here. These might be off by nearly one day, but we're generally talking 30 or more days worth of data, and if that's a concern, the number of days can simply be increased by one.

However, they both have the effect of identifying rows where PREVLASTSESSION is older than 30 days old.

If we look at what we’re asking Db2 to do with each query (in the clause we're focusing on), we can see the difference:

Original Query: Apply the DAYS function to the value of the CURRENT TIMESTAMP special register and to the prevlastsession for EVERY row in the base table. Applying the function to every row of the table like this negates the use of an index entirely if we are unlucky. If we are only mildly unlucky, it requires a leaf page scan instead of direct index access by navigating the b-tree. Then subtract those two, and return the rows where this value is less than 30.

Modified Query: Calculate the value of the CURRENT TIMESTAMP special register minus 30 days. Then use the b-tree index on PREVLASTSESSION to find the rows where PREVLASTSESSION is less than this value. Finally, use the RIDS identified to fetch the whole row (required for deletes).

The difference may not be so drastic looking for 7 rows, but this table is millions of records, and the difference becomes much more drastic at scale. Even with 7 rows, I've eliminated reading or evaluating 40% of the rows I had to evaluate with the original query.

This same concept applies across hundreds of queries in most databases, and boils down to: Don't apply functions to column data when you can apply them to static comparison values instead. I also think of this as trying to apply functions on the right side of the equals sign instead of the left, but that assumes your column name is on the left. Not every query can be re-written this way, but an amazingly large number can be. We can also avoid applying functions to column data by adding a generated column, or make such comparisons more efficient with a function-based index.