Do index views work at different time levels? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

All Forums

Old Forums

CLOSED - General SQL Server

Do index views work at different time levels?

Author

Topic

datamonkey
Starting Member

29 Posts

Posted - 2006-06-13 : 17:45:40

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))
			END,
month_begin_date as dateadd(month,datediff(month,0,[invoice_date]),0),
client_id varchar(10),
sales money not null)
go

	--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))
			END)
go

	--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')

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

	--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
	begin

			insert into tbl_detail ( invoice_date, client_id, sales) 
			select
			cast(cast(getdate() as int)  -115* rand(cast(cast(newid() as binary(8)) 
as int))as datetime) as invoice_date,
			client_id,
			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
		end
	--populate time dimension 
while @loopcnt < 365
	begin

			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...
		end
go

go
	--create indexed view
create view mv_summary with schemabinding
as
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
go
create unique clustered index cidx_client_invoice_date on mv_summary( 
client_id, month_begin_date)
go

	--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
on
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
on
a.client_id = b.client_id 
inner join time_dim t
on
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?
Thanks
DataMonkey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 19:26:15

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
ELSE
    write code for standard edition

Peter Larsson
Helsingborg, Sweden

datamonkey
Starting Member

29 Posts

Posted - 2006-06-13 : 19:35:42

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) no..no 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]))

Thanks
DataMonkey

datamonkey
Starting Member

29 Posts

Posted - 2006-06-15 : 20:07:04

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?

Thanks
DataMonkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources