Making SQL Work Too Hard
I was reading a forum and came across a question where someone wanted to take a query returning 30 columns and break them out into three lines of 10 columns each.
I really enjoy these kinds of challenges from the standpoint of how to write SQL to accomplish this task. I have been using SQL since 1985, and love solving problems that are somewhat esoteric. This question is possibly a form of Un-Pivot…which I enjoy as much as a pivot query.
Sitll, to this question my first response is, “WHY”. Simply because your database engine can do something doesn’t mean that is the best place to put your code.
Remember, when using a traditional SQL Engine, you have one database and one server supporting that database. When that server reaches capacity you have very few options to gain more.
Breaking 30 columns up into three rows of 10 columns sounds like a presentation problem. That modification is done just as easily in many different ways outside of the database. Any of those techniques result in code that can both scale up (bigger hardware) or scale out (more hardware working as a team).
Does this mean you shouldn’t put complicated computations in your database? That is a choice you will have to make. In my experience, I only tend to put those kinds of computations in a database if the work of extracting the necessary data is more intensive than making the computation and returning the results.
For example, if I need the sum of 10,000 rows, I would perform the sum in the database rather than returning 10,000 rows to be summed outside the database.
As an example of what I wouldn’t do, I wouldn’t use database server resources to concatenate names, addresses, etc. I would simply return the raw data, and let an external process handle the presentation.
For those of you using two tier software, such as Reporting Services, pulling directly from SQL Server, you still have options to do this work outside of the database. And, you won’t have to write the code over and over.
Using the Address example, you have a lot of work to build an USA address. The code to do the address calculation may be included in your report as an external dll, or even as a VB method. Now you code is in one place, it may be used for more than one report, and may be used by more than one data source instead of being included in a number of stored procedures or queries. The biggest benefit is that it can scale with the number of reporting servers.
I’m sure I struck a nerve for many of you. This is not a position I have held for my entire career. It is only through writing code in other layers that I have come to value the benefits of taking the load off the database server.
Your feedback is very welcome, and I’d be glad to post your response in upcoming newsletters. Feel free to send examples when you have found this principle to be of value, or situations you feel can’t be solved so easily. Forward your response to btaylor@sswug.org.
Cheers,
Ben
SelecTViews
With Stephen Wynkoop
Check out the news about the Dell Cloud announcement at VMworld 2011, the Nokia SQL-injection attack and more. We also have interviews with Monotouch developer Michael Bluestein and database architect Roman Rehak.
Watch the Show
$$SWYNK$$
Featured Article(s)
Introduction to PowerPivot – A Tool for Self-service Business Intelligence
The skills you will acquire with PowerPivot will be of immense value when implementing solutions based on the next generation of professional Business Intelligence tools in SQL Server 2011.
Featured Script
Find Field in tables
SP that will search for a field name in a database and return all places found…. (read more)