Solved

indexed views not being used by optimizer

Posted on 2011-09-08
14
426 Views
Last Modified: 2012-05-12
Please run the code below in a scratch database.  On the last query, check the execution plan.  

Why does it do a full table scan instead of doing an index seek on the covering index of the view?

The goal is to do a case-insensitive search on the LastName column, which in my real base table is collation Latin1_General_BIN (the server default here), by way of an index seek, not a scan.  I thought I could do this by creating a view of the table and setting the collation on that field to SQL_Latin1_General_CP1_CI_AS in the view, and index this version of the column on the view.

Thanks
create table dbo.tTest(id int identity(1,1), LastName varchar(80) COLLATE Latin1_General_BIN, FirstName varchar(80) COLLATE Latin1_General_BIN) 
GO

create unique clustered index tx0 on dbo.tTest(id)
GO

insert into dbo.tTest select 'Doe','John'
insert into dbo.tTest select 'Doe','Jane'
insert into dbo.tTest select 'Bach','Hellen'
insert into dbo.tTest select 'Smith','Jane'
insert into dbo.tTest select 'Smith','Mike'
insert into dbo.tTest select 'Jones','Mike'
GO

create view dbo.vTest  WITH SCHEMABINDING  as
select id, (LastName COLLATE SQL_Latin1_General_CP1_CI_AS) as LastName, (FirstName COLLATE SQL_Latin1_General_CP1_CI_AS) as FirstName
from dbo.tTest
GO

create unique clustered index vx0 on dbo.vTest(id) 
GO

create unique index vx1 on dbo.vTest(LastName,FirstName,id)
GO

select id, FirstName, LastName
from dbo.vTest -- with (index=vx1)
where LastName  = 'BACH'  
GO

--  drop view dbo.vTest
--  truncate table dbo.tTest
--  drop table dbo.tTest
GO

-- END OF SCRIPT --

--------------------------------------------------------------------------------------------
/** FYI - I can achieve the overall goal by creating calculated fields on the base table instead of a view (see below), but I would rather get this to work in a view (above) **/
--------------------------------------------------------------------------------------------


create table dbo.tTest(
                        id int identity(1,1), 
                        LastName  varchar(80) COLLATE Latin1_General_BIN, 
                        FirstName varchar(80) COLLATE Latin1_General_BIN,
                        cLastName  as (LastName  COLLATE SQL_Latin1_General_CP1_CI_AS),
                        cFirstName as (FirstName COLLATE SQL_Latin1_General_CP1_CI_AS)
                      ) 
GO

create unique clustered index tx0 on dbo.tTest(id)
GO

insert into dbo.tTest select 'Doe','John'
insert into dbo.tTest select 'Doe','Jane'
insert into dbo.tTest select 'Bach','Hellen'
insert into dbo.tTest select 'Smith','Jane'
insert into dbo.tTest select 'Smith','Mike'
insert into dbo.tTest select 'Jones','Mike'
GO

create unique index tx1 on dbo.tTest(cLastName,cFirstName,id)
GO

select id, cFirstName, cLastName
from dbo.tTest
where cLastName  = 'BACH'

--  drop view dbo.vTest
--  truncate table dbo.tTest
--  drop table dbo.tTest
GO

Open in new window

0
Comment
Question by:knightEknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
14 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36506296
I'm not sure and I can't run it right now but I would think that creating the view and changing the collation AND creating the compound index both Require the table to be completely scanned when they are created.  And your WHERE using on the LastName doesn't correspond to any index you have created.  You don't have an index on only LastName so it has to scan the table to find only that value.
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36506454
The index vx1  is on LastName, FirstName, id, which is sufficient for the WHERE clause containing only LastName.  Further, an index on only LastName would require the engine to do the additional step of going back to the base table for the other selected columns - that is why it is better (in cases like this) to include all selected columns in the index.  Also, see the second part of the script above, which makes use of calculated fields on the base table.  The fact that this works as desired is evidence that in principle this can work.  I just don't understand why the optimizer won't make use of the view index -- even with a query hint telling it to do so!  Any help here?
0
 
LVL 18

Accepted Solution

by:
lludden earned 400 total points
ID: 36506600
What edition of SQL are you using?

This might be pertinent
Indexed views are a feature of all versions of SQL Server 2000 and SQL Server 2005. In the Developer and Enterprise editions of SQL Server 2000 and SQL Server 2005, the query processor can use an indexed view to solve queries that structurally match the view, even if they don't refer to the view by name. In other versions, you must reference the view by name and use the NOEXPAND hint on the view reference to query the contents of an indexed view.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 36506690
I found this  http://blogs.msdn.com/b/mssqlisv/archive/2008/09/26/designing-composite-indexes.aspx  and this  http://www.sql-server-performance.com/2007/composite-indexes/  .  The best answer is to make the collation case-insensitive in the first place and eliminate the extra operations.
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36506699
Thanks, good article:  http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx

In this case, the NOEXPAND hint did the trick:

select id, FirstName, LastName
from dbo.vTest with (NOEXPAND)
where LastName  = 'BACH'  
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36506714
Very sorry, I accepted the wrong solution.  I had it already working from lluden's suggestion.  Sorry Dave, I'll have to fix this...
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36506727
... Dave posted just as I accepted, and I just went to the last post in the thread, thinking it was lluden's.  I've requested to have this thread re-opened so I can award the points appropriately.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36506741
That's ok, at least you got it working.
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36506757
I haven't looked over your articles yet Dave, but I agree with the premise you posted - make the collation case insensitive from the beginning.  However, in this case we did not have that option.  I appreciate your contribution and I will read them over.
0
 
LVL 33

Author Closing Comment

by:knightEknight
ID: 36510253
Thanks to both of you.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36512241
You're welcome.
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36513252
FYI - another good article on this exact subject:  http://www.eggheadcafe.com/software/aspnet/33165884/indexed-views-optimizer-hints-and-noexpand.aspx

The basic conclusion is that if the optimizer decides it has an execution plan that is "good enough" for this query, it runs with it.  Since in my example there are only six rows (1 page) in the table, one plan is probably as good as the next.  It may be the case that if the table has a few million rows in it, the optimizer will choose to use the index of the View (which in this case would be optimal) rather than any index on the base table.

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36513944
That makes sense to me.  The extra disk activity for the index may swamp the time to just scan the table with only 6 rows.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question