In the first part, we looked at the a very simple example with a single table SELECT using covering indexes. This one is a little more complex. We are still covering but we are joining two tables, ordering and grouping. Filtered indexes do not support joins so we have to create two indexes. In this test, the indexed view wins but filtered indexes come a respectable 2nd place compared to normal indexes.
Â
The DDL(2.5M in tblitem and 25M in tblitemdetails)
CREATE TABLE [dbo].[tblItem](
[Itemid] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](100) NULL,
[Datecreated] [datetime] NULL,
[Closed] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Itemid] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblItemDetails](
[ItemDetailsID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NULL,
[Color] [varchar](20) NULL,
[Size] [varchar](20) NULL,
[Flavor] [varchar](20) NULL,
[ItemDetailsID] ASC
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tblItemDetails] WITH CHECK ADDCONSTRAINT [fk01] FOREIGN KEY([ItemID])
REFERENCES [dbo].[tblItem] ([Itemid])
ALTER TABLE [dbo].[tblItemDetails] CHECK CONSTRAINT [fk01]
--Create normal and filtered indexes
create index ix01 on tblitem(Datecreated, ItemName)
create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22' and itemid > 0 and itemid < 50
create index ix01 on tblitemdetails(itemid, color)
create index ix02 on tblitemdetails(itemid, color) where itemid > 0 and itemid < 50
--Create indexed views
alter view ivw01
with schemabinding
as
select a.ItemID, b.Color, COUNT_big(*) as cnt
from dbo.tblItem a join dbo.tblItemDetails b on a.Itemid= b.ItemID
where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50
group by a.ItemID, b.Color
create unique clustered index ix01 on ivw01(itemid)
create index ix02 on ivw01(itemid, color)
Test Queries
set statistics io on
--Using a normal covering index
from dbo.tblItem a with (index=ix01) join dbo.tblItemDetails b with (index=ix01) on a.Itemid= b.ItemID
order by cnt
--using a filtered index
from dbo.tblItem a with (index=ix02) join dbo.tblItemDetails b with (index=ix02) on a.Itemid= b.ItemID
--using an indexed view
select * from ivw01
IO numbers:
Normal covering indexes
(8 row(s) affected)Table 'tblItemDetails'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Filtered covering indexes
(8 row(s) affected)Table 'tblItem'. Scan count 1, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tblItemDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Indexed View
(8 row(s) affected)Table 'ivw01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Note: By increasing the result set 100 fold(itemid > 0 and itemid < 5000), we see very similar results except with merge joins.
Query Plans:
The query plans are different. Most notably being the scans for the filtered index and indexed view vs. the normal index. This is fine though. They as just a fraction of the size of the normal index.
Conclusion
The mere fact that the indexed view runs with 50 times less IO overhead does not make it the automatic choice. What if you are on standard edition? Filtered indexes work on standard edition. Also we filtered on a date column. What if you need a real time rolling total? You have to use a normal index. Know you tools and then test, test and test some more.
posted @ Monday, September 22, 2008 7:38 PM
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail