I've got a little problem with how my SQL Server query performs.
This basically looks as follows (It's been generated by Dapper):
SELECT COUNT(*) FROM Data AS D 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:
- Seek predicate on Local
- Seek predicate on DatasetId
- Seek predicate range on CurrencyId (will build a internal values table first)
- Predicate range on ExchangeId
- 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?