Sample Header Ad - 728x90

Slow performance matching columns to lists of values

0 votes
3 answers
147 views
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?
Asked by James (103 rep)
Jan 13, 2019, 02:32 PM
Last activity: Jan 14, 2019, 06:38 PM