Replacing ORs by using UNION ALL

A common problem I often find in SPs is the use of an OR in the WHERE statements. It's very common for the business ask to be able to get something given certain conditions, that can affect several fields on a table, like getting a person by his name OR by his last name (or both). Although it seems that OR is easiest way to solve this (and yes, it is, from a human POV), the main problem using them in the predicates is that is not escalable.

Depending on the distribution of data in rows, or even the amount of rows in a table, using ORs  can drastically affect performance, since the DB can switch from choosing to do an Index Seek, or do an Index Scan. This is not always bad, since depending on data on rows, sometimes it's better, but in this case, we can avoid them when using ORs by replacing them by UNION ALL. 

For instance, let's check this scenario:

We have the table Subscriptions has more than 5m rows, and has 3 indexes (among others): 

PK {SubscriptionID}
IX {PolicyNumber}
IX {AssociationNumber}
IX {IsInternal, PolicyNumber}

And this query:

select *
from 
 Subscriptions
where IsInternal = '1'                 
and (PolicyNumber like @sSubscriptionNu or 
     AssociationNumber like @sSubscriptionNu) 

You see the OR in the predicate? If we execute this query, and check the execution plan, the engine cannot use the IXs, but uses directly the PK, causing an index scan:

Ok, lets work on the query then, removing the OR by using an UNION ALL:

select *
from 
     (select SubscriptionID, IsInternal from Subscriptions 
      where PolicyNumber like @sSubscriptionNu 
      union all
      select SubscriptionID, IsInternal from Subscriptions 
      where AssociationNumber like @sSubscriptionNu
      ) S
where S.IsInternal = '1'

And let's check the execution plan now:

At first sight (even when it looks more complex) we can see an improvement in the execution plans. The first one costs 75% of the batch, and the second one 25%. Now, let's compare the results of both versions (old and new) using the SQL Profiler:

Even here, we can see the logical reads have drop from 25599 to 379! Great improvement, it is.

Of course, there are a lot of other scenarios where ORs is used and can be replaced by an UNION ALL, but the idea es the same. It is just a bit more complicated to refactor the SQL code.

As we saw, replacing the OR by an UNION ALL is better, but not always. If we execute the same query over an empty table (an extreme case, I know) the OR is better. But since a normal operation in a production environment would be with the table filled with a lot of rows, using an UNION ALL is prefered. So we can say that the UNION ALL is more scalable than the OR, since any change in the statistics, table content, or even a simple change in the query can turn a efficient query in a sluggish one.

Finding Serializable transactions

Situation: The main app in my company was creating Serializable transactions, and thus creating Range Locks in production DB.

So, we had to modify the app to change the way transactions were being generated, since we found that the framework was using TransactionScope and, as you may know (or not), the default value for new connections is Serializable. So, the app was modified, and after that, my boss came over to my desk and told me "We need to know if there are some Serializable transactions in XXX environment. Can you check that?" 

Challenge accepted!

Since I had no direct access to the server, and so I cannot left a SQL Profiler running, I had to think in a workaround. So, for that, my idea was to use SQL Server Traces, specifically using the ‘Audit Login’ event (I have to thank Guillaume Columeau for that, an SQL Server expert in Paris). In the TextData column of the trace, you can check the connection isolation level. Also, all connection properties are displayed in that column.

First of all, I had to try the idea in my local environment. So, after creating the trace (I’m not going to get into detailed info about the tools you can use to create them, since there a lot of places in Internet for that. Just Google it ;-) I run the app and did some random flows, which used the DB, with the previous version of the app, a version which I know for sure it was creating serializable transaction. Anyway, this is what I found:

select
      TextData,
      HostName,
      ApplicationName
from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.INTEGRATION\MSSQL\LOG\INTEGRATION$DBM_Logins.trc', default)

