Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View index not being used Expand / Collapse
Author
Message
Posted Thursday, August 01, 2013 11:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 5:13 AM
Points: 5, Visits: 46
Hi there,

I have the following indexed view:

CREATE VIEW [dbo].[campaignContacts] WITH SCHEMABINDING
AS

SELECT cs.campaignId, sc.contactId, COUNT_BIG(*) AS total
FROM dbo.campaignSources cs
INNER JOIN dbo.sources s ON s.id = cs.sourceId
INNER JOIN dbo.sourceContacts sc ON sc.sourceId = s.id
GROUP BY cs.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?


Post #1480058
Posted Thursday, August 01, 2013 11:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 12,744, Visits: 31,065
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480059
Posted Thursday, August 01, 2013 10:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 2,842, Visits: 2,423
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


Post #1480244
Posted Friday, August 02, 2013 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 5:13 AM
Points: 5, Visits: 46
Thanks for the replies, very helpful.
Post #1480323
Posted Friday, August 02, 2013 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,754, Visits: 12,854
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1480326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse