Query Tuning Using A CTE

I’ve been doing a lot of research around Common Table Expressions, or CTEs, for a presentation that I will be giving to my developers. Joe Sack points out in one of his PluralSight courses that CTEs do not inherently increase performance but are instead often chosen to increase the readability of a particular query or to meet recursion requirements. I agree with him on this. That’s not to say that a CTE won’t increase performance, and recently I ran into an instance where I used CTE to increase performance as opposed to readability.

A few days ago a Business Analyst was in my office picking my brain on a completely unrelated topic when he casually mentioned a query that was going to take about fifteen minutes to run. That’s right, fifteen minutes! This of course piqued my interest and I began asking questions about his query. He explained that most of the query ran well but one subquery was taking a majority of the execution time. He was trying to get to some data that was in an XML format but not stored in an XML column. Therefore, he had to convert the data to XML then shred it. Below is a sample query; the names have been changed to protect the innocent.

If we take a closer look at the last select statement in the subquery, we see that the business analyst was using the vaule() xQuery method to parse the XML in the where clause. That’s not so bad, except for this one using an IN clause here:

Transact-SQL

1

2

3

4

t.XMLdata.value('(/root/tag/p[@n="Code"]/v)[1]','nvarchar(max)')

in('290','294','297','500','505','SEIZ','JEOP')

The BA was hoping to avoid repeated xQueries by using the IN clause instead of a bunch of OR statements, but what SQL Server does behind the scenes is just that. We can confirm this by looking at a snippet of the execution plan.

As you can see, we have nine xQuery statements—two for the two Boolean values and seven for all the values in the IN clause. So that is what I would need to work on first. I decided to pull the xQuery out of the where clause and use a CTE to first shred the XML and then in the SELECT portion of the CTE use the same logic to compare the resulting values.

I then inserted that data into a temporary table too and plugged in a simple select into the subquery of the original query. Execution of the new query now took less than four minutes, and after adding a few indexes, I managed to cut another minute off the execution time. So now, a query that once took about fifteen minutes to run was now completing in about two-and-a-half minutes. This is why I love my job; I took knowledge I had just acquired and implemented it in a way that actually improved a query.