Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Improve Stored Procedure Performance

Status
Not open for further replies.
MrPlough69

MrPlough69

IS-IT--Management
May 17, 2005
39
GB
Hi All,

I’ve got the following stored procedure which returns a list of football players along with some simple stats (running on SQL Server 2005):

Code: 
CREATE PROCEDURE GetTeamLineup
	@UserId INT,
	@FixtureId INT,
	@TeamId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT mt.Surname, 
	COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ),

	( SELECT Count( PlayRef ) 
		FROM Scorers 
		WHERE PlayRef = mt.RefNo 
		AND FixId = MatchIdNo ) AS GoalsScored,

	( SELECT Count( PlayRef )
		FROM Bookings
		WHERE PlayRef = mt.RefNo
		AND FixId = MatchIdNo 
		AND BookingType = 'Y') AS YellowCards,

	( SELECT Count( PlayRef )
		FROM Bookings
		WHERE PlayRef = mt.RefNo
		AND FixId = MatchIdNo 
		AND BookingType IN ( 'YR', 'R' ) ) AS RedCards,

	mt.RefNo
	FROM MATCHmagTeams mt
	INNER JOIN TeamSquads ts ON ts.RefNo = mt.RefNo
	INNER JOIN Teams te ON te.TeamId = mt.TeamId
	LEFT JOIN BlockLineups ubl
		ON ubl.bli_FixtureId = @FixtureId AND ubl.bli_UserId = @UserId
	LEFT JOIN LineupRatings ulr
		ON ulr.lr_BlockLineupId = ubl.bli_UniqId AND ulr.lr_PlayerId = mt.RefNo

	LEFT JOIN BlockLineups tbl
		ON tbl.bli_FixtureId = @FixtureId AND tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )
	LEFT JOIN LineupRatings tlr
		ON tlr.lr_BlockLineupId = tbl.bli_UniqId AND tlr.lr_PlayerId = mt.RefNo

	WHERE MatchIdNo = @FixtureId
	AND te.TeamId = @TeamId
	ORDER BY mt.Surname
END

The query plan doesn’t have any table/index scans, so just wondered if it would be possible to improve the SQL (the above is the best performing that I’ve been able to come up with).

Many thanks in advance.
 
Sort by date Sort by votes
You should just be able to left join to the Scorers and Bookings Table, add a group by and perform a count for the scores and cards (you will have to use a Case statement for the bookings). This will eliminate the score/booking queries being ran for each person.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Upvote 0 Downvote
Thanks Mark,

I now have the following:

Code: 
ALTER PROCEDURE [dbo].[GetTeamLineup]
	@UserId INT,
	@FixtureId INT,
	@TeamId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT mt.Surname, 
	COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ),
	COUNT( sc.PlayRef ) AS GoalsScored,
	SUM(
		CASE bk.BookingType 
			WHEN 'Y' THEN 1
			ELSE 0
		END
	) AS YellowCards,
	SUM(
		CASE bk.BookingType 
			WHEN 'YR' THEN 1
			WHEN 'R' THEN 1
			ELSE 0
		END
	) AS RedCards,
	mt.RefNo
	FROM MATCHmagTeams mt
	INNER JOIN TeamSquads ts ON ts.RefNo = mt.RefNo
	INNER JOIN Teams te ON te.TeamId = mt.TeamId
	LEFT JOIN BlockLineups ubl
		ON ubl.bli_FixtureId = @FixtureId AND ubl.bli_UserId = @UserId
	LEFT JOIN LineupRatings ulr
		ON ulr.lr_BlockLineupId = ubl.bli_UniqId AND ulr.lr_PlayerId = mt.RefNo

	LEFT JOIN BlockLineups tbl
		ON tbl.bli_FixtureId = @FixtureId AND tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )
	LEFT JOIN LineupRatings tlr
		ON tlr.lr_BlockLineupId = tbl.bli_UniqId AND tlr.lr_PlayerId = mt.RefNo

	LEFT JOIN Scorers sc ON sc.PlayRef = mt.RefNo AND sc.FixId = MatchIdNo

	LEFT JOIN Bookings bk ON bk.PlayRef = mt.RefNo AND bk.FixId = MatchIdNo

	WHERE MatchIdNo = @FixtureId
	AND te.TeamId = @TeamId
	GROUP BY mt.Surname, COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ), mt.RefNo
	ORDER BY mt.Surname
END

Is this what you had in mind? To my surprise I only got a 1% improvement over the previous query.

 
Upvote 0 Downvote
>> The query plan doesn’t have any table/index scans

Really???

[tt][blue]
LEFT JOIN BlockLineups tbl
ON tbl.bli_FixtureId = @FixtureId AND tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )[/blue][/tt]

I would think that the part in bold would prevent a seek (and therefore cause a scan.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I've just had a double check George, and I can confirm that there are indeed no scans.

The operations that are taking up the bulk of the time are clustered index seeks on the TeamSquads and LineupRatings tables. I'm a bit confused about these as I thought clustered index seeks should be super-speedy.

 
Upvote 0 Downvote
Here you go Denis

Code: 
  |--Compute Scalar(DEFINE:([Expr1025]=CONVERT_IMPLICIT(int,[Expr1038],0)))
       |--Stream Aggregate(GROUP BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname], [Expr1024], [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) DEFINE:([Expr1038]=COUNT([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]), [Expr1026]=SUM([Expr1030]), [Expr
            |--Sort(ORDER BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname] ASC, [Expr1024] ASC, [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo] ASC))
                 |--Compute Scalar(DEFINE:([Expr1024]=CASE WHEN [MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating] IS NOT NULL THEN CONVERT_IMPLICIT(real(24),[MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating],0) EL
                      |--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1021]))
                           |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings].[_dta_index_Bookings_15_1525580473__K6_K1_3_4_5]), SEEK:([PDMSoccerSQL2000].[dbo].[Bookings].[FixID]=[@fixtureid]) ORDERED FORWARD)
                           |    |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings]), SEEK:([Bmk1021]=[Bmk1021]) LOOKUP ORDERED FORWARD)
                           |--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeam
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1018]))
                                |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers].[IX_Scorers]), SEEK:([PDMSoccerSQL2000].[dbo].[Scorers].[FixID]=[@fixtureid]) ORDERED FORWARD)
                                |    |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers]), SEEK:([Bmk1018]=[Bmk1018]) LOOKUP ORDERED FORWARD)
                                |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [tbl].[bli_UniqId], [Expr1037]) WITH UNORDERED PREFETCH)
                                     |--Nested Loops(Left Outer Join)
                                     |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [ubl].[bli_UniqId], [Expr1036]) WITH UNORDERED PREFETCH)
                                     |    |    |--Nested Loops(Left Outer Join)
                                     |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [Expr1035]) WITH UNORDERED PREFETCH)
                                     |    |    |    |    |--Nested Loops(Inner Join)
                                     |    |    |    |    |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Teams].[PK_Teams]), SEEK:([PDMSoccerSQL2000].[dbo].[Teams].[TeamId]=[@teamid]) ORDERED FORWARD)
                                     |    |    |    |    |    |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[IX_MATCHmagTeams_TeamID]), SEEK:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[TeamID]=[@teamid]),  WHERE:([PDMSoccerSQL2000
                                     |    |    |    |    |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[TeamSquads].[PK_TeamSquads]), SEEK:([PDMSoccerSQL2000].[dbo].[TeamSquads].[RefNo]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED F
                                     |    |    |    |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [ubl]), SEEK:([ubl].[bli_UserId]=[@userid] AND [ubl].[bli_FixtureId]=[@fixtureid]) ORDERED FORWARD)
                                     |    |    |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [ulr]), SEEK:([ulr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [ubl].[bli_UniqId] AN
                                     |    |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [tbl]), SEEK:([tbl].[bli_UserId]=[MyFootballYear].[dbo].[GetUserTemplateUserId]([@userid]) AND [tbl].[bli_FixtureId]=[@fixtureid]) 
                                     |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [tlr]), SEEK:([tlr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [tbl].[bli_UniqId] AND [tlr].[l
 
Upvote 0 Downvote
Is this what you had in mind? To my surprise I only got a 1% improvement over the previous query.
Click to expand...
Yes, it is. How many records are in these tables?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Upvote 0 Downvote
Can you please explain why BlockLineups & LineupRatings appear in the query twice?

The only difference I see is....

ubl.bli_UserId = @UserId

vs.

tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )

You may be better off linking to these tables just once with on OR condition on the join, like this...

Code: 
[COLOR=blue]ALTER[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[GetTeamLineup]
    @UserId [COLOR=blue]INT[/color],
    @FixtureId [COLOR=blue]INT[/color],
    @TeamId [COLOR=blue]INT[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
    [COLOR=green]-- SET NOCOUNT ON added to prevent extra result sets from
[/color]    [COLOR=green]-- interfering with SELECT statements.
[/color]    [COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color];

    [COLOR=blue]SELECT[/color] mt.Surname,
    [COLOR=#FF00FF]COALESCE[/color]( ulr.lr_Rating, mt.MatchRating ),
    [COLOR=#FF00FF]COUNT[/color]( sc.PlayRef ) [COLOR=blue]AS[/color] GoalsScored,
    SUM(
        [COLOR=blue]CASE[/color] bk.BookingType
            [COLOR=blue]WHEN[/color] [COLOR=red]'Y'[/color] [COLOR=blue]THEN[/color] 1
            [COLOR=blue]ELSE[/color] 0
        [COLOR=blue]END[/color]
    ) [COLOR=blue]AS[/color] YellowCards,
    SUM(
        [COLOR=blue]CASE[/color] bk.BookingType
            [COLOR=blue]WHEN[/color] [COLOR=red]'YR'[/color] [COLOR=blue]THEN[/color] 1
            [COLOR=blue]WHEN[/color] [COLOR=red]'R'[/color] [COLOR=blue]THEN[/color] 1
            [COLOR=blue]ELSE[/color] 0
        [COLOR=blue]END[/color]
    ) [COLOR=blue]AS[/color] RedCards,
    mt.RefNo
    [COLOR=blue]FROM[/color] MATCHmagTeams mt
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] TeamSquads [COLOR=blue]ts[/color] [COLOR=blue]ON[/color] [COLOR=blue]ts[/color].RefNo = mt.RefNo
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] Teams te [COLOR=blue]ON[/color] te.TeamId = mt.TeamId
    [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] BlockLineups ubl
        [COLOR=blue]ON[/color]  ubl.bli_FixtureId = @FixtureId 
        AND [!](ubl.bli_UserId = @UserId
             Or ubl.bli_UserId = dbo.GetUserTemplateUserId( @UserId ))[/!]

    [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] LineupRatings ulr
        [COLOR=blue]ON[/color] ulr.lr_BlockLineupId = ubl.bli_UniqId AND ulr.lr_PlayerId = mt.RefNo

    [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Scorers sc [COLOR=blue]ON[/color] sc.PlayRef = mt.RefNo AND sc.FixId = MatchIdNo

    [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Bookings bk [COLOR=blue]ON[/color] bk.PlayRef = mt.RefNo AND bk.FixId = MatchIdNo

    [COLOR=blue]WHERE[/color] MatchIdNo = @FixtureId
    AND te.TeamId = @TeamId
    [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] mt.Surname, [COLOR=#FF00FF]COALESCE[/color]( ulr.lr_Rating, mt.MatchRating ), mt.RefNo
    [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] mt.Surname
[COLOR=blue]END[/color]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George,

The players are given a rating initially by our general data inputters who watch the match, the results are then used in another application where a special user further refines the ratings, finally standard users get to set their own ratings.

I join on the tables twice so that I can use the COALESCE to set the order of preference that the ratings are used ( user overrides special user, which in turn overrides the general data inputters ).


Mark,

The MatchMagTeams table has 829k rows,
TeamSquads 61k rows,
Teams 2k rows,
BlockLineups 5k rows,
LineupRatings 133k rows,
Scorers 86k rows,
Bookings 100k rows.

Denis,

Sorry about that, my second attempt:

Code: 
StmtText

GetTeamLineup @FixtureId = 318876, @UserId = 36, @TeamId = 11

CREATE PROCEDURE [dbo].[GetTeamLineup]
	@UserId INT,
	@FixtureId,
	@TeamId
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
  	SELECT mt.Surname, 
	COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ),
	COUNT( sc.PlayRef ) AS GoalsScored,
	SUM(
		CASE bk.BookingType 
			WHEN 'Y' THEN 1
			ELSE 0
		END
	) AS YellowCards,
	SUM(
		CASE bk.BookingType 
			WHEN 'YR' THEN 1
			WHEN 'R' THEN 1
			ELSE 0
		END
	) AS RedCards,
	mt.RefNo
	FROM MATCHmagTeams mt
	INNER JOIN TeamSquads ts ON ts.RefNo = mt.RefNo
	INNER JOIN Teams te ON te.TeamId = mt.TeamId
	LEFT JOIN BlockLineups ubl
		ON ubl.bli_FixtureId = @FixtureId AND ubl.bli_UserId = @UserId
	LEFT JOIN LineupRatings ulr
		ON ulr.lr_BlockLineupId = ubl.bli_UniqId AND ulr.lr_PlayerId = mt.RefNo

	LEFT JOIN BlockLineups tbl
		ON tbl.bli_FixtureId = @FixtureId AND tbl.bli_UserId = dbo.GetUserTemplateUserId( @UserId )
	LEFT JOIN LineupRatings tlr
		ON tlr.lr_BlockLineupId = tbl.bli_UniqId AND tlr.lr_PlayerId = mt.RefNo

	LEFT JOIN Scorers sc ON sc.PlayRef = mt.RefNo AND sc.FixId = MatchIdNo

	LEFT JOIN Bookings bk ON bk.PlayRef = mt.RefNo AND bk.FixId = MatchIdNo

	WHERE MatchIdNo = @FixtureId
	AND te.TeamId = @TeamId
	GROUP BY mt.Surname, COALESCE( ulr.lr_Rating, tlr.lr_Rating, mt.MatchRating ), mt.RefNo
	ORDER BY mt.Surname

(3 row(s) affected)

StmtText
       |--Compute Scalar(DEFINE:([Expr1025]=CONVERT_IMPLICIT(int,[Expr1038],0)))
            |--Stream Aggregate(GROUP BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname], [Expr1024], [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) DEFINE:([Expr1038]=COUNT([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]), [Expr1026]=SUM([Expr1030]), [Expr1027]=SUM([Expr1031])))
                 |--Sort(ORDER BY:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[Surname] ASC, [Expr1024] ASC, [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo] ASC))
                      |--Compute Scalar(DEFINE:([Expr1024]=CASE WHEN [MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating] IS NOT NULL THEN CONVERT_IMPLICIT(real(24),[MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [ulr].[lr_Rating],0) ELSE CASE WHEN [MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [tlr].[lr_Rating] IS NOT NULL THEN CONVERT_IMPLICIT(real(24),[MyFootballYear].[dbo].[LineupRatings].[lr_Rating] as [tlr].[lr_Rating],0) ELSE [PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[MATCHrating] END END, [Expr1030]=CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='Y' THEN (1) ELSE (0) END, [Expr1031]=CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='YR' THEN (1) ELSE CASE WHEN [PDMSoccerSQL2000].[dbo].[Bookings].[BookingType]='R' THEN (1) ELSE (0) END END))
                           |--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Bookings].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1021]))
                                |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings].[_dta_index_Bookings_15_1525580473__K6_K1_3_4_5]), SEEK:([PDMSoccerSQL2000].[dbo].[Bookings].[FixID]=[@FixtureId]) ORDERED FORWARD)
                                |    |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Bookings]), SEEK:([Bmk1021]=[Bmk1021]) LOOKUP ORDERED FORWARD)
                                |--Hash Match(Right Outer Join, HASH:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef])=([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]), RESIDUAL:([PDMSoccerSQL2000].[dbo].[Scorers].[PlayRef]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1018]))
                                     |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers].[IX_Scorers]), SEEK:([PDMSoccerSQL2000].[dbo].[Scorers].[FixID]=[@FixtureId]) ORDERED FORWARD)
                                     |    |--RID Lookup(OBJECT:([PDMSoccerSQL2000].[dbo].[Scorers]), SEEK:([Bmk1018]=[Bmk1018]) LOOKUP ORDERED FORWARD)
                                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [tbl].[bli_UniqId], [Expr1037]) WITH UNORDERED PREFETCH)
                                          |--Nested Loops(Left Outer Join)
                                          |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [ubl].[bli_UniqId], [Expr1036]) WITH UNORDERED PREFETCH)
                                          |    |    |--Nested Loops(Left Outer Join)
                                          |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo], [Expr1035]) WITH UNORDERED PREFETCH)
                                          |    |    |    |    |--Nested Loops(Inner Join)
                                          |    |    |    |    |    |--Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[Teams].[PK_Teams]), SEEK:([PDMSoccerSQL2000].[dbo].[Teams].[TeamId]=[@TeamId]) ORDERED FORWARD)
                                          |    |    |    |    |    |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[IX_MATCHmagTeams_TeamID]), SEEK:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[TeamID]=[@TeamId]),  WHERE:([PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[MatchIDno]=[@FixtureId]) ORDERED FORWARD)
                                          |    |    |    |    |--Clustered Index Seek(OBJECT:([PDMSoccerSQL2000].[dbo].[TeamSquads].[PK_TeamSquads]), SEEK:([PDMSoccerSQL2000].[dbo].[TeamSquads].[RefNo]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)
                                          |    |    |    |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [ubl]), SEEK:([ubl].[bli_UserId]=[@UserId] AND [ubl].[bli_FixtureId]=[@FixtureId]) ORDERED FORWARD)
                                          |    |    |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [ulr]), SEEK:([ulr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [ubl].[bli_UniqId] AND [ulr].[lr_PlayerId]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)
                                          |    |--Index Seek(OBJECT:([MyFootballYear].[dbo].[BlockLineups].[IX_BlockLineups_3] AS [tbl]), SEEK:([tbl].[bli_UserId]=[MyFootballYear].[dbo].[GetUserTemplateUserId]([@UserId]) AND [tbl].[bli_FixtureId]=[@FixtureId]) ORDERED FORWARD)
                                          |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[LineupRatings].[IX_LineupRatings_1] AS [tlr]), SEEK:([tlr].[lr_BlockLineupId]=[MyFootballYear].[dbo].[BlockLineups].[bli_UniqId] as [tbl].[bli_UniqId] AND [tlr].[lr_PlayerId]=[PDMSoccerSQL2000].[dbo].[MATCHmagTeams].[RefNo]) ORDERED FORWARD)

(25 row(s) affected)

StmtText
    UDF: GetUserTemplateUserId

CREATE FUNCTION [dbo].[GetUserTemplateUserId]
(
	@UserId INT
)
RETURNS INT
AS
BEGIN
	-- Get the user's selected team id
	-- Work out the template user id for the user
	DECLARE @TemplateUserId INT
	SET @TemplateUserId = (
		SELECT tu.us_UniqId FROM Users us
		INNER JOIN Users tu ON tu.us_SelectedTeamId = us.us_SelectedTeamId
		WHERE us.us_UniqId = @UserId
		AND tu.us_TemplateUser = 1
	)

(2 row(s) affected)

StmtText
                 |--Compute Scalar(DEFINE:([Expr1007]=[Expr1007]))
                      |--Nested Loops(Left Outer Join)
                           |--Constant Scan
                           |--Assert(WHERE:(CASE WHEN [Expr1006]>(1) THEN (0) ELSE NULL END))
                                |--Stream Aggregate(DEFINE:([Expr1006]=Count(*), [Expr1007]=ANY([MyFootballYear].[dbo].[Users].[us_UniqId] as [tu].[us_UniqId])))
                                     |--Nested Loops(Inner Join, WHERE:([MyFootballYear].[dbo].[Users].[us_SelectedTeamId] as [us].[us_SelectedTeamId]=[MyFootballYear].[dbo].[Users].[us_SelectedTeamId] as [tu].[us_SelectedTeamId]))
                                          |--Clustered Index Seek(OBJECT:([MyFootballYear].[dbo].[Users].[PK_Users] AS [us]), SEEK:([us].[us_UniqId]=[@UserId]) ORDERED FORWARD)
                                          |--Clustered Index Scan(OBJECT:([MyFootballYear].[dbo].[Users].[PK_Users] AS [tu]), WHERE:([MyFootballYear].[dbo].[Users].[us_TemplateUser] as [tu].[us_TemplateUser]=(1)))

(8 row(s) affected)

StmtText
      	RETURN @TemplateUserId

(1 row(s) affected)
 
Upvote 0 Downvote
Thanks for the suggestions all.

The problem was caused by a poorly placed clustered index (set to a column with few unique values).
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
337
pjw001
pjw001
Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
192
Andrzejek
Andrzejek
Auguy
  • Locked
  • Question
Passing TVP instead of Individual Parameters to Procedure
Replies
0
Views
86
Auguy
Auguy
ahmedsa2018
  • Locked
  • Question
Are this query below have problem on performance issue ?
Replies
1
Views
75
ahmedsa2018
ahmedsa2018
ahmedsa2018
  • Locked
  • Question
how to enhance this query for good performance ?
Replies
2
Views
71
johnherman
johnherman

Part and Inventory Search

Sponsor

Back
Top