Bob Schneider
asked on
Creating Indexes-sql server 2012 web
I have a site that displays nordic ski and cross-country running results and, in times of high volume, the results render slowly. I would like to know how to optimize the query and set indexes. We did this with my fitness events database but now I need to do that with the cc meet database and I am not sharp enough to do it alone. Here is a query to start with:
What do you need to know about the tables in order to help me? Sorry for asking a similar question but I just am having a hard time bridging the two.
sql = "SELECT r.LastName, r.FirstName, t.TeamName, r.RosterID, r.Gender, ir.RaceTime, ir.Excludes, ir.TeamPlace, ir.Bib "
sql = sql & "FROM Roster r INNER JOIN Grades g ON r.RosterID = g.RosterID INNER JOIN IndRslts ir ON r.RosterID = ir.RosterID "
sql = sql & "INNER JOIN Teams t ON t.TeamsID = r.TeamsID WHERE ir.RacesID = " & lRaceID & " AND ir.Place <> 0 AND ir.RaceTime <> '00:00' "
sql = sql & "ORDER BY ir.Place"
What do you need to know about the tables in order to help me? Sorry for asking a similar question but I just am having a hard time bridging the two.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would help if you can post the structure of the tables and respective PK, FK and Indexes. The Query Execution Plan would be useful as well.
At least you need to have indexes created on FK's and on columns used in the WHERE clause as well in the ORDER clause.
Assuming that Place and RaceTime can only have positive values I would change the '<>' to '>', like this:
At least you need to have indexes created on FK's and on columns used in the WHERE clause as well in the ORDER clause.
Assuming that Place and RaceTime can only have positive values I would change the '<>' to '>', like this:
sql = "SELECT r.LastName, r.FirstName, t.TeamName, r.RosterID, r.Gender, ir.RaceTime, ir.Excludes, ir.TeamPlace, ir.Bib "
sql = sql & "FROM Roster r INNER JOIN Grades g ON r.RosterID = g.RosterID INNER JOIN IndRslts ir ON r.RosterID = ir.RosterID "
sql = sql & "INNER JOIN Teams t ON t.TeamsID = r.TeamsID WHERE ir.RacesID = " & lRaceID & " AND ir.Place > 0 AND ir.RaceTime > '00:00' "
sql = sql & "ORDER BY ir.Place"
ASKER
I guess I just need to learn how to create indexes. I have PK-FK constraints in place and it sounds like my query is good so I will go ahead and learn this before posting back.
i do not believe in this situation its about changing the syntax of the query you listed above. It looks pretty tight.
Its about optimizing the tables by creating indexes on the tables.
it doesn't take long at all :)
http://msdn.microsoft.com/en-us/library/ms186342.aspx
http://msdn.microsoft.com/en-us/library/ms190457.aspx
and a great video
https://www.youtube.com/watch?v=ITcOiLSfVJQ