Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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?

share|improve this question
1  
Note that a view is just a macro: dba.stackexchange.com/a/14903/630. I'd question why you need the view, rather than work around it... – gbn Mar 26 '12 at 7:08
The view is a critical piece of logic. The conditions under which rows would be added or removed are extremely varied. – Andy Edinborough Mar 27 '12 at 1:10

1 Answer

up vote 4 down vote

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.
share|improve this answer
-Well, I believe they're as reasonable as they can be. :] -Yes, all the indexes possible are being used, which is why I'm trying to index a view. -This procedure is the main function of our site. Sometimes hit a couple times for a single request (although much work has been done to reduce that). -A few seconds of stale data certainly isn't the end of the world. Actually, my idea was to run the update in a separate process while doing something like read uncommitted so the user would never be blocked (or only minimally so). – Andy Edinborough Mar 27 '12 at 1:07
Did you consider breaking up the view? Suppose you could break it up into 3 views, all with sub-millisecond response time. Would your server be able to handle 3 times the requests if they were that fast? – Mike Sherrill 'Catcall' Mar 27 '12 at 1:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.