Some times when you are working with 3rd party vendor code, or maintaining legacy code, it can be tricky especially when a query doesn’t perform. Modifying the query by adding hints is one way to address the issue but more than often you will find your hands are tied and unable to change a single line of code. I had to deal with something like that once and fortunate enough to have computed columns at my disposal.
A computed column although easy to understand is probably not the first tool people associate with performance tuning. Yet it can be a very powerful weapon in your performance tuning arsenal. I will demonstrate with an example.
Say you are working with a table and a view like the ones below.
if object_id('trade_id_lookup') is not null
drop table trade_id_lookup
create table trade_id_lookup
trade_id bigint not null identity(1, 1) primary key
, system_id int not null
, portfolio sysname not null
, product_id int not null
if object_id('v_trade_id_lookup') is not null
drop view v_trade_id_lookup
create view v_trade_id_lookup
cast(trade_id as nvarchar) as 'trade_id'
insert trade_id_lookup (system_id, portfolio, product_id)
values (1, 'portfolio', 9)
The view is then used throughout the system for looking up trade attributes given a trade_id.
select system_id, portfolio, product_id
where trade_id = '999'
It all looks quite innocent until you start to interrogate the execution plan.
A clustered index scan, why? Due to the CAST expression embedded in the view, the optimizer is not able to perform an index seek because it had to convert every single row. If you hover over the index scan icon, it will show something like:
Bear in mind that I intentionally stripped away all the non essential code for the demo. Imagine looking at a large execution plan filled with joins and index scans, it is not an easy task to spot something like this without looking at the code inside the view. This is one of the reasons I hate deeply nested views, they are a product of object-oriented paradigm and should not belong in the database world. Or maybe it’s because of my inability to understand INCEPTION.
Now we know what’s causing the performance issue, what can we do about that? One quick answer would be to remove the CAST expression inside the view. I wish it was that simple, remember we are working with code that we don’t necessarily understand its intent. Modifying queries albeit a seemingly simple line is fraught with danger and should be your last sort.
So we cannot change the queries nor the view. Then what? Fortunately, I could modify the underlying table, provided that no business logic was modified. This is where the computed column comes in handy.
alter table trade_id_lookup
add trade_id_string as cast(trade_id as nvarchar)
create index idx_louie on trade_id_lookup (trade_id_string)
By copying the CAST logic from the view into a computed column, we achieved two things:
- An index can now be created on the converted trade_id.
- No code has to be modified and it all just magically works.
Executing the SELECT query again will yield the execution plan below, see how the index seek is now in use?
The key lookup is a by-product of the demo, you can optimize the query further by adding the referenced columns in the INCLUDE list of the index.
To understand a bit more about how the optimizer utilises indexes on computed columns, please refer to Paul White’s answer to the question Index on Persisted Computed column needs key lookup to get columns in the computed expression