0

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?

New contributor
James is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    Try this index: CREATE INDEX idx ON dbo.Results(UserId, ObjectiveId, DateUploaded) INCLUDE (TrafficLight); – Dan Guzman Jan 13 at 15:31
  • @DanGuzman Will do. Just to check, should it be CREATE INDEX idx ON dbo.Results(UserId, ObjectiveId, TrafficLight) INCLUDE (DateUploaded);? – James Jan 13 at 15:40
  • I don't think it matters much as long as DateUploaded is part of the index (key or included). – Dan Guzman Jan 13 at 15:50
  • 1
    Based on the ORDER BY and the fact that you only care about non-NULL trafficlights, I would probably do ON dbo.Results(UserId, ObjectiveId, DateUploaded DESC, TrafficLight) WHERE TrafficLight IS NOT NULL. It may not be intuitive to have TrafficLight in the index, but this is due to some optimizer blindness. – Aaron Bertrand Jan 13 at 18:00
  • Thanks, this has made a big improvement and taught me more about indexes. If you add as an answer then I will accept. – James Jan 14 at 0:05
2

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.

1

you are missing Set Nocount On in proc.

Beside how many rows will be of TrafficLight IS NOT NULL per 100 i.e what is Selectivity of "TrafficLight IS NOT NULL" ?

What is the relation between Student and Objective ? One to one or one to many ?

Table is not normalise .if there is no window function then Why order by in proc ?you can order then at UI level.

CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY
AS
Set NoCount ON

SELECT Id, UserId, ObjectiveId, DateUploaded, TrafficLight
FROM dbo.[Results] R
WHERE Exists(
  (SELECT [Id] FROM @students id=R.[UserId])
  AND exists  (SELECT [Id] FROM @objectives id=R.[ObjectiveId])
  AND [TrafficLight] IS NOT NULL
ORDER BY [UserId] ASC, [ObjectiveId] ASC, [DateUploaded] DESC


 CREATE INDEX idx ON dbo.Results(UserId, ObjectiveId,DateUploaded DateUploaded DESC, TrafficLight) ;

I will not use Filtered index TrafficLight IS NOT NULL so early. What is the use of TrafficLight IS NULL ?

1

Typically, once you pass a handful of entries, the use of IN starts to slow things down by limiting the engine's choices. IN is just syntactic sugar and under the hood SQL is expanding it into a giant list of OR clauses.

Have you tried this variant using inner joins?

SELECT R.ID
    , R.UserId
    , R.ObjectiveId
    , R.DateUploaded
    , R.TrafficLight
FROM [Results] AS R
    INNER JOIN (SELECT DISTINCT ID AS UserID FROM @Students) AS U ON U.UserID = R.UserID
    INNER JOIN (SELECT DISTINCT ID AS ObjectiveID FROM @Objectives) AS O ON O.ObjectiveID = R.ObjectiveID
WHERE R.TrafficLight IS NOT NULL

Your Answer

James is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.