SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Optimizing roll-up query on normalized schema
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lebedev
Posting Yak Master

USA
126 Posts

Posted - 03/08/2013 :  14:06:09  Show Profile  Reply with Quote
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

Australia
54 Posts

Posted - 03/08/2013 :  23:42:00  Show Profile  Reply with Quote
-- 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)

Edited by - UnemployedInOz on 03/09/2013 00:05:02
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/09/2013 :  00:47:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000