Slooow SQL Query

  • Hi,

    I am trying to write this query to retrieve the next 6 games a players squad are goin to be playing, i have written the code below and it works except that it takes 42 seconds to run the query, i cant seem to get it to run any faster so if anyone has a ny tips of suggestions i would really appreciate it

    Thanks in advance

    Tim

     

     

    Select TOP 6 homeTeam.Name + ' v ' + awayTeam.Name as 'Match'

    From Match m

    Join Squad homeSquad on homeSquad.ID = m.SquadA

    Join Squad awaySquad on awaySquad.ID = m.SquadB

    Join Club homeTeam on homeTeam.ID = homeSquad.ClubID

    Join Club awayTeam on awayTeam.ID = awaySquad.ClubID

    Where m.Date >= GetDate()

    And ((m.SquadA in (Select SquadID

       From SquadPlayerMapping spm

       Join Squad s on s.ID = spm.SquadID

       Join Club c on c.ID = s.ClubID

       Where spm.PlayerID = 21

       And spm.DateLeft is null

       And spm.SquadID = dbo.pr_bll_GetFirstTeamSquadIDFromClubID(s.ClubID)))

    Or (m.SquadB in (Select SquadID

       From SquadPlayerMapping spm

       Join Squad s on s.ID = spm.SquadID

       Join Club c on c.ID = s.ClubID

       Where spm.PlayerID = 21

       And spm.DateLeft is null

       And spm.SquadID = dbo.pr_bll_GetFirstTeamSquadIDFromClubID(s.ClubID))))

    --Order By m.Date

  • Asked and answered here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88616

     


    N 56°04'39.16"
    E 12°55'05.25"

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply