Optimizing roll-up query on normalized schema

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

SQL Server 2012 Forums

Transact-SQL (2012)

Optimizing roll-up query on normalized schema

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2013-03-08 : 14:06:09

Hello,

We have a table in SQL Server 2012 which contains information about files, such as CreatedDate and Size. We created a report which returns the number of files and their cumulative size by year. For example, it would have a row 2012, 1000000, 100GB, which means that in 2012 there were 1000000 files created with total size of 100GB.

This reports takes many hours to run. Creating an index on CreatedDate does not help because all rows end up being aggregated anyway.

What are our optimization options here besides creating a star schema? Create Indexed View? What else?

Thanks,

Alec

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-08 : 23:42:00

-- this may help if there is no time on the CreatDate and uses the index on the CreateDate
-- with a possible include of size
Select Year(CreateDate),sum(CNT), sum(size2) as Size
from (select Createdate,count(*) as CNT, sum(size) as size2
From YourTable Group by Createdate) z
Group by Year(CreateDate)

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-09 : 00:47:27

Create view dbo.Testvw
WITH SCHEMABINDING

AS
Select
size, year(CreateDate) as cd,[ID]
from dbo.YourTable;
go

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Testvw(cd,size,ID);

GO
select count(*),sum(size),cd
from Testvw
group by td
go
-- or (as they both use the index created)
select count(*),sum(size),year(CreateDate) as td
from YourTable
group by year(CreateDate)
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources