On Indexes and Views

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Comments posted to this topic are about the item On Indexes and Views

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one.

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Excellent article! Indexed views are a useful tool to solve performance problems when properly implemented.

  • M Roush

    SSC Enthusiast

    Points: 136

    This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? 🙂

    Thanks,

    Mike

  • Jeff Moden

    SSC Guru

    Points: 996676

    M Roush (9/9/2008)


    This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? 🙂

    Thanks,

    Mike

    Depending on what you filter a view-of-a-view on in a query, views of views can be horrible for performance because you will usually cause the inner view to materialize in full if you filter or join on a calculated column.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996676

    Timothy! Great article on NO EXPAND. Nicely done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Tom Garth

    SSCertifiable

    Points: 6173

    Timothy,

    That's good information, and a great example.

    Thanks!

    One question anyone?

    I thought that Developer Edition was equivalent to Enterprise Edition, but mine acts like Standard Edition when I don't use NO EXPAND.

    Anybody know why?

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Adrian Hains

    Ten Centuries

    Points: 1322

    I have been using the same approach for my sql2k5 standard boxes with great success. I have found that I routinely want to know if a view is (1) an indexed view, (2) a wrapper view for an indexed view, or (3) a traditional view. To that end, I use a nomenclature of *_BaseIV and *_IV for the first two cases-

    create table t1 (c1 int primary key);

    go

    /*indexed view*/

    create view myview1_BaseIV

    with schemabinding as

    select c1 from dbo.t1;

    go

    create unique clustered index myview1baseiv_ucidx_c1 on dbo.myview1_baseiv(c1);

    go

    /*wrapper for indexed view*/

    create view myview1_IV

    as select c1 from dbo.myview1_BaseIV with (noexpand);

    Naming them this way makes it so I can easily discern what type of view each is when scanning through the list of views in SSMS. I'll often times be looking for an indexed view that is an aggregate rollup of some table, so this makes it quite easy to locate.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Adrian, that is a great idea on the nomenclature. Thanks for pointing it out.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Andy Lennon

    SSCrazy

    Points: 2354

    a well-written article, and with appropriate citations, no less!

    Thanks Timothy. I'll be looking for more articles from you...

    Cheers!

    A lowly developer

  • Jerry Hung

    SSChampion

    Points: 12908

    Oops, didn't know I have to use NOEXPAND to use Indexed Views

    all this time I thought when I selected on my view, it used the Clustered Index

    Good thing it wasn't slowing anything down

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • lage_bergstrom

    Valued Member

    Points: 53

    Thank you for an interesting article!

    The company I work for is still using SQL Server 2000. I tried the example and it seem like the NOEXPAND just gave an extra overhead to the query. It didn't expand without the tag, and with the tag it took a little bit more resources. Without NOEXPAND total cost was 0.0634, with NOEXPAND it was 0.0642.

    My question is if anyone knows the best practice for SQL Server 2000? Shall I use NOEXPAND to be prepared for a possible migration to SQL Server 2005?

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    lage_bergstrom (9/9/2008)


    Thank you for an interesting article!

    The company I work for is still using SQL Server 2000. I tried the example and it seem like the NOEXPAND just gave an extra overhead to the query. It didn't expand without the tag, and with the tag it took a little bit more resources. Without NOEXPAND total cost was 0.0634, with NOEXPAND it was 0.0642.

    My question is if anyone knows the best practice for SQL Server 2000? Shall I use NOEXPAND to be prepared for a possible migration to SQL Server 2005?

    Lage,

    All of my testing was on SQL Server 2005 and 2008 (and all my references are specifically for 2005), but I believe the same things apply to 2000, and if you are using something other than Enterprise Edition you likely want to use noexpand where it is appropriate.

    As for the time trials, make certain you are testing them in separately and not in a batch, otherwise the caching, execution plan generation, etc will cause one portion to affect the others. When I try running them together in one batch, my results are inconsistent with it coming out faster with the noexpand sometimes and without other times. When I run them separately the one with noexpand comes out slightly, but consistently better. Of course, I kept these sample sizes small, if you increase the table sizes the difference becomes much more marked.

    To provide an anecdote, I once reduced a query that was being run routinely from taking over an hour to taking less than a minute by indexing the view and adding with (noexpand). But that was a very large query which needed several joins against large tables.

    (edited to correct a typo)

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • lage_bergstrom

    Valued Member

    Points: 53

    Thank you Timothy for your answer. This was really useful.

    Lage

  • Wayne West

    SSC-Insane

    Points: 22586

    I would like to use an indexed view in our ERP system (SS 2000 Enterprise) but I'm paranoid because it's a canned system and we don't have the source, I'm afraid that I could break something. I have two areas where performance is routinely very sub-par and I'm certain indexed views would bring performance up to proper levels.

    The system has a lot of design problems, but I'm stuck with it, so that's the way it is. I'd probably lose the indexed views whenever they did an upgrade, but recreating them would be a minimal hassle.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 15 posts - 1 through 15 (of 20 total)

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