(I truncated the TextData field, since it's a little bit long) 

So, indeed, as I was expecting, the app were generating serializable trxs. So I had to identify the SP that was being executed inside it. In the traces, we also can retrieve the execution start time (in the StartTime column)

The SQL instance has also a trace running for the queries, so all I had to do as to check which SPs were executed in that timeframe:

select
      ObjectName
from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.INTEGRATION\MSSQL\LOG\INTEGRATION$DBM_Queries_And_Locks.trc', default)
where StartTime >= '2014-02-17T16:20:21.390'
and EndTime <= '2014-02-17T16:30:44.053'

Searching for that SP inside the app, I could confirm that it was using TransactionScope, with default values, so SERIALIZABLE was being used. Remember that this was a previous version, before the change I mentioned at the beggining of this blog entry, so it was the expected result. So, I was done for the POC, and ready to move to the real environment.

End of story: I replicated all of this in the environment (with help from the DBA, the same guy I mentioned before) my boss asked me to check, and, happily, there was no serializable transaction being generated. so the app was correctly modified.

I’m attaching the code for creating (and stopping) the trace. LoginTrace.zip

Different table alias => Different execution plan

So, finally I decided to start writing a little bit of my SQL experiences along my career as a DB involved person.

For my first entry, I decided to write about a problem I had a year ago, about a query that performed very poorly in some conditions, mostly because of SQL parameter sniffing. I had the following scenario:

And the problematic query was the next (inside a SP, of course):

       
declare @SubscriptionName   nvarchar(60)

select @SubscriptionName = SubscriptionName
from Subscription where SubscriptionId = 246939

select
          c.ClientId
         ,c.ClientName                 
         ,su.SubscriptionNumber   
from Client c
inner join Subscription su
       on c.SubscriptionId = su.SubscriptionId
where su.SubscriptionName = @SubscriptionName

The parameter sniffing occurred because the distribution of the SubscriptionName field in Subscription table. We have a lot of duplicated names for different SubscriptionIDs. So, for instance, we have:

       
select count(*) as [ = 1 ] from (
select SubscriptionName, count(*) as SubscriptionCount
from Subscription su
group by SubscriptionName
having count(*) = 1 ) as Subscriptions

= 1

26275

       
select count(*) as [ > 1] from (
select SubscriptionName, count(*) as SubscriptionCount
from Subscription su
group by SubscriptionName
having count(*) > 1 ) as Subscriptions

> 1

11093

So, a lot of non-repeated, against a lot of repeated. But, a deeper analysis showed this:

select count(*) as [ >= 1000] from (
select SubscriptionName, count(*) as SubscriptionCount
from Subscription su
group by SubscriptionName
having count(*) >= 1000 ) as Subscriptions

>= 1000

17

select count(*) as [ < 1000]  from (
select SubscriptionName, count(*) as SubscriptionCount
from Subscription su
group by SubscriptionName
having count(*) < 1000 ) as Subscriptions

< 1000

37351

So now we have a LOT of low repeated names, and very few (in comparison) highly repeated names. And when one of these rows were used to create the execution plan (or vice versa), the resulting plan was not adequate for the other kind of distribution.

I couldn't change the table’s structure (nor create any index), and I was working on SQL2005 at that time, so a lot of possible optimizations could not be applied. No RECOMPILE option was available either, since the SP was HEAVILY used, and the DBA was explicitly against it. So I had to think in a different way of dealing whit this. So he proposed using different aliases to the Subscription table according to the distribution of the Name used for the search.

So, if the Name count was above 1000, one alias should be used, and if not, the default alias had to be used:

declare
  @SubscriptionName   nvarchar(60),
  @sql                nvarchar(max),
  @alias              nvarchar(6)

select @alias = N'su'

select @SubscriptionName = SubscriptionName 
from Subscription 
where SubscriptionId = @SubscriptionId

if (select count(SubscriptionName) 
   from Subscription 
   where SubscriptionName = @SubscriptionName) > 1000
  select @alias = N'sus'

set @sql =
  N'select top 100
    c.ClientId
  , c.ClientName                 
  , ' + @alias + '.SubscriptionNumber   
  from Client c
  inner join Subscription ' + @alias + '
  on c.SubscriptionId = ' + @alias + '.SubscriptionId
  where ' + @alias + '.SubscriptionName = @SubscriptionName'

exec sp_executesql @sql, 
    N'@SubscriptionName nvarchar(60)', 
      @SubscriptionName

This way, I was forcing the optimizer to use one or another execution plan. If the SubscriptionName distribution was under 1000, this was the generated execution plan:

But, if the SubscriptionName distribution was above 1000, this happened:

If I used the same alias for both cases, the execution plan was the same, no matter what distribution the column had. So, this way was able to force the optimizer to use different ways to deal with the name distribution issue we had (and have) on that table. I think it’s a nice way to handle some specific parameter sniffing problems, when you’re not able to use the “normal” ways of dealing with it.

Ok, that’s it, thanks for reading! This is my first time writing a IT blog, so any comments and suggestions are highly welcomed. Stay tuned, I will be talking about running background traces in SQL server to help debugging for the next installment of this blog.