By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Indexes being improperly used when selecting data through a view

P: n/a
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..MyTest1
UNION ALL
SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1

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

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

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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Correction... "UNION ALL" should just say "UNION"

Nov 23 '05 #2

P: n/a
joshsackett (jo*********@gmail.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..IDX_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..MyTest1
UNION ALL
SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1
...
While in REPORTDB:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Index seek PRODUCTION1..IDX_MyTest2
--> Bookmark lookup PRODUCTION1..IDX_MyTest1
--> Index seek ARCHIVE1..IDX_MyTest2
--> Bookmark lookup ARCHIVE1..IDX_MyTest1
--> 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****@sommarskog.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
Nov 23 '05 #3

P: n/a
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.

Dec 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.