Indexes being improperly used when selecting data through a view

[joshsackett]

I am having a problem with indexes on specific tables. For some reason
a query that runs against a view is not selecting the correct index on
a table. I run the same query against the table directly and it looks
fine. Can anyone give me some insight? Thanks.

PRODUCTION1:
CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
CHAR(10))
CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)

ARCHIVE1:
CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
CHAR(10))
CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)

REPORTDB:
CREATE VIEW MyTest1 AS
SELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..My Test1
UNION ALL
SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTes t1

While in PRODUCTION1:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Clustered index seek PRODUCTION1..ID X_MyTest2
--> Results returned

While in ARCHIVE1:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Clustered index seek ARCHIVE1..IDX_M yTest2
--> Results returned

While in REPORTDB:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Index seek PRODUCTION1..ID X_MyTest2
--> Bookmark lookup PRODUCTION1..ID X_MyTest1
--> Index seek ARCHIVE1..IDX_M yTest2
--> Bookmark lookup ARCHIVE1..IDX_M yTest1
--> Concatenate data and results returned

[joshsackett]

Correction... "UNION ALL" should just say "UNION"

[Erland Sommarskog]

joshsackett (jo*********@gm ail.com) writes:

I am having a problem with indexes on specific tables. For some reason
a query that runs against a view is not selecting the correct index on
a table. I run the same query against the table directly and it looks
fine. Can anyone give me some insight? Thanks.

PRODUCTION1:
CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
CHAR(10))
CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)
You have:
While in PRODUCTION1:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Clustered index seek PRODUCTION1..ID X_MyTest2
--> Results returned
But that is not possible. Either you would have an Index Seek +
Bookmark Lookup oon IDX_MyTest2 or a Clustered Index Scan on IDX_MyTest1.
REPORTDB:
CREATE VIEW MyTest1 AS
SELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..My Test1
UNION ALL
SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTes t1
...
While in REPORTDB:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Index seek PRODUCTION1..ID X_MyTest2
--> Bookmark lookup PRODUCTION1..ID X_MyTest1
--> Index seek ARCHIVE1..IDX_M yTest2
--> Bookmark lookup ARCHIVE1..IDX_M yTest1
--> Concatenate data and results returned

What happens if you actually have the UNION ALL, and not the UNION as
you correct later? With UNION SQL Server will have to do an operation
to sort out duplicates - that may affect the choice of plan.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[joshsackett]

I realized that it had to do with the UNION command. It was performing
a poor search in the first place and when the UNION ran it had to
perform a sort.

Thanks for the help.