August 29, 2007 at 9:33 am
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
August 29, 2007 at 11:40 am
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