I've got a little problem with how my SQL Server query performs.

This basically looks as follows (It's been generated by Dapper):

INNER JOIN #Permission AS P
    ON P.CategoryId = D.CategoryId
    AND P.IndustryCode = D.IndustryCode
    AND P.GeographyId = D.GeographyId
WHERE D.Local = 0
AND D.DatasetId = @DatasetId
AND D.CurrencyId IN (@Currency0, @Currency1, @Currency2)
AND D.ExchangeId IN (@Exchange0, @Exchange1, @Exchange2);

Table has tens of millions of records and my counts seem to be too slow, taking like 15 seconds to execute, which is far too much. My temporary table contains about 100k rows. But this might vary due to user input.

This is the execution plan: https://www.brentozar.com/pastetheplan/?id=ryXwfmp8m

Table has the following relevant indexes:

CREATE NONCLUSTERED INDEX IX_Data_Test ON dbo.Data (Local, DatasetId, CurrencyId, ExchangeId) INCLUDE (CategoryId, IndustryCode, GeographyId)
WHERE Local = 0;

CREATE NONCLUSTERED INDEX IX_Data_Another ON dbo.Data (CategoryId, IndustryCode, GeographyId, Local, DatasetId, CurrencyId, ExchangeId)
WHERE Local = 0;

Both indexes seem to be useful, but SQL Server will ignore the second one and it builds query that will do the following:

  1. Seek predicate on Local
  2. Seek predicate on DatasetId
  3. Seek predicate range on CurrencyId (will build a internal values table first)
  4. Predicate range on ExchangeId
  5. Then do a HASH MATCH with #Permission table

What could be general recommendations to speed performance up of such a query? I have not yet tried dropping indexes and keeping the ones that have (CategoryId, IndustryCode, GeographyId) as first keys, but being able to filter these rows first would help a lot.

Also, I dislike IN clause that causes seek predicate ranges. Is it a good idea to rewrite query to run query per @Currency and per @Exchange and then SUM() their counts?

  • 1
    Could you post the actual execution plans? – George.Palacios Aug 23 '18 at 16:47
  • Will be done tomorrow morning. Have no ability to do it now. Thank you. – Evaldas Buinauskas Aug 23 '18 at 16:51
  • You may want to add indexes to Temp tables as well. – Unkush Aug 23 '18 at 16:55
  • Note that, if it's possible for overlap between your @Currency and @Exchange lists, a sum of the counts for each might results in some entries being counted twice. – RDFozz Aug 23 '18 at 19:12
  • @RDFozz good spot, but I kinda think that with union all I would be able to run correct count. Speaking of indexes, they include just the columns that are needed for count query, so there are no lookups made to the actual table. – Evaldas Buinauskas Aug 23 '18 at 20:15

Your Answer

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

Browse other questions tagged or ask your own question.