asked on

Optimizing a Query

Here is a SQL Server 2012 query. Is there a way that it could be re-structured to perform better?

SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.ChipTime, ir.FnlTime, ir.ChipStart, p.City, p.St
FROM Participant p JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID
JOIN PartRace pr ON pr.RaceID = ir.RaceID AND pr.ParticipantID = p.ParticipantID
WHERE ir.RaceID = " & lRaceID & " AND ir.FnlTime IS NOT NULL AND ir.FnlTime > '00:00:00.000' AND p.Gender = '" & sGender & "' ORDER BY " & sOrderBy

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Peter Chan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

I have the clustered indexes. I don't think I can get around the order by issue because of the different views that people request. Is the structure of the query optimal? Would changing the order of the fields and/or the joins matter at all?

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

How could I make the sort on the client side?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

VItor, yeah I am using a simple html table to display the data. Ultimately I should incorporate jquery but for now...

Thanks for all of the help. And, yes I do have clustered indexes on those table fields so it is probably as good as it can get... We will be timing roughly 3000 runners on July 4th and they will all be looking for their results via results emails that we send upon finishing so I am just trying to avoid a web crash. We use SQL Server 2012 Web on our server.