h1

Don’t assume it’s performant

January 18, 2008

When working as a contractor you inevitably inherit previous applications and databases with the remit to maintaining and upgrading them with a list of changes.

Sometimes you find something that just doesn’t look right and this week a colleague and I tackled just a problem.
When running SQL Studio Profiler on a server for another reason we noticed that a certain stored procedure that was effectively returning the information for one row was taking anywhere from 1.3 to 5 seconds to run – and this was a vital “data entity” that was constantly requested on the site.

Looking into the stored procedure we saw the SQL involved was something like:

SELECT t.A, t.B, t.C, t.D,……..t.Q,
, (SELECT COUNT(*) FROM t2 with (nolock) WHERE Field = t.ID) AS ACount
, (SELECT COUNT(*) FROM t3 with (nolock) WHERE Field = t.ID) AS OtherCount
FROM t
WHERE t.ID =@ID

The extra bit of information being queried from the other tables looked like it could be causing issues and by requesting an estimated execution plan we saw that there was a bottleneck of processing occuring on a clustered index scan of one of those tables:

ExecutionPlan

Checking that secondary table for indexes we found that there was only one on the primary key – there wasn’t an index for the “Field” that was being queried in the above SQL.

We introduced a non-unique, non-clustered index on that field and did the appropriate testing.

Once the changes were committed to the live enviroment we saw a dramatic change in performance:

PerformanceChange

The change occurs halfway down the image – the CPU usage and timings have virtually disappeared and our query is now in the milliseconds.
Needlesss to say the site’s speed is now sizzling!

The moral of this post?

Don’t assume that what you inherit is performant in any way. In this example the lack of a simple index on that secondary table was introducing a slow down on the site on a large scale and as we know best practices are not always followed!

My new mantra: Profiler is my new friend on new projects.

Advertisements

One comment

  1. […] modern day Trojan horse got me thinking about software projects and their applications – when you inherit an application there are many assumptions that you make and one of them is about the quality of the code involved. […]



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: