Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unanswered: Nested Views: How Inefficient?

    I've been reading around that nested views can be quite inefficient because:

    a) Using views in general involves some overheads (getting info from system tables etc.)

    b) The optimiser doesn't do anything intelligent with them but just mixes all the joins from each of the nested views into one big, nasty join

    If the only way to get the results I need involves writing this "big, nasty" join anyway, does it matter that I'm not putting it directly into a single view, but breaking it into components so that I can also access parts of the join for other purposes?

    If the queries process lots of data, are the system overheads really that noticeable?

    THANX!,
    Angelos

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    look into udfs and see if some views can be converted into functions.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ms_sql_dba
    look into udfs and see if some views can be converted into functions.
    example, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Posts
    10
    Thanks for the tip. I think I see the point.

    This would be faster because each function would perform its join seperately, and only the results would be joined to eachother, right?

    Or is it rather a matter of system overheads being reduced?

    All the best,

    Angelos

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    you got it, and overhead will be reduced because of that.


    example? of what?

  6. #6
    Join Date
    Apr 2004
    Posts
    10
    Quote Originally Posted by ms_sql_dba
    you got it, and overhead will be reduced because of that.


    example? of what?
    thanks!

    I think the example was requested of *me*...
    I'm just experimenting now, so don't have a concrete example yet...

    Basically, I'm trying to get my principles correct before choosing a method to use. A main concern is code maintainability. Nested views could easily be avoided by repeating code. But building simpler views and then composing them into more complicated ones allows subsequent use of the simpler ones independently whenever they are sufficient.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the example was requested of ms_sql_dba

    an example of a view that can be converted to a udf
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Functions are precompiled, while views are not. Functions are generally more efficient because of that. In your case, however, some experimentation may be required, because it also means that the entire function must be processed before joining it to your other functions/views/tables. It's possible that if you were using views the server would combine all the view and table references to come up with the most efficient plan for that combination. You would suffer some overhead cost in devising the query plan, but you might avoid needless or redundant joins and calculations that are not required in your final dataset.
    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
  •