August 1, 2013 at 11:45 am
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?
August 1, 2013 at 11:50 am
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
August 1, 2013 at 10:30 pm
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
August 2, 2013 at 4:21 am
Thanks for the replies, very helpful.
August 2, 2013 at 4:28 am
-- 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
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