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 :
Looks ok – but you are using a function. An alternative would be the following :
When you look at the execution plans for both of these , you start seeing the problem.
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 :
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 :
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.
Tech Ed 2010 has been great so far. I’ve loaded the slides from my 2 sessions onto skydrive – you can find them here –Skydrive
Well this was a nice surprise. I stumbled across this article today about a computer at Carnegie Mellon University that can learn language and facts. And, its makers are teaching it to automatically scan the internet and learn on its own.
Called NELL, short for Never Ending Language Learner, you can find out more here. At the moment it seems to have limited cognitive abilities, making simple errors that a small child would make. Perhaps its makers will address that in steps , and then maybe open up an interface so we could quiz the little guy. the one difference would be that eventually something like this would have far more of a repository of knowledge than other “bots”.
There’s also a paper on NELL here.
Update : These links are better.
Well we certainly live in interesting times. Apart from starting a new job ( and there’s more to come on that front ), it seems like the old blog has been giving a coat of new paint. If you’re coming here from a search engine , I would like to apologise. You see, Microsoft has cut a deal with WordPress to move our blogs to this here wonderful site. The only problem is that some of the links from search engines will now be broken.
I’ll try to sort that one out, but it will be slow. In the mean time if you search this site , I’m sure you’ll find what you’re looking for ( unless you’re Bono ). In the upcoming weeks ….. more SQL goodness and I’ll be putting up my Tech Ed presentations for those of you who couldn’t get to Durban.