HELP!! Calling all ace developers
-
Greetings Developers!
I have been trying to run a query and all it does is execute it, but the little 'globe' just spins away for hours and nothing gets returned.
I think maybe I haven't optimzed my query that well-I would be VERY GRATEFUL if someone could have a look at it and give their comments or advice
SELECT TOP 15
PlayerID as RecordID,
PlayerName as RecordLabel,
ot.BuyerTeamID as RecordExtraID,
ot.BuyerTeamName as RecordExtraLabel,
Bid as RecordValue,
'HighestTransfer' as Type,
0,
ll.LeagueID
FROM OldTransfers ot WITH(NOLOCK)
INNER JOIN Teams t WITH(NOLOCK) ON t.TeamID = ot.BuyerTeamID
INNER JOIN LeagueLevelUnits llu WITH(NOLOCK) ON llu.LeagueLevelUnitID = t.LeagueLevelUnitID
INNER JOIN LeagueLevels ll WITH(NOLOCK) ON ll.LeagueLevelID = llu.LeagueLevelID
WHERE
ll.LeagueID = 90 And
Bid > 2000000
ORDER BY Bid desc
just a quick note-it should only return one row that contains the LeagueID = 90!
MANY, MANY THANKS
-
Do you have indexes on all those columns??
t.TeamID, ot.BuyerTeamID, llu.LeagueLevelUnitID, t.LeagueLevelUnitID, ll.LeagueLevelID, llu.LeagueLevelID
Can you run this statement? And how fast does it run ? :
Select * from dbo.LeagueLevels where ll.LeagueID = 90
-
Hey RGR'us,
I ran the select statement and the result was immediate.
Yes indexes have been applied to those columns.
any more ideas?
-
Send us the execution plan... there must be a scan somewhere that is grinding this thing down to a stop.
Run this and send us the results.
SET
SHOWPLAN_TEXT ON
GO
--Your query goes here
GO
SET
SHOWPLAN_TEXT OFF
GO
-
Here you go!
|--Top(15)
|--Nested Loops(Inner Join, OUTER REFERENCES
[llu].[LeagueLevelId]))|--Nested Loops(Inner Join, OUTER REFERENCES
[t].[LeagueLevelUnitId]) WITH PREFETCH)| |--Nested Loops(Inner Join, OUTER REFERENCES
[ot].[BuyerTeamID]) WITH PREFETCH)| | |--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[Hattrick_Temp].[dbo].[OldTransfers] AS [ot]) WITH PREFETCH)| | | |--Index Seek(OBJECT
[Hattrick_Temp].[dbo].[OldTransfers].[Bid] AS [ot]), SEEK[ot].[Bid] > 2000000) ORDERED BACKWARD)| | |--Clustered Index Seek(OBJECT
[Hattrick_Temp].[dbo].[Teams].[aaaaaTeams_PK] AS [t]), SEEK[t].[TeamId]=[ot].[BuyerTeamID]) ORDERED FORWARD)| |--Index Seek(OBJECT
[Hattrick_Temp].[dbo].[LeagueLevelUnits].[IXU_LeagueLevelUnitID] AS [llu]), SEEK[llu].[LeagueLevelUnitId]=[t].[LeagueLevelUnitId]) ORDERED FORWARD)|--Index Seek(OBJECT
[Hattrick_Temp].[dbo].[LeagueLevels].[LeagueId] AS [ll]), SEEK[ll].[LeagueId]=90 AND [ll].[LeagueLevelId]=[llu].[LeagueLevelId]) ORDERED FORWARD) -
Seems pretty good except for the bookmark lookup which may or may not be the problem. Can you run the query again in QA and show the graphical execution plan, and tell us what % of the query is used by each operation?
-
and here are the results:
-
We can't see an image unless you post it on a webserver. You can always manually tell us whta operations take the longest.
-
no results Patrick...
until then, I'd recommend that you do one join at a time starting from
SELECT * FROM LeagueLevels LL WHERE LeagueID = 90
and working your way down...
SELECT
* FROM LeagueLevels LL INNER JOIN LeagueLevelUnits LLU ON ll.LeagueLevelID = llu.LeagueLevelID
until you hit the bottleneck
Max
-
How about trying this approach:
SELECT TOP 15
PlayerID as RecordID,
PlayerName as RecordLabel,
ot.BuyerTeamID as RecordExtraID,
ot.BuyerTeamName as RecordExtraLabel,
Bid as RecordValue,
'HighestTransfer' as Type,
0
FROM
OldTransfers ot WITH(NOLOCK)
INNER JOIN
(
SELECT
t.teamId
FROM
LeagueLevels ll WITH(NOLOCK)
INNER JOIN LeagueLevelUnits llu WITH(NOLOCK) ON ll.LeagueLevelID = llu.LeagueLevelID
INNER JOIN Teams t WITH(NOLOCK) ON t.LeagueLevelUnitID = llu.LeagueLevelUnitID
WHERE
ll.LeagueID = 90
) AS x ON x.teamId = ot.BuyerTeamId
WHERE
Bid > 2000000
ORDER BY Bid desc
What I'm doing here is isolating the query to select the teams first and coding this as a subselect (aliased as table "x"). This should select only those teams in the relevant league. Then the join is done on the OldTransfers table ...?
SELECT
t.teamId
FROM
LeagueLevels ll WITH(NOLOCK)
INNER JOIN LeagueLevelUnits llu WITH(NOLOCK) ON ll.LeagueLevelID = llu.LeagueLevelID
INNER JOIN Teams t WITH(NOLOCK) ON t.LeagueLevelUnitID = llu.LeagueLevelUnitID
WHERE
ll.LeagueID = 90
HTH
Warren
-
One of the first things to do when you seem to be getting bad query plans is to force updates of statistics and rowcounts (sp_updatestats and DBCC UPDATEUSAGE). Sometimes this can result in a huge improvement without rewriting any code.
It looks like the query is focusing on the bid amout instead of the league ID, so it is listing the details for all transfers over 2000000 before narrowing down to a specific team. You could eliminate the bookmark lookup by creating a covering index beginning with BuyerTeamID. (Add PlayerID and/or PlayerName if they are coming from the OldTransfers table.) I have no idea what your rowcounts or access patterns are, you'll have to be the final judge of whether this is a good idea.
CREATE INDEX IX_OldTransfers_xxx ON OldTransfers (BuyerTeamID, Bid, BuyerTeamName)
I've read that filters in the WHERE clause are applied after joining all rows, while filters in the ON clause are applied earlier and reduce total I/O. If this is true, you might be able to get a better execution plan by rearranging your query like this:
SELECT
TOP 15 PlayerID as RecordID, PlayerName as RecordLabel, ot.BuyerTeamID as RecordExtraID, ot.BuyerTeamName as RecordExtraLabel,
Bid as RecordValue, 'HighestTransfer' as Type, 0, ll.LeagueID
FROM LeagueLevels ll WITH(NOLOCK)
INNER JOIN LeagueLevelUnits llu WITH(NOLOCK) ON ll.LeagueLevelID = llu.LeagueLevelID AND ll.LeagueID = 90
INNER JOIN Teams t WITH(NOLOCK) ON llu.LeagueLevelUnitID = t.LeagueLevelUnitID
INNER JOIN OldTransfers ot WITH(NOLOCK) ON t.TeamID = ot.BuyerTeamID AND Bid > 2000000
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply