Making non-SARGable queries seek, without changing the query

We’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance.

Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s because you’ve got a third-party app and simply don’t have access to the code. But you do have access to the database…

Bad query, good query

I’m going to use the pubs database for this example, because I love low laughably small it is.

First, let’s look at that non-SARGable query that is causing all sorts of performance problems:

SELECT lname
FROM pubs.dbo.employee
WHERE LEFT(lname,1) ='J';

The plan for this does a table scan, because that LEFT() function prevents using any index.

If I were able to re-write the query, I could simply change that so that the function call isn’t wrapping the column name at all, in this case, using a LIKE expression:

SELECT lname
FROM pubs.dbo.employee
WHERE lname LIKE 'J%';

And now my query does a seek on the index, as expected!

But what if you can’t change that query?

So, let’s go back to the problem of a third-party application where I physically can’t change the query.

What if I can change the table?

DISCLAIMER: If the application developer has non-SARGable predicates causing problems, they might also have other bad habits, like using SELECT * or doing an INSERT without including the column list. You’ll need to test this option to make sure you don’t fix this query and break a different one.

We’re going to add a computed column, and index that computed column. We aren’t persisting the computed column itself–that would make it live in the clustered index with all the other columns, and we don’t want that. We’re going to create a non-clustered index so we can seek on the computed values–essentially manually persisting the column.

The important thing here is that the function we use in our index must match the function used in the query. If the function changes, we’re going to have to create a new computed column & index.

ALTER TABLE dbo.employee 
     ADD lname_firstchar AS LEFT(lname,1);
CREATE INDEX ix_employee_lname_firstchar 
 BbbbON dbo.employee(lname_firstchar);

Now, when we go back & run our original, non-SARGable query:

SELECT lname
FROM pubs.dbo.employee
WHERE LEFT(lname,1) ='J';

We get a beatifull index seek on our new index:

Now, I wouldn’t use this technique intentionally. I’d always prefer to re-write the query so I don’t have to maintain that extra index. But in response to an emergency problem while you develop/deploy a code fix, or argue with your vendor to fix their code, this can be a great way to save the day when you can’t fix the query.

The post Making non-SARGable queries seek, without changing the query appeared first on Andy M Mallon - AM².