July 15, 2020 at 8:38 am
Hi all
We've got some views that we'd like to index but we can't as we are currently using OUTER APPLY in them.
I've attached a sample view (I've had to rename the extension to .txt).
None of us can figure out how to get away from the outer apply so any help would be appreciated.
Has anyone got any ideas?
TIA
Richard
July 15, 2020 at 9:11 am
Is it not as simple as changing the OUTER APPLY into a LEFT(?) JOIN and moving the WHERE predicates from the ANSWERS subquery to the join predicate?
John
July 15, 2020 at 10:09 am
Never thought of that (we've done just about everything else!)
I'll try it and see how it goes.
July 15, 2020 at 11:48 am
Not only OUTER APLLY is the problem, you can't use MAX function in the indexed view. Check "Additional Requirements"
July 15, 2020 at 12:15 pm
I've seen that bit as well thanks (and managed to get rid this morning).
I'm just doing a test replacing the OUTER APPLY with a LEFT JOIN and moving things around.
Just putting everything together and seeing if it works.
::edit::
Can't use sub-queries or CTE's either. Back to the drawing board
July 17, 2020 at 3:12 pm
How often does the data get modified? Wondering if you could materialize the view and then update when the data is updated
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2020 at 3:57 pm
Just an idea (caveat, not had the time it would need to totally get my head around all that code....) would the end use allow you refactor as a stored procedure, then optimise the indexes on the underlying tables for the sProc, rather than use an indexed view?
Might allow you to use temp tables, ctes, other techniques to reduce complexity, aggregate etc and improve performance while still executing simple sql from the application/report or wherever is called from.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 20, 2020 at 1:05 pm
Shame. I guess converting a lot of those would be a huge development task 🙁
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 20, 2020 at 1:23 pm
Yeah, it would take weeks.
August 13, 2020 at 3:19 pm
Apologies for the delay in responding.
We can't use materialised views for the same reason we can't use indexed views.
I think it's down to the fact we need either:-
I can't see any way around this so it looks like we're stuck unless someone has any other ideas.
Thanks
Richard
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply