SQL Server – Slow Performance Matching Columns to Lists of Values

azure-sql-databaseperformancequery-performancesql serverstored-procedurestable-valued-parameters

I've made a stored procedure to get all results which match specific students and objectives.

CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY
AS
SELECT Id, UserId, ObjectiveId, DateUploaded, TrafficLight
FROM [Results]
WHERE
  [UserId] IN (SELECT [Id] FROM @students)
  AND [ObjectiveId] IN (SELECT [Id] FROM @objectives)
  AND [TrafficLight] IS NOT NULL
ORDER BY [UserId] ASC, [ObjectiveId] ASC, [DateUploaded] DESC

It uses a user-defined table type for passing in arrays of students and objectives:

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] [int] NULL
)

Typically @students contains ~30 IDs and @objectives contains ~100 IDs. The Results table has about 500,000 rows and a nonclustered index on UserId, ObjectiveId, TrafficLight.

At the moment it's taking a long time (up to 5 seconds) to search 500,000 rows. I'm new to stored procedures and think I must be doing something wrong. How can the performance be improved?

Best Answer

Based on the ORDER BY and the fact that you only care about non-NULL trafficlights, I would consider either:

...ON dbo.Results(UserId, ObjectiveId, DateUploaded DESC, TrafficLight)

Or even adding a filter on that index:

...WHERE TrafficLight IS NOT NULL

Whether the filter makes sense or not depends on what percentage of the table is NULL vs. NOT NULL and how often you run this exact query compared to one that doesn't filter. It may not be intuitive to have TrafficLight in the index, but this is due to some optimizer blindness.

Related Question