Post reply

materialized view right course for a datamining query with 7 joins?

  • January 31, 2014 at 11:07 am

    #282909

    I have a query that has 7 joins and aggregates data and runs daily. It takes about 2 minutes to run against the source data and currently there's no contention for resources with the main applications daily function. But, since I'm not going to be there to babysit this thing longterm, I want to optimize.

    Reading Itzik Bin Gan's stuff and he seems to advise creating a materialized view aka indexed view, for queries that have many joins and aggregate data. I have the resources at my disposal to do that, so I'm wondering if I should create a materialized view or just let the query run daily against source data.

    --Quote me

  • February 3, 2014 at 1:00 pm

    #1685618

    polkadot (1/31/2014)

    I have a query that has 7 joins and aggregates data and runs daily. It takes about 2 minutes to run against the source data and currently there's no contention for resources with the main applications daily function. But, since I'm not going to be there to babysit this thing longterm, I want to optimize.

    Reading Itzik Bin Gan's stuff and he seems to advise creating a materialized view aka indexed view, for queries that have many joins and aggregate data. I have the resources at my disposal to do that, so I'm wondering if I should create a materialized view or just let the query run daily against source data.

    First, you should check if you can implement a materialized view. Check the conditions that must be satisfied for it.

    Second, you should check if the data in tables grow fast. If it's the case than go with indexed view, otherwise you shouldn't.

    Have in mind that updates over the materialized view, because it's behaving like a table.

    Regards,

    Igor

    Igor Micev,My blog: www.igormicev.com

  • February 3, 2014 at 1:24 pm

    #1685624

    Post the actual execution plan as a .sqlplan attachment - there may be scope gor optimisation that you've missed.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply