View index not being used

  • Hi there,

    I have the following indexed view:

    CREATE VIEW [dbo].[campaignContacts] WITH SCHEMABINDING

    AS

    SELECTcs.campaignId, sc.contactId, COUNT_BIG(*) AS total

    FROMdbo.campaignSources cs

    INNER JOINdbo.sources s ON s.id = cs.sourceId

    INNER JOINdbo.sourceContacts sc ON sc.sourceId = s.id

    GROUP BYcs.campaignId, sc.contactId

    GO

    CREATE UNIQUE CLUSTERED INDEX [IX_campaignContacts] ON [dbo].[campaignContacts]

    (

    campaignId ASC,

    contactId ASC

    )

    GO

    Now, when I do a simple SELECT contactId FROM campaignContacts it still takes a few seconds to compute the query before delivering results (these are big tables), and in the execution plan the index is not used.

    I think I'm probably misunderstanding something critical about indexed views here, but surely if there is an index that already has the data I'm looking for, it should be used?

  • andybellenie (8/1/2013)


    CREATE UNIQUE CLUSTERED INDEX [IX_campaignContacts] ON [dbo].[campaignContacts]

    (

    campaignId ASC,

    contactId ASC

    )

    The index you created above would be used if you had a statement like this:

    SELECT contactId

    FROM campaignContacts

    WHERE campaignId = @p1

    AND contactId = @p2

    so the index specifically supports WHERE statements..

    if you don't have a WHERE statement, the optimizer would ignore that index, and use something different.

    does that help?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • What edition of SQL 2005 are you using ? From memory, Standard Edition will not use an index on a view unless you specify the NOEXPAND hint

  • Thanks for the replies, very helpful.

  • -- force usage of the view

    SELECT Column1, Column2, ... FROM Table1, View1

    WITH (NOEXPAND) WHERE ...

    -- force usage of the base tables

    SELECT Column1, Column2, ... FROM Table1, View1

    WHERE ...

    OPTION (EXPAND VIEWS)

    Source: http://technet.microsoft.com/en-us/library/cc917715.aspx

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 4 (of 4 total)

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