How to improve performance of the view with 12 joins

  • CirquedeSQLeil (11/9/2009)


    rshm35 (11/9/2009)


    The query has 12 joins in that 2 views are there...

    i can not use indexed view..help me out..

    We need more information to help. Some great comments have been made in this thread for your issue. A join on 12 tables can be done (even with 2 of them being views). In order to better help, we need to know execution plans, queries, sample table structure, sample data.

    I will add that refactoring something as potentially complex as this is not really the ideal scenario for a forum post. You are really pushing the boundaries whereby a paid engagement is appropriate. I have spent DAYS tuning stuff like that before.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Paul White (11/9/2009)


    Grant,

    86? :w00t:

    Paul

    Not my design, man, not my design.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • rshm35 (11/9/2009)


    The query has 12 joins in that 2 views are there...

    i can not use indexed view..help me out..

    You've got to answer the questions that we've been posting back. We really can't help you based on the information you've provided.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (11/9/2009)


    Paul White (11/9/2009)


    Grant,

    86? :w00t:

    Paul

    Not my design, man, not my design.

    Yeah - this was your IMPROVED design, right?? LOL

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (11/9/2009)


    Yeah - this was your IMPROVED design, right?? LOL

    LMAO!!! :laugh:

  • Paul White (11/10/2009)


    TheSQLGuru (11/9/2009)


    Yeah - this was your IMPROVED design, right?? LOL

    LMAO!!! :laugh:

    You guys kidding? This app has had more examples of bad design than any I've seen. It was built originally with nothing by multi-statement table valued UDF's, then a consultant redesigned it with 86 table joins, then it was redesigned again by another consultant who put a query hint on one query (FAST 1) which was copied to every other query by the developers (along with several other hints including READ UNCOMITTED)... I love this app. It's a constant source of material for what NOT to do. Unfortunately it's been working and scaling well for about two years now (ever since we finally took it out of the hands of the developers & consultants). I've got a new monster lurking in the wings though, a DB built entirely with, by and for nHibernate. Should be another great source for material.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I have said this before and will say it again here: SQL Server continually amazes me with how well it keeps on chugging despite INCREDIBLY bad design, code, setup, configuration, hardware, (cough) tuning, etc,etc!!!!

    Oh, and ORM = $$$ from where I sit! I LOVE those things. hehehe Yet another example of what helps developers slap code together quickly is BAD for the database (and it doesn't matter WHICH RDBMS either).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • ORM - Obscene Relation Mangling?

  • Grant Fritchey (11/10/2009)


    I've got a new monster lurking in the wings though, a DB built entirely with, by and for nHibernate. Should be another great source for material.

    The mind boggles...let's hope it catches on so we can all retire after a couple of gravy-train consulting years fixing it all up 😀

  • Back to OP...

    I believe he has a table with 12 columns denoting the months of a year.

    If this is the case, you can use UNPIVOT to normalise your data on-the-fly.


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso,

    You muse be psychic to get that from the first post and this:

    rshm35 (11/9/2009)


    The query has 12 joins in that 2 views are there...

    i can not use indexed view..help me out..

    ...that's all the information that we've had so far, unless I've missed something...?

    Paul

  • It's still a guess, but my experience (been a teacher for many years) says it's something to do with months, due to the number 12.

    OP has not that many questions before and 12 "normalized" joins is nothing you get your hands on with that little experience.

    However writing a query with 12 subselects, correlated subqueries or even JOINs is something an unexperienced person would do.

    You know... 🙂

    SELECT (SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 1) AS January,

    (SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 2) AS February,

    (SELECT SUM(Amount) FROM Table1 WHERE MONTH(Col1) = 3) AS March

    ...

    Or similar approach with a 12 correlated subqueries or 12 joins to do the same thing.


    N 56°04'39.16"
    E 12°55'05.25"

  • It's certainly plausible...;-)

    And it does help to be psychic with a lot of these questions.

  • Good guess Peso , though i think that it may be the 12 days of Christmas 😀

    You know....

    Create View 12Days

    as

    Select Qty from Bird where Species = 'Partridge' and InAnPearTree = 'Y'

    union all

    Select Qty from Bird where Species ='Dove' and SubSpecies = 'Turtle'

    union all

    Select Qty from Bird where Species = 'Hen' and SubSpecies = 'Speckled'

    union all

    Select Qty from Bird where Action = 'Calling'

    union all

    Select Qty from Jewellery where Type = 'Ring' and Metal='Gold'

    etc......

    How many times have we seen that 😀



    Clear Sky SQL
    My Blog[/url]

  • CREATE VIEW dbo.CrystalBalls

    WITH SCHEMABINDING

    AS SELECT Egg FROM

    (

    VALUES

    ('Egg'), ('Egg'), ('Egg'), ('Egg'),

    ('Egg'), ('Egg'), ('Egg'), ('Egg'),

    ('Egg'), ('Egg'), ('Egg'), ('Egg')

    ) Eggs (Egg);

    ...count them...there's a dozen fresh free-range ones 😛

Viewing 15 posts - 16 through 30 (of 36 total)

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