Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Rich Rewards of Tuning SQL

By Tony Davis,

The past years have been a halcyon period in terms of ever-increasing processor power / clock speed. However, this has often been used to hide a multitude of coding sins. Got a major performance issue on your system? No problem, throw hardware at it, make it go away (at least for the time being).

However, anyone tempted into this "kill it with iron" approach to resolving performance issues should first take a look at the famous Tally table thread on the SQLServerCentral forums or, more recently, Phil Factor's Speed Phreak competition on asksqlservercentral. The latter presents a classic reporting challenge: for a subscription list with 10,000 subscribers, find the fastest way to provide a month-end report detailing the number of current subscribers, and the number of new subscribers and 'un-subscribers' in that month.

Some of the solutions, filled with quirky updates, clever aggregations, pivots, cubes and derived tables, are smart enough to make your eyes water, and will keep lesser mortals scratching their heads for a while. However, the effort it takes to try to understand them will bear rich rewards.

Tests performed on a million-row sample database revealed a tenfold increase in performance from a "so-so" SQL solution (4000 ms) to a "solid" SQL solution (400 ms). And as the host himself, Phil Factor, comments, even the ho-hum solutions are a lot better than much of the cursor-ridden production code that is so prevalent.

If this is the sort of reward to be reaped from tuning just one of the tens of typical reporting queries that make up your average application, then maybe the next time you are tempted to "kill it with iron", why not instead invest that time in hammering and refining your SQL queries? After all, how much money would you have to throw at hardware in order to see a comparable 10-fold performance benefit?

Cheers,

Tony.

Total article views: 275 | Views in the last 30 days: 1
 
Related Articles
SCRIPT

Listing Subscribed SSRS Reports including Parameters & their Values

This script will allow user to analysis their Subscribed Reports.

FORUM

jump to report solution need

jump to report solution need

FORUM

Poor Performing Query at Subscriber

Query running slow at Subscriber

BLOG

Creating Power Map Reporting Solutions Recording and Q&A

I hope you were able to attend my free webinar on Creating Power Map Reporting Solutions on April 1...

FORUM

BIDS Solution/Project shows no reports now

BIDS Solution/Project won't build now after I moved a few reports in SQL Studio

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones