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
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
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
ORDER BY PARTITION
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:
And then what happens with the updated view:
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.