| Author |
 Topic  |
|
|
lebedev
Posting Yak Master
USA
126 Posts |
 Posted - 03/08/2013 : 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
Australia
52 Posts |
Posted - 03/08/2013 : 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) |
Edited by - UnemployedInOz on 03/09/2013 00:05:02 |
 |
|
|
UnemployedInOz
Yak Posting Veteran
Australia
52 Posts |
Posted - 03/09/2013 : 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 |
|
|
| |
Topic |
|
|
|
Optimizing roll-up query on normalized schema
