Home > SQL Server > Writing Code that scales – Part 1 – Functions in predicates

Writing Code that scales – Part 1 – Functions in predicates


Well Tech Ed 2010 came and went and I hope that most of you enjoyed it. As promised , I will try to share some of the tips from my Tech Ed sessions on this blog. The session on writing SQL code that scales seems to be popular, so we’ll start there.

The first tip shows the effect of functions in your predicates ( eg. the WHERE clause ), on indexed columns. Suppose you needed to list all customers from a customer dimension whose Surname started with the letter “L”. You might write a query that looks like the following :

Sample Query 1 - Functions in Predicates

Looks ok – but you are using a function. An alternative would be the following :

Sample Query 1 - Functions in Predicates

When you look at the execution plans for both of these , you start seeing the problem.

Sample Query 1 - Functions in Predicates

Firstly we see that the query with a function performs an index scan instead of an index seek. This is because a function on an indexed column does not make use of the index as effectively as if we didn’t have one. It cannot use the index to quickly locate rows because it has to rely on the output of the function. So it scans the index. Secondly, the bombshell, because of the above, the first query constitutes a whopping 91% of the cost of the batch. This means that its roughly 10 times as slower as the second query. The subtree cost confirms this as well – 0.065 vs .006

Another example is if we’re asked to select data from a fact table for a particular month and year. Lets say that the data surrogate key is actually an integer representation of the date.You might end up unfortunately with something resembling this :

Sample Query 3- Functions in Predicates

The problem with many queries is that they are written as a direct translation of the given request. That’s why inefficient queries are sometimes written. An alternative to the above would be :

Sample Query 3- Functions in Predicates

Its always good to think about the best way in which we can write a query before just accepting the first option that comes to mind. You can also experiment with the effects of functions in join clauses. Remember that these queries perform well in your development environment, but when they have to scale up in a production environment the real performance issues are unmasked.

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: