Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Views scripted in the wrong order

    Is there a way to make EM script all views of a database in the order in which they depend on each other?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I think you are going to have to shun the EM and query sysdepends,sysobject and syscomments. Anywho, nested views are a recipe for disaster. You lose the benefits of indices and introduce a level obscurity into your design and you can get into a nasty tangle when it comes to table schema changes. have fun loading your gun.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sean,

    it appears the horse has already left the barn


    I think I would script each view to it's own file. Then I would automate a process that use sp_depends to figure out who's dependant on what. I would then generate an osql file that calls the cript in the order you need.

    It's doable....but as Sean mentions...

    It's like nested queries in access. Is this where this came from?

    You'd be better off with a rewrite....

    Got any Queries that do a GROUP BY with no Scalr functions?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    > You lose the benefits of indices

    Is it because the optimizer can't find the optimal solution when there are nested views on the top of the base table?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    although you can index views w/ enterprise edition, there are a bunch of restrictions.

    Otherwise you are basically querying the result set of a query and creatng a psuedo table which does not have and index. So when you join viewA.col1 on view2.col1 although both may have had an index on them at the table level, the analyzer will not use or recognize them.

    Dig out asap.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure your statement is correct, Thrasy.
    Under some circumstances SQL Server will incorporate the logic of the underlying view into the outer statement and optimize for the best overall plan, including indexes.
    ...or so I seem to recall reading...
    If I get a chance, I will try to look it up. But I bet if you ran a query plan on the outer statement you would see the guts of the inner views rather than see them treated as independent datasets, and it would be easy to see if indexes are being used.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    go ahead and fact check me, but I am either right or I have had this delusion for a long time. I would do some experimenting but I am a little busy at the moment
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This reminded me of a thread I read at SQLTeam (should you reference a view from a sproc or write all the SQL in your sproc - basically can the sproc take advantage of the indexes of the tables upon which a view is based? The answer was yes as I recall).

    I must admit my (limited of course) understanding was as Blindman.

    Code:
     CREATE VIEW View1 AS
    SELECT * FROM TableA
    GO
    CREATE VIEW View2 AS
    SELECT * FROM TableB
    GO
     
    SELECT * 
    FROM TableA INNER JOIN TableB ON
    TableA.FieldA = TableB.FieldA AND
    TableA.FieldB = TableB.FieldB
     
    SELECT * 
    FROM view1 INNER JOIN view2 ON
    view1.FieldA = view2.FieldA AND
    view1.FieldB = view2.FieldB
    Very simple views of course - I don't know if that has an implication. However the execution plans were identical, each using the indexes of the underlying tables.

    As it happens - I nest views too. Predominantly because I understood that there was no performance penalty for doing so. Although there are obvious disadvantages my main reasons for doing this are a) I only need to optimise once and (crucially for me) b) business logic is not repeated across n objects.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2002
    Posts
    229
    > Got any Queries that do a GROUP BY with no Scalr functions?

    Yes

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    perhaps I was wrong. happens. still not a good thing to nest views.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    still not a good thing to nest views.
    Hi Sean
    Out of curiosity - why is this? You and Brett certainly seem to be in agreement.
    Is it me approaching things from a developers "write generic code once and reuse it" mind set Vs a DBAs "Every process should be as efficient as it can possibly be" approach?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Coolberg
    Is there a way to make EM script all views of a database in the order in which they depend on each other?
    Hi
    Conincidently I've just stumbled on this whilst looking for something else. I haven't looked at it in detail but I would imagine you could customise for views reasonably easily. The author throws in several caveats though...
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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