Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: Performane tuning and deadlock trapping in this scenario.

    SQL2K
    SP4


    Howdy all. I have done a fair amount of performance tuning and deadlock resolving in my day, but never under these circumstances. Here is the scenario:

    A sproc written in Dynamic SQL (with a dynamic WHERE clause I should add) queries a View. This View is joined to several more Views, that are joined to several more Views, that are joined to several more Views, that are.......

    So, what I've been having to do to find out if my column is indexed or not is read View1, then View2, and so on and so forth until I figure out what the table really is.

    This is extremely time consuming. I can actually have 10 - 20 joins by the time all is said and done.

    Now, I am aware that Dynamic SQL is bad, and I'm also aware that 10 - 20 joins is bad. But that doesn't solve anything for me. Can anyone provide any ideas on how to troublshoot perfomance issues and deadlocks in this scenario?

    TIA, ChrisR

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you looked at the query plan? Views are not precompiled, so the optimizer is supposed to combine all the views into a single efficient query plan. It does not necessarily run all the views referenced.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    128
    The query plan is a nightmare.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    My best tip would be to install it on SQL Server 2005 in a test environment, and use SQL Profiler there. SQL Profiler in SQL Server 2005 is pretty good at trapping deadlocks, and will show you what's conflicting.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd suggest you just rewrite it to eliminate the views. Views are nice as "reusable code', but the moment you start nesting them the drawbacks start outweighing the benefits.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    Views are nice as "reusable code', but the moment you start nesting them the drawbacks start outweighing the benefits.
    heh...heh...heh...heh...I've got a vendor that nested functions inside of functions inside of functions.

    I want to quote this and send it to them.

    May I?

    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Any problem in programming can be solved by another layer of indirection, except for the problem of too many layers of indirection.


  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by hmscott
    I've got a vendor that nested functions inside of functions inside of functions.

    I want to quote this and send it to them.

    May I?

    Regards,

    hmscott
    Of course. The world would be a better place if people quoted me more often.

    Quote Originally Posted by jezemine
    Any problem in programming can be solved by another layer of indirection, except for the problem of too many layers of indirection.

    DAMMIT JEZ! You just had to come up with a better quote than me, didn't you? Thanks for raining on my parade.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    couldn't resist. The great thing about that quote is that it applies in all situations, except when it doesn't apply.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your wisdom is strong, Jedi.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •