Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and aggregates.

I have experimented and have created indexes by studying the execution plan and some by reading the suggested indexes and all of them have improved performance by some extent.

My question is what can further be done in this case, what indexes can be applied or how can the query be modified to gain better performance and to reduce execution time?

So first the query to create and fill the tables and to create Indexes:

CREATE TABLE FactTable (id BIGINT IDENTITY PRIMARY KEY, FKDim1 BIGINT NOT NULL, FKDim2 BIGINT, DateRef DATETIME, Fact1 MONEY, Fact2 MONEY)
CREATE TABLE Dim1Table (id BIGINT IDENTITY PRIMARY KEY, Dim1Name NVARCHAR(20), Dim1Val1 MONEY, Dim1Val2 MONEY)
CREATE TABLE Dim2Table (id BIGINT IDENTITY PRIMARY KEY, Dim2Name NVARCHAR(20), Dim2Val1 MONEY, Dim2Val2 MONEY)
CREATE TABLE CalendarTable (id BIGINT IDENTITY PRIMARY KEY, [Date] DATETIME UNIQUE NONCLUSTERED, [Weekday] NVARCHAR(10), [Month] NVARCHAR(10))

ALTER TABLE FactTable ADD CONSTRAINT FK_Dim1 FOREIGN KEY (FKDim1 ) REFERENCES Dim1Table(ID);
ALTER TABLE FactTable ADD CONSTRAINT FK_Dim2 FOREIGN KEY (FKDim2 ) REFERENCES Dim1Table(ID);
ALTER TABLE FactTable ADD CONSTRAINT FK_Calendar FOREIGN KEY (DateRef) REFERENCES CalendarTable([Date]);

DECLARE @counter INT;
SET @counter = 1;

