|
|
 |
 |
 |
 |
Indexed views driving me batty ...
I have an indexed view iv_cm. And I have a query like this: select *
from iv_cm with(noexpand)
where iv_cm.col_a = 12345
The optimizer does the right thing for this query. It does an "index
seek" on iv_cm using the index on col_a.
But if there is any other constraint on any other column in the where
clause, it will not use the index on col_a. Instead it does a
"clustered index scan" on iv_cm.
e.g. if the query is something like:
select *
from iv_cm with(noexpand)
where iv_cm.col_a = 12345
and iv_cm.col_b = <foo>
In this case the optimizer does a "clustered indexed scan" on iv_cm
using the clustered index on the primary key of iv_cm. Why doesnt it
do a bookmark lookup and a filter for the condition on col_b which is
what it would do if iv_cm was a table instead of an indexed view? The
index on col_a is very selective. iv_cm has a very large number of
rows. We are seeing this on SQL Server 2000.
This is driving me nuts. Is this just a plain old bug in SQL Server
2000? Is this fixed in SQL Server 2005?
Thanks in advance for any and all help!
Anil
P.S. We are looking for consultants who know SQL Server inside out. If
you know the answer to this type of questions and are looking for a
gig in the Bay Area, please let me know.
Could you post the DDL for the view and base tables? --
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"SQL Practitioner" <sqlpractitio @gmail.com> wrote in message ... I have an indexed view iv_cm. And I have a query like this: select *
from iv_cm with(noexpand)
where iv_cm.col_a = 12345
The optimizer does the right thing for this query. It does an "index
seek" on iv_cm using the index on col_a.
But if there is any other constraint on any other column in the where
clause, it will not use the index on col_a. Instead it does a
"clustered index scan" on iv_cm.
e.g. if the query is something like:
select *
from iv_cm with(noexpand)
where iv_cm.col_a = 12345
and iv_cm.col_b = <foo>
In this case the optimizer does a "clustered indexed scan" on iv_cm
using the clustered index on the primary key of iv_cm. Why doesnt it
do a bookmark lookup and a filter for the condition on col_b which is
what it would do if iv_cm was a table instead of an indexed view? The
index on col_a is very selective. iv_cm has a very large number of
rows. We are seeing this on SQL Server 2000.
This is driving me nuts. Is this just a plain old bug in SQL Server
2000? Is this fixed in SQL Server 2005?
Thanks in advance for any and all help!
Anil
P.S. We are looking for consultants who know SQL Server inside out. If
you know the answer to this type of questions and are looking for a
gig in the Bay Area, please let me know.
-----------------------------------------------Reply-----------------------------------------------
On May 30, 3:24 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > Could you post the DDL for the view and base tables? > --
> Tom
I have abbreviated the DDL to spare you all the gory details of the underlying table. The view definition is create view dbo.tiv_om_cm_3760 with SCHEMABINDING as
select ...
from dbo.tt_om_cm
where om_cm_id = 0 or (org_id = 3760 and isactive = 1)
GO
CREATE UNIQUE CLUSTERED INDEX [tiv_om_cm_3760_idx1] ON [dbo].
[tiv_om_cm_3760]([om_cm_id]) ON [PRIMARY]
GO
CREATE INDEX [tiv_om_cm_3760_containernum_x1] ON [dbo].
[tiv_om_cm_3760]([containernum]) ON [PRIMARY]
GO
And the query is of the form:
select *
from tiv_om_cm_3760 with(noexpand)
where tiv_om_cm_3760.containernum = 'ABCD123456'
and tiv_om_cm_3760.mode_id = 1 -- with this clause it doesnt use
index on containernum. without it, the index is used.
-----------------------------------------------Reply-----------------------------------------------
What is the datatype of mode_id? You may want to cast the comparison with 1 to be the same. For example, if it is smallint, try: select *
from tiv_om_cm_3760 with(noexpand)
where tiv_om_cm_3760.containernum = 'ABCD123456'
and tiv_om_cm_3760.mode_id = cast (1 as smallint)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"SQL Practitioner" <sqlpractitio @gmail.com> wrote in message ... On May 30, 3:24 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > Could you post the DDL for the view and base tables? > --
> Tom
I have abbreviated the DDL to spare you all the gory details of the underlying table. The view definition is create view dbo.tiv_om_cm_3760 with SCHEMABINDING as
select ...
from dbo.tt_om_cm
where om_cm_id = 0 or (org_id = 3760 and isactive = 1)
GO
CREATE UNIQUE CLUSTERED INDEX [tiv_om_cm_3760_idx1] ON [dbo].
[tiv_om_cm_3760]([om_cm_id]) ON [PRIMARY]
GO
CREATE INDEX [tiv_om_cm_3760_containernum_x1] ON [dbo].
[tiv_om_cm_3760]([containernum]) ON [PRIMARY]
GO
And the query is of the form:
select *
from tiv_om_cm_3760 with(noexpand)
where tiv_om_cm_3760.containernum = 'ABCD123456'
and tiv_om_cm_3760.mode_id = 1 -- with this clause it doesnt use
index on containernum. without it, the index is used.
-----------------------------------------------Reply-----------------------------------------------
On May 30, 6:58 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > What is the datatype of mode_id? You may want to cast the comparison with 1 > to be the same. For example, if it is smallint, try: > select *
> from tiv_om_cm_3760 with(noexpand)
> where tiv_om_cm_3760.containernum = 'ABCD123456'
> and tiv_om_cm_3760.mode_id = cast (1 as smallint)
> --
> Tom
Oh, believe me I have tried all that and then some. It doesnt matter what the second condition is. You can make it "3.14159*om_mode_id = 1". You can make it "om_mode_id*om_mode_id > 0". The second condition can be on other columns on tiv_om_cm_3760 that is of type string or date. The only way I can get it to not do a clustered index scan is if I change the second where condition to a sub-select like so: select *
from tiv_om_cm_3760 with(noexpand)
where tiv_om_cm_3760.containernum = 'ABCD123456'
and exists (
select tiv_om_cm_3760 foo with(noexpand)
where foo.om_cm_id = tiv_om_cm_3760.om_cm_id
and mode_id = cast (1 as smallint)
)
The other thing I can do is to add every column that could possibly
show up in the where clause to the index on the indexed view.
But all that is getting silly and impractical in the real world.
Looks like there is a rule that says "An index on an indexed view will
be used only if the columns in the index is a superset of the columns
referred to in the where clause".
Or the optimizer sets the cost of a "bookmarked lookup" to be much,
much, much higher for an indexed view than for a regular table.
Extremely frustrating.
Thanks for all your help.
Anil
-----------------------------------------------Reply-----------------------------------------------
On May 30, 6:58 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > What is the datatype of mode_id? You may want to cast the comparison with 1 > to be the same. For example, if it is smallint, try: > select *
> from tiv_om_cm_3760 with(noexpand)
> where tiv_om_cm_3760.containernum = 'ABCD123456'
> and tiv_om_cm_3760.mode_id = cast (1 as smallint)
> --
> Tom
I was puzzled by your comments about the "cast". I guess you were referring to the issue in http://msdn2.microsoft.com/en-us/library/aa175773(sql.80).aspx. But in my case it is not that optimizer is going against the base table. All columns in the where clause are in the indexed view. And I have the noexpand to prevent it from going to the underlying table. My issue is that the indexes on the indexed view are not being used if there is any other constraint in the where clause. Anil
-----------------------------------------------Reply-----------------------------------------------
That article looks familiar. ;-) The point I was making was similar but different.
Just grabbing at straws here, but have you tried updating statistics on each
index of the indexed view - WITH FULLSCAN?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"SQL Practitioner" <sqlpractitio @gmail.com> wrote in message ... On May 30, 6:58 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > What is the datatype of mode_id? You may want to cast the comparison with > 1 > to be the same. For example, if it is smallint, try: > select *
> from tiv_om_cm_3760 with(noexpand)
> where tiv_om_cm_3760.containernum = 'ABCD123456'
> and tiv_om_cm_3760.mode_id = cast (1 as smallint)
> --
> Tom
I was puzzled by your comments about the "cast". I guess you were referring to the issue in http://msdn2.microsoft.com/en-us/library/aa175773(sql.80).aspx. But in my case it is not that optimizer is going against the base table. All columns in the where clause are in the indexed view. And I have the noexpand to prevent it from going to the underlying table. My issue is that the indexes on the indexed view are not being used if there is any other constraint in the where clause. Anil
-----------------------------------------------Reply-----------------------------------------------
On May 31, 1:38 pm, SQL Practitioner <sqlpractitio @gmail.com> wrote:
> On May 30, 6:58 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > > What is the datatype of mode_id? You may want to cast the comparison with 1
> > to be the same. For example, if it is smallint, try:
> > select *
> > from tiv_om_cm_3760 with(noexpand)
> > where tiv_om_cm_3760.containernum = 'ABCD123456'
> > and tiv_om_cm_3760.mode_id = cast (1 as smallint)
> > --
> > Tom
> I was puzzled by your comments about the "cast". I guess you were
> referring to the issue inhttp://msdn2.microsoft.com/en-us/library/aa175773(sql.80).aspx.
> But in my case it is not that optimizer is going against the base
> table. All columns in the where clause are in the indexed view. And I
> have the noexpand to prevent it from going to the underlying table. My
> issue is that the indexes on the indexed view are not being used if
> there is any other constraint in the where clause.
> Anil
Did you try covering indexes on your indexed view? -----------------------------------------------Reply-----------------------------------------------
On May 31, 12:09 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > That article looks familiar. ;-) > The point I was making was similar but different.
> Just grabbing at straws here, but have you tried updating statistics on each
> index of the indexed view - WITH FULLSCAN?
> --
> Tom
All stats are at fullscan. That is one of the first things that I check. Have you gurus come across or heard of something along these two rules
of thumb that I have come up with:
An index on an indexed view will be used only if the columns in the
index is a superset of the columns referred to in the where clause.
The optimizer sets the cost of a "bookmarked lookup" to be much, much,
much higher for an indexed view than for a regular table.
Thanks again for ...
Anil
-----------------------------------------------Reply-----------------------------------------------
> Did you try covering indexes on your indexed view?
Yes, if the index also has all the other columns that potentially could be part of a search criteria then the index will be used. But that is really impractical. If I have 50 columns in the indexed of
which 20 can be part of some search criteria and 10 of those columns
should be indexed. Then I need 10 indexes each with 20 covering
columns!
Anil
-----------------------------------------------Reply-----------------------------------------------
You may want to put that up at connect.microsoft.com. If it's an optimizer bug, which it sounds like it is, then it should be fixed. --
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"SQL Practitioner" <sqlpractitio @gmail.com> wrote in message ... On May 31, 12:09 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > That article looks familiar. ;-) > The point I was making was similar but different.
> Just grabbing at straws here, but have you tried updating statistics on
> each
> index of the indexed view - WITH FULLSCAN?
> --
> Tom
All stats are at fullscan. That is one of the first things that I check. Have you gurus come across or heard of something along these two rules
of thumb that I have come up with:
An index on an indexed view will be used only if the columns in the
index is a superset of the columns referred to in the where clause.
The optimizer sets the cost of a "bookmarked lookup" to be much, much,
much higher for an indexed view than for a regular table.
Thanks again for ...
Anil
-----------------------------------------------Reply-----------------------------------------------
On May 31, 2:54 pm, SQL Practitioner <sqlpractitio @gmail.com> wrote: > > Did you try covering indexes on your indexed view? > Yes, if the index also has all the other columns that potentially
> could be part of a search criteria then the index will be used.
> But that is really impractical. If I have 50 columns in the indexed of
> which 20 can be part of some search criteria and 10 of those columns
> should be indexed. Then I need 10 indexes each with 20 covering
> columns!
> Anil
Yes well IMO indexed views are not too practical in any environment. In OLTP systems they usually introduce a lot of lock contention and deadlocks and as such are not very commonly used. In DSS systems they are usually inferior to OLAP cubes. I think this is why indexed views do not get too much attention from the smart people who implement the database engine. -----------------------------------------------Reply-----------------------------------------------
On May 30, 6:58 pm, "Tom Moreau" <t @dont.spam.me.cips.ca> wrote: > What is the datatype of mode_id? You may want to cast the comparison with 1 > to be the same. For example, if it is smallint, try: > select *
> from tiv_om_cm_3760 with(noexpand)
> where tiv_om_cm_3760.containernum = 'ABCD123456'
> and tiv_om_cm_3760.mode_id = cast (1 as smallint)
> --
> Tom
Oh, believe me I have tried all that and then some. It doesnt matter what the second condition is. You can make it "3.14159*om_mode_id = 1". You can make it "om_mode_id*om_mode_id > 0". The second condition can be on other columns on tiv_om_cm_3760 that is of type string or date. The only way I can get it to not do a clustered index scan is if I change the second where condition to a sub-select like so: select *
from tiv_om_cm_3760 with(noexpand)
where tiv_om_cm_3760.containernum = 'ABCD123456'
and exists (
select tiv_om_cm_3760 foo with(noexpand)
where foo.om_cm_id = tiv_om_cm_3760.om_cm_id
and mode_id = cast (1 as smallint)
)
The other thing I can do is to add every column that could possibly
show up in the where clause to the index on the indexed view.
But all that is getting silly and impractical in the real world.
Looks like there is a rule that says "An index on an indexed view will
be used only if the columns in the index is a superset of the columns
referred to in the where clause".
Or the optimizer sets the cost of a "bookmarked lookup" to be much,
much, much higher for an indexed view than for a regular table.
Extremely frustrating.
Thanks for all your help.
Anil
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc