Sql-server – Speeding Up Querying Views

materialized-viewsql servert-sqlview

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view–it has unions and subqueries. I can't add indexes to it.

So, I want to cheat.

By creating a procedure that copies the view to an actual table with indexes. The procedure runs quickly, less than a second. But the effect for our stored procedure is amazing. 50 executions used to take 12 seconds. Now it takes only 4. I'm thinking of implementing this method and then recreating the "compiled" table as needed, but never more than every 2 or 3 seconds.

My question is this. How bad is this practice? Are there better ways of handling such a complicated view?

Best Answer

240 msec isn't that bad for a complex view involving unions and subqueries. It's not great, but I've seen a lot worse. Beyond this, I'm afraid I have more questions than answers.

It's not clear from your question whether this is currently an indexed view. Is it?

It sounds like the first user to hit stale data will have to wait at least a full second for the table to be repopulated. Is that right? Does that matter?

  • Look at the execution plan for both the complicated view and for the join on the view that the stored proc does. Are both execution plans reasonable?
  • Are existing indexes being used?
  • Are existing indexes the best you can do? (There are a lot of options to the CREATE INDEX statement that most people don't know about.)
  • Do other indexes need to be built?
  • Can you use an indexed view?
  • Did you try the Database Engine Tuning Advisor?
  • Calculate the cost of the existing, slower process against the programmer time needed to "fix" it. How many times per day does this procedure get executed? How long will it take you to recover the cost of "fixing" this?
  • What's the cost of a user getting stale data using the new "create a table from this view" process?
  • What's the likelihood of a user getting stale data using the new process?
  • What's the cost of repopulating your table for 1 second out of every 5 seconds?
  • How does this new table (and its log files) affect disaster recovery?
  • Are the underlying tables reasonable? (Statistically, most performance problems are structural problems.) Look hard at the use of ID numbers, and at how normalization has been carried out.
Related Question