November 16, 2017 at 12:01 pm
I need to know what, if anything, can be done to avoid having SQL Server make use of an existing view (it can't go away), when executing the code for a new view. Every which way I order the joins to the code from yet another view, I look at the execution plan, and the view I want to avoid is right there, staring me in the face. Any way to force the view to use the base tables that are specified in the query?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 16, 2017 at 12:50 pm
Indexed view?
Why are you trying to avoid it? If an indexed view is showing up in a plan, the optimiser thinks it's more efficient than going to the base tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2017 at 1:36 pm
GilaMonster - Thursday, November 16, 2017 12:49 PMIndexed view?Why are you trying to avoid it? If an indexed view is showing up in a plan, the optimiser thinks it's more efficient than going to the base tables.
Problem was performance related, but as there were other things that went haywire the moment I found another way around this problem, I ended up playing Whack-A-Mole, and it was fruitless. Any gain I'd find in one particular set of parameters, and the other sets would have elongated execution. Just too much of a catch-all type of query to be effective at doing much with it. I had to let this one go and move on to other problems.
The specifics on this were that this indexed view was a major cost item in the execution plan because the existing view design doesn't take into account the usage this sproc makes of it, and as there's a LOT of other code dependent on it, it can't just go away without a lot more work than is practical at this point in time. I'll also bet I never get back to it... :hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply