How to speed up current query with index

Question

I'm using a v12 server in Azure SQL Database, and I have the following table:

CREATE TABLE [dbo].[AudienceNiches]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [WebsiteId] [nvarchar](128) NOT NULL, [VisitorId] [nvarchar](128) NOT NULL, [VisitDate] [datetime] NOT NULL, [Interest] [nvarchar](50) NULL, [Gender] [float] NULL, [AgeFrom18To24] [float] NULL, [AgeFrom25To34] [float] NULL, [AgeFrom45To54] [float] NULL, [AgeFrom55To64] [float] NULL, [AgeFrom65Plus] [float] NULL, [AgeFrom35To44] [float] NULL, CONSTRAINT [PK_AudienceNiches] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )

I'm executing this query: (UPDATED QUERY)

`select  a.interest, count(interest) from (
select visitorid, interest
from audienceNiches
WHERE WebsiteId = @websiteid
AND VisitDate >= @startdate
AND VisitDate <= @enddate
group by visitorid, interest) as a
group by a.interest`

And I have the following indexs (all ASC):

idx_WebsiteId_VisitDate_VisitorId idx_WebsiteId_VisitDate idx_VisitorId idx_Interest

The problem is that my query return 18K rows aproximaly and takes 5 seconds, the whole table has 8.8M records, and if I expand a little the data the time increases a lot, so, what would be the best index to this query? What I'm missing?

Answer 1

The best index for this query is a composite index on these columns, in this order:

• WebsiteId
• VisitDate
• Interest
• VisitorId

This allows the query to be completely answered from the index. SqlServer can range scan on (WebsiteId, VisitDate) and then exclude null Interest and finally count distinct VisitorIds all from the index. The indexes entries will be in the correct order to allow these operations to occur efficiently.