I have large fact table, and a much smaller dimension table in a simple star schema:
--1. CREATE TABLE dbo.Dim ( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, CustomerName VARCHAR(2000) ) --index CREATE UNIQUE NONCLUSTERED INDEX uniqueindex1 ON Dim(CustomerName); --2. CREATE TABLE dbo.Fact ( ... PurchaseDate DATE CustomerNameId INT CONSTRAINT fk1 FOREIGN KEY (CustomerNameId) REFERENCES dbo.Dim(Id) ... ) --index CREATE CLUSTERED COLUMNSTORE INDEX ccs ON dbo.Fact;
Running the following simple query, which filters on fact table, and joins in the dimension:
SELECT sd.CustomerName,f.* FROM dbo.Fact f INNER JOIN dbo.Dim sd ON sd.Id = f.CustomerNameId WHERE f.PurchaseDate IN ( '20000506', '20000507', '20000508', '20000509', '20000501', '20000502', '20000503' )
Interestingly the dimension table tend to scan ALL its 500 000 rows in 4 iteration, but in the end only few thousand is needed in that date range of the fact table.
This is very inefficient with larger dimension tables, basically all the rows scanned all the time, like the lookup table indexes are not even there.
The expected thing would be that sql server first limits the fact table on the date range, then using this limited range of CustomerKeyId it looks up the CustomerName from the small dimension table using an index seek.
- Is this really how inefficiently the star schema is, or is there something i miss here?
- In other words, how could i force sql server to prepare the limited CustomerKeyId table and lookup only those? (with CTE somehow?)