I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records comes down to 145,878,852. The number of distinct records are 107,311,357.

select count(*) 
from [BroadcastOpens] with (nolock) 
where [OpenTimeUtc] >= @StartDate 
and  [OpenTimeUtc] <@EndDate

The most time consuming process is SELECT DISTINCT..., which took about 40 minutes to run.

SELECT DISTINCT [SubscriberId],[OpenTimeUtc]  
FROM [BroadcastOpens] WITH (nolock) 
WHERE [OpenTimeUtc] >= @StartDate 
AND [OpenTimeUtc] <@EndDate

I have created two indexes on this table for both columns BroadcastId and OpenTimeUtc. They helped to speed up the process, but seem to be less significant.

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_BroadcastId_Temp] 
ON [dbo].[BroadcastOpens]([BroadcastId])

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_OpenTimeUtc_Temp] 
ON [dbo].[BroadcastOpens]([OpenTimeUtc])

As suggested by Martin Smith, I also have created an indexed view on Table BroadcastOpens grouped by [SubscriberId],[OpenTimeUtc],[BroadcastId].

CREATE VIEW dbo.vwBroadcastOpensRecords

SELECT [SubscriberId],[OpenTimeUtc],[BroadcastId], COUNT_BIG(*) as tmp    
from [dbo].[BroadcastOpens] group by [SubscriberId],[OpenTimeUtc],   [BroadcastId]  

vwBroadcastOpensRecords(SubscriberId, OpenTimeUtc,BroadcastId);

This is the step in the query that causes problem:

Image of Execution Plan

I am now testing both approaches to see which one yields better performances.

  1. No changes to the existing tsql query, no non clustered indexed created. Running on production server.
    enter image description here

  2. No changes to the existing tsql query, created two non clustered indexes on the table. Running on staging server.
    enter image description here

  3. Create an indexed view, and modified existing tsql query to use this new indexed view to replace table ... testing... (I stopped the execution of the query as it was running for 28 minutes already... I suspect there is something wrong with my indexed view...)
    enter image description here

Just wondering: The table BroadcastOpens gets constantly updated, does this have an impact on the indexed view that I created?

Any suggestions on how to improve the performance of this query are appreciated.

Thank you HABO and Martin Smith for your invaluable help!

  • 1
    The noonclustered index seems totally irrelevant to the query. – ypercubeᵀᴹ Feb 16 '15 at 21:37
  • If you're working on a report, do you really want to be reading uncommitted data? – Mark Sinkinson Feb 16 '15 at 22:27
  • Hi ypercube, thank you very much for your comments. Could you elaborate on that? – Yevgraf Andreyevich Zhivago Feb 17 '15 at 20:56
  • I was referring to the (BroadcastId) index. Neither of your queries uses the BroadcastId column so I wondered why you expected this index to be helpful (for these 2 queries.) – ypercubeᵀᴹ Feb 17 '15 at 21:53
  • Right, the reason that I need to include BroadcastId in the index is BroadcastId is used in a join logic with the select distinct query. – Yevgraf Andreyevich Zhivago Feb 17 '15 at 22:01

If you want this query gangsta fast create an indexed view that groups on [OpenTimeUtc], [SubscriberId]. That view will serve the date filter perfectly and it will pre-calculate the distinct as well. The query will result in a plan that does an index range scan on the view. Not possible to do any better than that.

| improve this answer | |
  • I have already implemented nonclustered indexes on BroadcastOpens table for both [BroadcastId] and [OpenTimeUtc] columns. Do you consider that this new index view will speed up the process even further? – Yevgraf Andreyevich Zhivago Feb 16 '15 at 23:26
  • The Table BroadcastOpens table gets constantly updated, does this cause concern to the indexed view I created? – Yevgraf Andreyevich Zhivago Feb 16 '15 at 23:28
  • Hi usr, thank you very much for all your help. As you suggested, I have created an index view and I am testing its performances now. Cheers – Yevgraf Andreyevich Zhivago Feb 17 '15 at 2:05
  • The indexed view precomputes more than an index can (I think it is clear why, right?). Since it does everything your query does it has literally unbeatable performance.; Yes, write amplification is a concern. Understand how much more writes will be made and measure the impact. You can understand thy by looking at a DML exec plan. – usr Feb 17 '15 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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