Indexed views

  • 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

    Attachments:
    You must be logged in to view attached files.
  • 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

  • Never thought of that (we've done just about everything else!)

     

    I'll try it and see how it goes.

  • Not only OUTER APLLY is the problem, you can't use MAX function in the indexed view. Check "Additional Requirements"

    https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

     

  • 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

  • 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/

  • 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?

  • @mike01 - not used materialized views before.  I'll do some digging and see what turns up.

     

    @david.Edwards76768 - Unfortunately not, they are used in other sprocs as well as reports.

  • 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?

  • Yeah, it would take weeks.

  • Apologies for the delay in responding.

     

    We can't use materialised views for the same reason we can't use indexed views.

    think it's down to the fact we need either:-

    • OUTER APPLY
    • PIVOT
    • LEFT JOIN

    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