Use SQL Materialized view to eliminate DISTINCT and speed up response time

Question: Users are complaining this query is slow and is creating a bottleneck. The query includes a subquery  which relies on the  DISTINCT keyword.  The Execution Plan indicates the  highest cost is related to the DISTINCT .    How can I speed up the query response time?

--SQL Server parse and compile time: 
-- CPU time = 31 ms, elapsed time = 116 ms.
--SQL Server Execution Times:
--CPU time = 29500 ms,  elapsed time = 36388 ms.
--SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.


SELECT     NumbersCodeId,              sStatsDescription,              sStatsDescriptionCode,        
      NumbersRoutineId,              NumbersTypeId 
 FROM       NumbersCode  
WHERE     
 NumbersTypeId = 2  AND NumbersCodeId IN (SELECT distinct  NumbersCodeID FROM NumberStatistics  INNER JOIN NumberObject ON NumberObject.lNumberObjectId = Number.lNumberObjectId )  

Answer:   Looking at the plan , the issues are : a) the DISTINCT keyword b) potential normalisation problem.

The normalisation problem requires an analysis of the data model and how it is queried. I’m assuming you don’t have an immediate influence on the table design .

One option to improve the speed is to use a MATERIALIZED VIEW , which is basically a View with computed and stored date. An example of computed and stored data is a Clustered Index

Based on the initial query , the example below – creates a VIEW , eliminating the  need for the DISTINCT keyword . A CLUSTERED INDEX is added to the view.

A revised query continues using the subquery , but this time includes a SELECT on the Materialized View. Compare the Excecution Times

 The solution is useful to make you think differently about  improving response time.  It has a management overhead – as the method may be different from how the other database objects are managed

As the DBA , the data model and queries are often in Production , before you’re called in to troubleshoot a bottleneck.

--Potential Fix –
--SQL Server parse and compile time: 
-- CPU time = 0 ms, elapsed time = 3 ms.
--SQL Server Execution Times:
-- CPU time = 0 ms,  elapsed time = 17 ms.
--SQL Server parse and compile time: 
-- CPU time = 0 ms, elapsed time = 0 ms.


ALTER VIEW vNumbersCodeID
WITH SCHEMABINDING AS
select  NumbersCodeID, COUNT_BIG(*) as countbig from NumberStatistics  inner join NumberObject on NumberObject.lNumberObjectId = Number.lNumberObjectId
GROUP BY lObjectStatsCodeID

GO


CREATE UNIQUE CLUSTERED INDEX [idx] ON [dbo].[idxNumbersCodeID] 
(
                [NumbersCodeID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


----Change query to : 


SELECT     NumbersCodeId,              sStatsDescription,              sStatsDescriptionCode,        
      NumbersRoutineId,              NumbersTypeId 
 FROM       NumbersCode  

WHERE     
 NumbersTypeId = 2  AND NumbersCodeId IN (select NumbersCodeID from vNumbersCodeID WITH (NOEXPAND))  

 Read More

SQL Server - Query Response Time measure - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question