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 )
@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?