Improving performance of your RIGHT query

~ franklinyamamoto

I always get a kick out of when a query needs to perform a look up on the last 4 digits of a column because it usually ends up being something radioactive like a credit card number or a social security number.  Either way, trying to do a lookup by the RIGHT most characters of a query is difficult because those characters cannot be indexed with a normal index.

I wrote a recent blog post on improving performance of a LEFT query.  The gist of it was to use a wildcard query; however, leading wildcards are not sargable and can’t easily be used to improve the performance of a RIGHT query for the reason mentioned above.

There are a couple options for improving RIGHT queries:

The first option can be a real hassle since you’ll need to make sure the new column is always kept up to date.  It can lead to code changes or even triggers (yuck) to keep it in sync.

The second option is probably the easiest to implement and won’t require code changes.

Third option might work, but it is more meant for tuning of queries where you don’t know how many characters on the RIGHT you are searching for.  For example this option can optimize searching the last 5 or last 7 character searches.  The drawback is there is some level of code change involved in this.

I’ll stick to discussing the second option for this blog post.

The demo query

The demo query is pretty straightforward, we’re just looking for account numbers that end with the value 0001.  I didn’t bother to parameterize this since it doesn’t really add to the point I’m trying to make here.

select * from CustomerAccount WHERE RIGHT(AccountNbr, 4) = '0001'

The results of this are a clustered index scan:

This query took 270 seconds to execute.

Creating an indexed computed column for Last 4 (RIGHT) query

If you’ve regularly been reading my recent posts, a lot deal with sargability.  In many of these, indexed computed columns are mentioned as a quick zero code change fix.  I don’t see indexed computed columns as a hammer looking for a nail, instead I see it as an option when you are painted into a corner.

ALTER TABLE CustomerAccount ADD AccountNbrRight AS RIGHT(AccountNbr, 4)

CREATE INDEX IX_CustomerAccountAccountNbrRight
ON CustomerAccount (AccountNbrRight)

The above statements added an indexed computed column to the table.  After adding this, we get a plan with a seek on the new index:

This query took 63 milliseconds to execute.

No modifications were required to the original query.  This is one of the nice benefits of indexed computed columns.

Happy tuning!