THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Performance-Driven Development

I was reading a blog yesterday about the evils of SELECT *. The author pointed out that it's almost always a bad idea to use SELECT * for a query, but in the case of SQL Azure (or any cloud database, for that matter) it's especially bad, since you're paying for each transmission that comes down the line. A very good point indeed.

This got me to thinking - shouldn't we treat ALL programming that way? In other words, wouldn't it make sense to pretend that we are paying for every chunk of data - a little less for a bit, a lot more for a BLOB or VARCHAR(MAX), that sort of thing? In effect, we really are paying for that. Which led me to the thought of Performance-Driven Development, or the act of programming with the goal of having the fastest code from the very outset. This isn't an original title, since a quick Bing-search shows me a couple of offerings from Forrester and a professional in Israel who already used that title, but the general idea I'm thinking of is assigning a "cost" to each code round-trip, be it network, storage, trip time and other variables, and then rewarding the developers that come up with the fastest code.

I wonder what kind of throughput and round-trip times you could get if your developers were paid on a scale of how fast the application performed...

Published Tuesday, May 11, 2010 7:36 AM by BuckWoody



Zack Jones said:

Timely topic. I was just about to start some Google searching for ways to measure the amount of time it takes to execute a stored procedure. I'm in the process of converting some stored procedures that use temp tables to use common table expressions and I'd love to know of a way to see the benefits, or lack there of, by changing from a temp table to a common table expression.

May 11, 2010 12:47 PM

jchang said:

if developers were paid based on the performance of their code, we would not have fast applications, we would have cold, hungry developers. I hope the evil SELECT * article had an exception for IF [NOT] EXISTS (SELECT *). To get developers to write good code, we have to start with understanding the execution plan. To have a meaningingful execution plan, it must be for a populated database, even if its fake data. We do have the UPDATE STATISTICS capability for setting pages and rows, but not histogram. Once, I started to build a data distribution statistics histogram editor for SQL 2000, but in 2005/8, there is a checksum-type field, so I cannot generate a valid histogram.

It would be nice if MS let me know how the checksum is generated. I could generate fake data distribution stats, developers would have realistic execution plans to look at, and we would still have bad performance from poorly built apps

May 11, 2010 8:52 PM

AdaTheDev said:

I'm a big fan of developing for performance from the word go. Unfortunately, it's easy for a query to be knocked up with no regard for how it might perform in a production environment, with production data volumes or how it would scale as data volumes increase. A run through on a dev environment that typically doesn't have production-level data volumes generally doesn't flag up a noticeable issue unless you pro-actively think about performance - i.e. may take ms to run a query on dev, but a simple check of the execution plan could sound alarm bells for how it might perform in production.

So, I'm in the camp where I'd much rather be doing "Performance-Driven Development" to minimise as much as I can, the possibility of poor performance coming back to bite me later.

May 12, 2010 4:21 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement