Optimizing view performance: Avoid Expensive Columns

Hello All!

In this installment of SATO Database Architects Blog, we'll show a little trick to improve performance on SQL views. Say what you will about views, but one thing is for sure...they are actually used. In a lot of places. And as you might have guessed, querying against view can be extraorginaliry slow.

The performance hit can be pretty bad, if the view contains function calls, since they are invoked for each row. Amazing how lots of people do not realize this, but if you think about it - if you have a function in a 100,000 row view, you can bet the function is called 100,000 times. Suppose you havea view like:

CREATE VIEW vwSales AS

SELECT ID, SalesDate, CustomerId, dbo.SomeComplexFunction(ID) FROM Sales

Suppose this view is joined in a complex query, such as:

SELECT s.ID, s.SalesDate, c.FirstName, c.LastName

FROM Customer c

INNER JOIN vwSales s on s.CustomerId = c.Id

You would hope that the SQL Server is smart enough to NOT call the SomeComplexFunction(). Alas, it often does and slow you down - doing the computation that gets discarded. One way to avoid that, of course, is to explicitly select the minimum column you need out of a view, and then join on that.

SELECT subq.ID, subq.SalesDate, subq.CustomerId

FROM Customer c

INNER JOIN

(SELECT ID.SalesDate, CustomerId FROM vwSales) subq

ON subq.CustomerId = c.CustomerId

This runs much faster due to explicit avoidance of SomeComplexFunction() from being called.

That's it for the day! Happy querying.