WHILE @counter < 10000
BEGIN
INSERT INTO Dim1Table(Dim1Name,Dim1Val1,Dim1Val2)VALUES('Dim1-'+CAST((@counter % 100) AS NVARCHAR),RAND() * 10000,RAND() * 20000);
INSERT INTO Dim2Table(Dim2Name,Dim2Val1,Dim2Val2)VALUES('Dim2-'+CAST(@counter AS NVARCHAR),RAND() * 10000,RAND() * 20000);
SET @counter = @counter + 1;
END

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = CAST('1/1/1995' AS DATETIME)
SET @EndDate = DATEADD(d, 3650, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO CalendarTable([Date],[Weekday],[Month])SELECT @StartDate, DATENAME(dw, @StartDate), DATENAME(MONTH, @StartDate)
SET @StartDate = DATEADD(dd, 1, @StartDate)
END

SET @counter = 1;
WHILE @counter < 500000
BEGIN
INSERT INTO FactTable
(FKDim1,FKDim2,DateRef,Fact1,Fact2)VALUES(@counter % 10000,@counter % 10000, DATEADD(dd, @counter % 3650, CAST('1/1/1995' AS DATETIME)), RAND() * 10000, RAND() * 20000)
SET @counter = @counter + 1
END

Code to create Indexes:

CREATE NONCLUSTERED INDEX [Dim1TableIndex1] ON [dbo].[Dim1Table]([Dim1Name] ASC)INCLUDE([id], [Dim1Val1], [Dim1Val2]);
CREATE NONCLUSTERED INDEX [Dim1TableIndex2] ON [dbo].[Dim2Table]([Dim2Name] ASC)INCLUDE([id], [Dim2Val1], [Dim2Val2]);
CREATE NONCLUSTERED INDEX [FactTableIndex1] ON [dbo].FactTable(FKDim1 ASC)INCLUDE(FKDim2, DateRef, Fact1, Fact2);
CREATE NONCLUSTERED INDEX [FactTableIndex2] ON [dbo].FactTable(FKDim2 ASC)INCLUDE(FKDim1, DateRef, Fact1, Fact2);
CREATE UNIQUE NONCLUSTERED INDEX [CalnedarIndex1] ON [dbo].[CalendarTable]([Date] ASC)INCLUDE ([id],[Weekday],[Month]);

Query 1: Simple join of Fact table with Calendar and Dimension tables, and a where clause:

SELECT D1.Dim1Name,
       D2.Dim2Name,
       C.[Date],
       C.[Weekday],
       C.[Month],
       D1.Dim1Val1,
       D2.Dim2Val2,
       F.Fact1,
       F.Fact2
FROM   FactTable F
       JOIN Dim1Table D1
            ON  D1.id = F.FKDim1
       JOIN Dim2Table D2
            ON  D2.id = F.FKDim2
       JOIN CalendarTable C
            ON  F.DateRef = C.Date

Execution Details with Indexes turned off (all 5 mentioned above)

    (15000 row(s) affected)
Table 'CalendarTable'. Scan count 9, logical reads 82, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1Table'. Scan count 9, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 159 ms,  elapsed time = 475 ms.

And Execution plan: enter image description here

With Indexes enabled:

(15000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 300, logical reads 1083, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim1Table'. Scan count 3, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 389 ms.

And Execution Plan: enter image description here

Second query, which aggregates after join:

SELECT D1.Dim1Name,
       C.[Month],
       Sum(D1.Dim1Val1) SumDim1Val1,
       Sum(D2.Dim2Val2) SumDim2Val2,
       Sum(F.Fact1) SumFact1,
       Avg(F.Fact2) Fact2Avg
FROM   FactTable F
       JOIN Dim1Table D1
            ON  D1.id = F.FKDim1
       JOIN Dim2Table D2
            ON  D2.id = F.FKDim2
       JOIN CalendarTable C
            ON  F.DateRef = C.Date
GROUP BY D1.Dim1Name, C.[MONTH]

Performance with all indexes turned off:

(1200 row(s) affected)
Table 'Dim1Table'. Scan count 9, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 9, logical reads 82, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2436 ms,  elapsed time = 554 ms.

And Execution Plan: enter image description here

And with Indexes enabled:

(1200 row(s) affected)
Table 'Dim1Table'. Scan count 9, logical reads 181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CalendarTable'. Scan count 9, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Dim2Table'. Scan count 9, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactTable'. Scan count 9, logical reads 3710, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2060 ms,  elapsed time = 518 ms.

And finally the execution plan: enter image description here

The improvements I got are not very significant, but when I consider large number of rows, for example remove the where clause from query 1, then the indexes reduce the execution time from around 9.5 seconds to 8.3 seconds.

I will restate my questions here:

  1. How can the indexes be redesigned or new indexes added to enhance performance?
  2. How can the performance be enhanced by redesigning the queries?
  3. What can be done other than Indexes and Redesigning the queries?

I have presented simple examples but have tried to cover some typical scenarios and type of queries in a star schema, to the concept behind the answers of these specific questions will apply generally as well. And Using SQL Server 2012.

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

There is rarely any need, point or benefit trying to micro optimise star schema queries with non-clustered indexes laden with included columns. Fact tables are built to be scanned.

The indexes you've created in your examples are subset copies of the parent table, which are being scanned (no seeks). The minor performance improvements come from scanning marginally fewer pages than the parent table. Given that star schemas are built to support ad-hoc query patterns it is not viable to create the indexes to support every possible enquiry.

  • Create your fact table clustered index on the date key. The majority of (typical) fact table queries include a time element and clustering on the date key enables range scanning of fact table rows.
  • Add non-clustered indexes on the foreign keys of your fact tables to assist with highly selective queries. Foreign keys to dimension tables can be created with NOCHECK to prevent any impact on ETL.
  • Cluster your dimension tables on their surrogate keys.
  • Create a non-clustered index on the natural key of each dimension table.
  • Stop.

The optimiser detects star schema query patterns and has strategies to deal with them efficiently, utilising scans and hash joins in Standard Edition or bitmap filtering in Enterprise. Follow the indexing strategy outlined above and let the optimiser deal with the rest.

share|improve this answer
add comment

In addition to Mark's excellent answer, there are a few other strategies you can add on to your existing system (this is not an exhaustive list, of course):

  1. Pre-aggregated tables, or indexed views. This will physically materialize the results (or intermediate results) of the query, so SQL Server will end up scanning much smaller indexes to return the full result set. This keeps your project within the same database, using technology you're familiar with.

  2. Analysis Services. It may be worth looking at this if the plan is to support a lot of slicing-and-dicing of the data. Analysis Services was built to pre-aggregate the data automatically according to the parameters you enter. The disadvantage of this is that it's probably totally new technology for you. While I'm not an expert in this area, I will say there is a learning curve. It's a very powerful tool.

  3. Result caching. If not a lot of rows are coming back, and you're finding that users are running the same queries over and over, cache the results, and invalidate the cache when new data is loaded (or figure out a way to selectively invalidate based on the new data).

Depending on the exact requirements of your project, these may not be applicable, but they do offer performance/response time benefits if they can be implemented (individually, or in combination).

share|improve this answer
    
Good stuff. Your second point is potentially made easier with SSAS Tabular in 2012 (BI/Enteprise Editions) which I found has an easier learning curve than Multidimensional. –  Mark Storey-Smith Nov 6 '13 at 15:13
    
@Mark: Thanks for pointing that out. I'm not familiar with the new BI features in 2012 yet. –  Jon Seigel Nov 6 '13 at 16:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.