The effect of unnecessary tables in a query

The other day I was asked to look at a performance issue with a form opening slow. After going through the normal motions I ended up with a method being called hundreds of time. It did not take that long time to execute but all in all it was the reason for the form slowing down.

What the method did was to call a view and summarise a quantity field. Not something extra ordinary but I gave the view a look anyhow. And that is what triggered me to do this small post on a simple but often effective question: Do I really need that table? 🙂

Let me show you what I found with a simple example. We want to have listed all purchase order ids, item ids, the line status and quantity sorted by purchase order id, line status and item id.

This is a simplified example of the real life example and the demo view looks like this:

SELECT TOP (100) PERCENT dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.ITEMID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.QTYORDERED
FROM dbo.PURCHTABLE
INNER JOIN dbo.PURCHLINE
	ON dbo.PURCHTABLE.PURCHID = dbo.PURCHLINE.PURCHID
		AND dbo.PURCHTABLE.DATAAREAID = dbo.PURCHLINE.DATAAREAID
		AND dbo.PURCHTABLE.PARTITION = dbo.PURCHLINE.PARTITION
ORDER BY dbo.PURCHTABLE.PARTITION
	, dbo.PURCHTABLE.DATAAREAID
	, dbo.PURCHTABLE.PURCHID
	, dbo.PURCHLINE.PURCHSTATUS
	, dbo.PURCHLINE.ITEMID

It returns the data as expected, it executes in a few ms and everybody is happy. Well, until you execute it 500 times in a row and get tired of waiting. Looking at what data we actually need returned points us towards the PurchTable. The only thing we get from this table is the PurchId which is present at the PurchLine table as well and fully indexed there as well.

So if we change the view to look like this instead we get a significant effect:

SELECT TOP (100) PERCENT PURCHID
	, ITEMID
	, PURCHSTATUS
	, QTYORDERED
FROM dbo.PURCHLINE
ORDER BY PARTITION
	, DATAAREAID
	, PURCHID
	, PURCHSTATUS
	, ITEMID

The result set is the same but the performance has increased. Let us have a quick look at the statistics.

First what happens when executing the first view:

View1 - statistics

And then what happens with the updated view:

View2 - statistics

Well, several things indicates that we made a score. Packages received from the server has been reduced from 6 to 4 (33%) and bytes received reduced from 13921 to 5885 (58%). With this example only returning very few records (9) we will not see an effect in the execution times being dramatic, however we see an improvement.

All we did was to simplify and still got the exact same result at a much lower cost. This is not only relevant for views but any query made against the SQL server.

Advertisements

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