I would like to optimize this sql statement. It is a fitness events results page and I am getting larger and larger races so I want to ensure that it doesn't crash (like happened last year during a large race) as folks went on the site to view their results. Specifically I want to know if the sql looks well constructed, where the indexes should be, and anything else that would make it likely to absorb a high number of requests. I think that the asp portion of the page is in pretty good shape.
Here is the sql statement:
SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St FROM Participant p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID WHERE ir.RaceID = 695 AND ir.FnlTime IS NOT NULL AND ir.EventPl >= 1 AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl
Here is what Enterprise Manager did to the sql:
SELECT TOP (100) PERCENT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM dbo.Participant AS p INNER JOIN
dbo.IndResults AS ir ON p.ParticipantID = ir.ParticipantID INNER JOIN
dbo.PartRace AS pr ON pr.ParticipantID = p.ParticipantID INNER JOIN
dbo.RaceData AS rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID
WHERE (ir.RaceID = 695) AND (ir.FnlTime IS NOT NULL) AND (ir.EventPl >= 1) AND (ir.FnlTime <> '00:00:00.000')
ORDER BY ir.EventPl
Would using this speed up the result or are there other issues that I could address? What is the best way to show you some data, if that would help.