Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Do index views work at different time levels?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

29 Posts

Posted - 06/13/2006 :  17:45:40  Show Profile
Sorry for the dup post... I figured this may have more exposure then 2005
For some reason... I may just not be seeing it but I cannot for the life of me get the SQL optimizer to successfully use date aggregation awareness to choose the correct table or indexed view source to perform its aggregation on.
Example I have a detail table (detail day level) and indexed view at the month level.
when a user performs a select against the detail table with a date constraint involving 1 month worth of data ie BETWEEN '1/1/2006' AND '1/31/2006'
The optimizer should be intelligent enough to choose the summarized indexed view over the detail table. Less IO is always good...
Oddly I cannot get it to work... though if I perform a select against the detail table with no time constraint it goes against the summary indexed view as expected.... arrgggg...

Here is a script I wrote to reproduce the issue:

/* code start : */

	--drop sample objects
drop view mv_summary
drop table tbl_detail
drop table time_dim
drop table client_dim

	--create detail table
create table tbl_detail(
ID int identity(1,1),
invoice_date datetime,
invoice_month as  CASE 
			WHEN cast(datepart(mm,invoice_date) as varchar(2)) < = 9 THEN  
cast(datepart(yyyy,invoice_date)as varchar(4)) + '0' + 
cast(datepart(mm,invoice_date)as varchar(2))
			ELSE cast(datepart(yyyy,invoice_date)as varchar(4)) + 
cast(datepart(mm,invoice_date) as varchar(2))
month_begin_date as dateadd(month,datediff(month,0,[invoice_date]),0),
client_id varchar(10),
sales money not null)

	--create time dimension
create table time_dim(
date_number datetime ,
month_begin_date as dateadd(month,datediff(month,0,date_number),0),
month_code as  CASE 
			WHEN cast(datepart(mm,date_number) as varchar(2)) < = 9 THEN  
cast(datepart(yyyy,date_number)as varchar(4)) + '0' + 
cast(datepart(mm,date_number)as varchar(2))
			ELSE cast(datepart(yyyy,date_number)as varchar(4)) + 
cast(datepart(mm,date_number) as varchar(2))

	--create client list
create table  client_dim (client_id varchar(10), client_desc varchar(20), 
usedflag bit null)
insert into client_dim(client_id,client_desc)values('A0000001','A0000001TEST')
insert into client_dim(client_id,client_desc)values('A0000002','A0000002TEST')
insert into client_dim(client_id,client_desc)values('A0000003','A0000003TEST')
insert into client_dim(client_id,client_desc)values('A0000004','A0000004TEST')
insert into client_dim(client_id,client_desc)values('A0000005','A0000005TEST')
insert into client_dim(client_id,client_desc)values('A0000006','A0000006TEST')
insert into client_dim(client_id,client_desc)values('A0000007','A0000007TEST')
insert into client_dim(client_id,client_desc)values('A0000008','A0000008TEST')
insert into client_dim(client_id,client_desc)values('A0000009','A0000009TEST')
insert into client_dim(client_id,client_desc)values('A0000010','A0000010TEST')

	--create index
create index idx_client_id on client_dim (client_id)

	--loop through each client and build a random list of data.
declare @loopcnt int
	set @loopcnt = 0
declare @clientloopcnt int
	set @clientloopcnt = 0
declare @clientid varchar(10)
declare @nextdate datetime
set		@nextdate = getdate()

	--populate detail table 
	while @loopcnt < 1--0000

			insert into tbl_detail ( invoice_date, client_id, sales) 
			cast(cast(getdate() as int)  -115* rand(cast(cast(newid() as binary(8)) 
as int))as datetime) as invoice_date,
			cast(cast(100 as int)  -115* rand(cast(cast(newid() as binary(8)) as 
int))as money)
			from client_dim --where usedflag is not null
			set @loopcnt = @loopcnt + 1
			print 'The loop counter is ' + cast(@loopcnt as char)
			--update the client as being complete...
			set @clientloopcnt = @clientloopcnt + 1
	--populate time dimension 
while @loopcnt < 365

			insert into time_dim (date_number) 
			values (@nextdate)
			set @nextdate = @nextdate + 1	
			set @loopcnt = @loopcnt + 1
			print 'The loop counter is ' + cast(@loopcnt as char)
			--update the client as being complete...

	--create indexed view
create view mv_summary with schemabinding
select a.month_begin_date, a.client_id, sum(a.sales) as sales, count_big(*) 
as RC
from dbo.tbl_detail a
group by a.month_begin_date ,a.client_id
create unique clustered index cidx_client_invoice_date on mv_summary( 
client_id, month_begin_date)

	--this query invokes the summary as expected...
select a.month_begin_date , b.client_id , b.client_desc , sum(sales) as sales
from dbo.tbl_detail a 
inner join dbo.client_dim b
a.client_id = b.client_id 
where b.client_desc = 'A0000001TEST' 
group by a.month_begin_date, b.client_id , b.client_desc

	--this query does NOT invokes the summary... though not sure why...
select  a.month_begin_date,b.client_id , b.client_desc , sum(sales) as sales
from dbo.tbl_detail a 
inner join dbo.client_dim b
a.client_id = b.client_id 
inner join time_dim t
t.month_begin_date = a.month_begin_date 
where b.client_desc = 'A0000001TEST' and t.date_number between '6/1/2006' and '6/30/2006'
group by  a.month_begin_date,b.client_id , b.client_desc

/* code end: */

Any Thoughts?

Patron Saint of Lost Yaks

30421 Posts

Posted - 06/13/2006 :  19:26:15  Show Profile  Visit SwePeso's Homepage
There is a big difference for Indexed Views between using Standard Edition of SQL Server or Enterprise Edition.

When using Standard Edition, Indexed Views DOES NOT WORK unless hinting the index in the query! This is not necessary for Enterprise Edition.

See Books Online.

To be sure you can create a multiple select with IF statement
IF SERVERPROPERTY('EngineEdition') = 3
    write code for enterprise edition
    write code for standard edition

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/13/2006 19:26:37
Go to Top of Page

Starting Member

29 Posts

Posted - 06/13/2006 :  19:35:42  Show Profile
Hi Peter, we are currently running SQL 2000 / 2005 Enterprise Version.
The issue is more along the lines that the optimizer seems to be super selective as to when it aggregates up to a higher level of aggregation (ie the summary indexed view)

Query 1 ( see prior post for SQL) ahhh good SQL

  |--Stream Aggregate(GROUP BY:([b].[client_id], [TEST_DB].[dbo].[mv_summary].[month_begin_date]) DEFINE:([Expr1006]=SUM([TEST_DB].[dbo].[mv_summary].[sales]), [b].[client_desc]=ANY([TEST_DB].[dbo].[client_dim].[client_desc] as [b].[client_desc])))
       |--Sort(ORDER BY:([TEST_DB].[dbo].[mv_summary].[client_id] ASC, [TEST_DB].[dbo].[mv_summary].[month_begin_date] ASC))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[client_id]))
                 |--Table Scan(OBJECT:([TEST_DB].[dbo].[client_dim] AS [b]), WHERE:([TEST_DB].[dbo].[client_dim].[client_desc] as [b].[client_desc]='A0000001TEST'))
                 |--Clustered Index Seek(OBJECT:([TEST_DB].[dbo].[mv_summary].[cidx_client_invoice_date]), SEEK:([TEST_DB].[dbo].[mv_summary].[client_id]=[TEST_DB].[dbo].[client_dim].[client_id] as [b].[client_id]) ORDERED FORWARD)

Query 2 ( see prior post for SQL) BAD SQL

  |--Hash Match(Aggregate, HASH:([a].[month_begin_date], [b].[client_id]), RESIDUAL:([TEST_DB].[dbo].[tbl_detail].[month_begin_date] as [a].[month_begin_date] = [TEST_DB].[dbo].[tbl_detail].[month_begin_date] as [a].[month_begin_date] AND [TEST_DB].[dbo].[client_dim].[client_id] as [b].[client_id] = [TEST_DB].[dbo].[client_dim].[client_id] as [b].[client_id]) DEFINE:([Expr1009]=SUM([TEST_DB].[dbo].[tbl_detail].[sales] as [a].[sales]), [b].[client_desc]=ANY([TEST_DB].[dbo].[client_dim].[client_desc] as [b].[client_desc])))
       |--Hash Match(Inner Join, HASH:([t].[month_begin_date])=([a].[month_begin_date]), RESIDUAL:([TEST_DB].[dbo].[time_dim].[month_begin_date] as [t].[month_begin_date]=[TEST_DB].[dbo].[tbl_detail].[month_begin_date] as [a].[month_begin_date]))
            |--Compute Scalar(DEFINE:([t].[month_begin_date]=[TEST_DB].[dbo].[time_dim].[month_begin_date] as [t].[month_begin_date]))
            |    |--Compute Scalar(DEFINE:([t].[month_begin_date]=dateadd(month,datediff(month,'1900-01-01 00:00:00.000',[TEST_DB].[dbo].[time_dim].[date_number] as [t].[date_number]),'1900-01-01 00:00:00.000')))
            |         |--Table Scan(OBJECT:([TEST_DB].[dbo].[time_dim] AS [t]), WHERE:([TEST_DB].[dbo].[time_dim].[date_number] as [t].[date_number]>='2006-06-01 00:00:00.000' AND [TEST_DB].[dbo].[time_dim].[date_number] as [t].[date_number]<='2006-06-30 00:00:00.000'))
            |--Hash Match(Inner Join, HASH:([b].[client_id])=([a].[client_id]), RESIDUAL:([TEST_DB].[dbo].[tbl_detail].[client_id] as [a].[client_id]=[TEST_DB].[dbo].[client_dim].[client_id] as [b].[client_id]))
                 |--Table Scan(OBJECT:([TEST_DB].[dbo].[client_dim] AS [b]), WHERE:([TEST_DB].[dbo].[client_dim].[client_desc] as [b].[client_desc]='A0000001TEST'))
                 |--Compute Scalar(DEFINE:([a].[month_begin_date]=[TEST_DB].[dbo].[tbl_detail].[month_begin_date] as [a].[month_begin_date]))
                      |--Compute Scalar(DEFINE:([a].[month_begin_date]=dateadd(month,datediff(month,'1900-01-01 00:00:00.000',[TEST_DB].[dbo].[tbl_detail].[invoice_date] as [a].[invoice_date]),'1900-01-01 00:00:00.000')))
                           |--Table Scan(OBJECT:([TEST_DB].[dbo].[tbl_detail] AS [a]))


Edited by - datamonkey on 06/15/2006 20:08:49
Go to Top of Page

Starting Member

29 Posts

Posted - 06/15/2006 :  20:07:04  Show Profile
That’s weird, MS doesn’t seem to have any information nor any examples including time other then querying directly against the view... has any about got indexed views to work in a time aware aggregation? In others words getting the optimizer to fetch the data from an indexed view based on a date range (one that can be satisfied by the indexed view) even when you explicitly queried the detail table?

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000