Rip's Domain

Query taking too long? Could be a null.

Posted in ColdFusion, MSSQL, SQL by rip747 on February 5, 2009

I was smacking my head against the wall trying to figure out why in the world a scheduled task I wrote was timing out on my production server, but not on my development server.

I started by looking at the database first since I know that that is the place that 99% of performance tuning could be done. I fired up Query Analyzer and popped in the SQL statement and began combing through the tables and views that made up the query.

To make a long story short (too late), I noticed that in one view I was returning a count aggregate and in my development database every row returned a value, but on my production database there was a row that was returning a null. I patched the problem with a quick isnull() and reran the query again. IT WORKED!!!!

The lessons I learned, double check your work, always account for nulls on joins and aggregates and never write business logic views at 4 in the morning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: