Results 1 to 3 of 3

Thread: Optimize Query

  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Optimize Query

    Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?

    Thanks!

    Code:
    	Set rs=Server.CreateObject("ADODB.Recordset")
    	sql = "SELECT td.TeamID, td.TeamName, rt.PartID, rt.Effort, rt.UnitMeas, pd.MinMilesConv "
    	sql = sql & "FROM TeamData td INNER JOIN PartData pd ON td.TeamID = pd.TeamID "
    	sql = sql & "JOIN RunTrng rt ON pd.PartID = rt.PartID "
    	sql = sql & "WHERE rt.TrngDate >= '" & Session("beg_date") & "' AND rt.TrngDate < '" & Session("end_date") 
    	sql = sql & "' AND pd.Archive = 'N' AND pd.Gender = '" & sGender & "' AND pd.Grade >= " & iMinGrade
    	sql = sql & " AND pd.Grade <= " & iMaxGrade & " ORDER BY td.TeamID"
    	rs.Open sql, conn, 1, 2

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Optimize Query

    this should run faster
    because it does a Sub Select that retrieves only the
    RunTrng/PartData that you need to join to the TeamData table


    SELECT Team.TeamID, Team.TeamName, PartRun.PartID, PartRun.Effort, PartRun.UnitMeas, PartRun.MinMilesConv
    FROM TeamData Team
    INNER JOIN (SELECT Part.TeamID, Part.MinMilesConv, Run.PartID, Run.Effort, Run.UnitMeas,
    FROM PartData Part
    INNER JOIN RunTrng Run ON Part.PartID = Run.PartID
    WHERE Run.TrngDate >= @StartDate AND
    Run.TrngDate < @EndDate AND
    Part.Archive = 'N' AND
    Part.Gender = @Gender AND
    Part.Grade >= @MinGrade AND
    Part.Grade <= @MaxGrade) PartRun
    ON Team.TeamID = PartRun.TeamID
    ORDER BY Team.TeamID

  3. #3
    Join Date
    Apr 2003
    Posts
    114
    Thanks! I will give it a shot!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •