Sloow SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sloow SQL Query

Author

Topic

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2007-08-29 : 11:46:35

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 11:50:32

Are you using SQL Server 2000, or have you moved onto SQL Server 2005?

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

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2007-08-29 : 12:01:56

im still using 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:05:38

[code]SELECT TOP 6 homeTeam.Name + ' v ' + awayTeam.Name as 'Match'
FROM Match AS m
INNER JOIN Squad AS homeSquad ON homeSquad.ID = m.SquadA
INNER JOIN Squad AS awaySquad ON awaySquad.ID = m.SquadB
INNER JOIN Club AS homeTeam ON homeTeam.ID = homeSquad.ClubID
INNER JOIN Club AS awayTeam ON awayTeam.ID = awaySquad.ClubID
WHERE m.Date >= CURRENT_TIMESTAMP
AND EXISTS (
SELECT *
FROM SquadPlayerMapping AS spm
INNER JOIN Squad AS s on s.ID = spm.SquadID
INNER JOIN Club AS c on c.ID = s.ClubID
WHERE spm.PlayerID = 21
AND spm.DateLeft IS NULL
AND spm.SquadID = dbo.pr_bll_GetFirstTeamSquadIDFromClubID(s.ClubID)
AND spm.SquadID IN (m.SquadA, m.SquadB)
)
ORDER BY m.Date[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:10:33

[code]SELECT TOP 6 homeTeam.Name + ' v ' + awayTeam.Name as 'Match'
FROM Match AS m
INNER JOIN Squad AS homeSquad ON homeSquad.ID = m.SquadA
INNER JOIN Squad AS awaySquad ON awaySquad.ID = m.SquadB
INNER JOIN Club AS homeTeam ON homeTeam.ID = homeSquad.ClubID
INNER JOIN Club AS awayTeam ON awayTeam.ID = awaySquad.ClubID
LEFT JOIN SquadPlayerMapping AS spm ON spm.SquadID IN (m.SquadA, m.SquadB)
AND spm.PlayerID = 21
AND spm.DateLeft IS NULL
AND spm.SquadID IN (dbo.pr_bll_GetFirstTeamSquadIDFromClubID(homeSquad.ID), dbo.pr_bll_GetFirstTeamSquadIDFromClubID(awaySquad.ID))
WHERE m.Date >= CURRENT_TIMESTAMP
ORDER BY m.Date[/code]

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

tadhg88
Yak Posting Veteran

62 Posts

Posted - 2007-08-29 : 12:18:44

damn thats fast and perfect i dont know how you do it but im really really grateful thanks a million man your a star
Tim

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-29 : 12:54:14

Very cool Peso, does your brain ever cramp up when you are doing this? Just out of curiosity, when you are debugging this stuff do you build the tables or are you able to just visualize everything? Also, do you have a process for breaking it down?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:59:56

quote:
Originally posted by Zoroaster

does your brain ever cramp up when you are doing this?

Yes

quote:
Originally posted by Zoroaster

Just out of curiosity, when you are debugging this stuff do you build the tables or are you able to just visualize everything?

Visualize. Like RainMan. But mostly I get lucky.

quote:
Originally posted by Zoroaster

Also, do you have a process for breaking it down?

Yes. I can tell you but then I have to kill you, unless you pay me

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 13:02:24

quote:
Originally posted by tadhg88

damn thats fast and perfect i dont know how you do it but im really really grateful thanks a million man your a star
Tim

Thanks for the feedback.

Have you tried them both? Do they return the same records as the original?
Can you post back the time it takes for the original and the times for the two suggestions (as long as they return the same records)?

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-29 : 13:23:35