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
Last activity: Jan 14, 2019, 06:38 PM