What would YOU do?

Here’s the solution that I would go with. Would you choose something different? Why?

Transcript

Of all the solutions that I’ve presented, which is my favorite?

My favorite is probably the branching Dynamic SQL

I like the fact that I can have the big plan and a small plan and that I can make them reusable.

I’ve got consistent, fast performance, and it’s fast for names where there’s a lot of rows as well as names where there’s not a lot of rows.

I also like that I can monitor these in my execution plan cache. I don’t have anything like a recompile hint, making that hard to see in the plan cache, and I’m not just spewing CPU and burning up extra CPU by forcing compiles all the time, either.

I also like that if I improve my indexing, the query plans will just adapt to it. I haven’t forced any indexes, I haven’t forced scanning.

But of course, I was able to do this in this situation because I had an easy branching criterion, and I could be confident that those FirstNameIds were going to continue to exist. We don’t have a practice in the babbynames database of deleting names or reassigning FirstNameIds.

If I ever did have the possibility of reassigning FirstNameIds, then this particular OPTIMIZE FOR hint I wrapped in that solution would not necessarily be a great idea.

That FORCESCAN hint, I don’t hate it

It’s hard to say I wholeheartedly love it, because if I’m forcing a scan and then the perfect index does come along, I’m going to be forced to scan that too.

The forcescan hint can be useful if I do have that practice of every time I add or remove or change an index, I have the practice of before I do it, I check all of my hinted queries, and double check if I should change them, because I might be forcing a scan when later on there’s an amazing seek that could be possible.

Which solution do you like the best?

I think in this case, it is fun to try out different things and to find out which you think makes the best sense. Can you find one that’s even better than what I have found?

I would love to hear about it if you do, and know that in every specific situation, too, this is going to differ.

There’s going to be judgment calls based on how the database is used and how the application works. How it’s supported, how you release code, what your processes are like, how often your data grows, and do things happen like the data that you might be optimizing for could be removed or deleted or disappear, as well as: what are your performance requirements?

The cool thing about all this– I mean, yes, it’s complicated, but on the other hand, we are going to need people to do this for quite a long time. There is a certain amount of job security involved in the complexity of all of this.

Thank you so much for joining me for this session; I look forward to tuning more queries with you.