I am trying to optimize a query which looks similar to this:
select top(1) t1.Table1ID, t1.Column1, t1.... .... t2.Table2ID, t2.... .... c.FirstName, c.LastName, c.... from BigTable1 t1 join BigTable2 t2 on t1.Table1ID = t2.Table1ID join Customer c on t2.CustomerID = c.CustomerID join Table4 t4 on t4.Table4ID = t2.Table4ID join Table5 t5 on t5.Table5ID = t1.Table5ID join Table6 t6 on t6.Table6ID = t5.Table6ID where t4.Column1 = @p1 and t1.Column1 = @p2 and t3.FirstName = @FirstName and t3.LastName = @LastName and t6.Column1 = @p5 and (@p6 is null or t2.Column6 = @p6) order by t2.Table2ID desc option(recompile);
BigTable1, BigTable2, Customer - are big transactional table (hundreds millions of rows), Table4,Table5,Table6 are relativly static and small lookup tables. The problem is that data in those big tables has pretty skewed distribution and because of that very often this query performs very poorly (estimated number of rows in execution plan are very different from the actual). Updating statistics on those big tables does not help (200 steps in histogram are not enough to cover all the skews in data distribution). For example, in Customer table there are a few (FirstName, LastName) combinations that correspond to about 500k records.
I see 2 options to improve performance of such queries:
- Split this query into smaller ones and saving intermidiate results into temporary tables. With this approach of materializing intermidiate results into temporary tables we can give the optimizer a better opportunity for cardinality estimations, but we add a substantial additiobal load on tempdb (cause this query is executed rather frequently, up to several times a second). And another drawback is that it is not faster for all parameter values, it appears to be significantly better for atypical parameter values (when the original query can take minutes, this takes just few seconds), but for parameters that correspond to a more o less unique (or infrequent) rows the approach with temp tables is slower.
Create filtered statistics for the spike values, for example for Customer table it would look like this:
declare @sql nvarchar(max) = N'', @i int, @N int; select top (1000) identity(int,1,1) as id, FirstName, LastName, count(*) as cnt into #FL from Customer where FirstName is not null and LastName is not null group by FirstName, LastName order by cnt desc; set @N = @@ROWCOUNT; set @i = 1; while @i <= @N begin select @sql = 'CREATE STATISTICS Customer_FN_LN_' + cast(id as varchar(10)) + ' ON dbo.Customer(CustomerID) WHERE FirstName = ''' + FirstName + ''' AND LastName = ''' + LastName + ''' WITH FULLSCAN, NORECOMPUTE' from #FL where id = @i; exec sp_executesql @sql; set @i = @i + 1; end;
So if we create this filtered stats for those 3 big tables and re-create those stats, lets say, every week, we should be OK with row estimations in the original query.
From my previous experience I usually used approach with temp tables, but in this case it looks like the approach with filtered statistics is more attractive. I haven't use it in production yet though and I am curious what could be the drawbacks.
So my questions are:
- Are there any other approaches to help optimizer deal with highly skewed data distributions?
- What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?