Sometimes you just have to break it up

Andrew Kelly — Tue, 14 Apr 2009 23:19:02 GMT

I will be the 1st to admit that I am no Itzik Ben-Gan when it comes to writing complex SQL statements and I wonder at times how people come up with some of the Selects that I see. Sometimes they are so complex or convoluted that I simply have a hard time figuring out the intent or the logic behind them. But as a performance consultant I pretty much find myself looking at these on a regular basis when they end up scanning very large tables or indexes. I find that many developers have a hard time splitting up the query into what I think are more manageable pieces, especially if there is a temp table involved. Lets face it temp tables are evil aren’t they :). Well that’s a whole another discussion which we won’t get into now but sometimes they can be be your friend. For instance today I took a rather unwieldy query that was being used in a stored procedure in which the optimizer simply couldn’t come up with an efficient query the way it was written. And one of the issues was that they needed a total count for paging purposes so they were running the same query twice, once for the count and once to get the TOP xx rows. In a nutshell I changed the query to use a UNION which allowed the optimizer to focus better on the SARG’s for each part of the query and placed the resultant rows into a temp table. This gave me the ability to get a count and retrieve the rows using TOP xx without running it twice. As you can see from the picture the costs were dramatic. The reads went from over 3 Million to just under 5 thousand and the duration went from over 23 seconds to 150 milliseconds. So bottom line is don’t be afraid to make the query more maintainable and break it up at times. You might be surprised what you can accomplish.

Double Standard?

Andrew Kelly — Thu, 27 Sep 2007 20:22:00 GMT

While this is nothing new, a conversation I had with a client the other day got me thinking more than I wanted to about what I see as sort of a double – standard for lack of a better term at the moment. Now I fully realize this can turn into a full out war of opinions, I feel the need to blog about it. But please keep in mind the focus of the argument and understand that I am in no way bashing or hyping one side vs. the other. What I am referring to is the mindset that it is OK or even encouraged for an application developer to develop both the app objects and the database objects. But it is not OK for a DBA to also develop application code. The point the client had was that the developer of an application needs to be somewhat of an expert in C#, VB etc. but they don’t need to know that much about databases to do a adequate job because SQL Server is simple. So the general idea seems to be that a DBA doesn’t require as much skill to architect a database as a C# developer needs to develop an application. So most people would never think of letting a DBA develop both the application and the database but it’s perfectly fine the other way around. Now I have been both a developer and a DBA so I have seen both sides of the fence and if you are talking about an application that doesn’t require a large or complicated database schema I don’t see too much problem with that. But how many small apps stay that way? Or what about ones that were always intended to be large and complicated? Why do so many people think that there is less of a need for a truly qualified person to design the database side? I see way too many apps that suffer from poor database design and implementation, especially the larger they get. If you wait until the database is hundreds of GB’s or even TB’s in size before you make the proper design changes to accommodate that it is often too late to do the right things. I don’t think that is too hard of a concept to understand or even agree with. Sure there are people who can be experts as both an application developer and a database architect as in any two skilled trades. But let’s face it that is the minority not the majority. I simply don’t expect most DBA’s to be experts in both SQL Server and C#. But I also don’t expect most developers to be experts in C# and SQL Server either. So why the double standard? Why do so many companies today feel it is perfectly OK to have the C# developer also do the database design and coding? What is it about proper database design and architecting that appears to be so simple that they feel developers can do just as good a job on the database with dramatically less training and experience than they typically have for the application side?

Andrew Kelly : DBA

Sometimes you just have to break it up

Double Standard?