View Tuning

Randolph West had to tune a query taking 10-100x too long:

A stored procedure with a single @ProductID parameter would allow us to add WHERE ProductID = @ProductID to both derived tables, which would make the query really fast. In my testing, it ran in under 100ms.

The problem is that this would require numerous code changes to the existing system. Many of the queries that rely on the view also use additional JOINs and WHERE conditions, meaning that a stored procedure is impractical.

Enter the table-valued user-defined function, or TVF.

I’m glad that the TVF worked out for him, but personally, I’m not a big fan of functions.  In this case, though, it does the trick and leaves everyone happy.