June 10, 2013 at 7:30 am
Hi Friends,
In a new assignment as DBA, I am trying to make few codes better and have tasted some success, thanks to all the learnings from this forum. However, there is a stored procedure written in a pretty way but somehow I get very bad performance from this and was just trying to understand how it actually runs inside.
You can find the procedure in the attachment. This somehow makes me thing that it is just like nested CTEs without using the word CTE anywhere and with CTEs, I never had pretty good experience when dealing with large data sets.
Kindly recommend me that from development point of view, how this can be arranged in a meaningful way so that I can work with the dev team to make it look better.
I work in non US working hours, so kindly bear with me in replying.
PS: I have not inlcluded any table schema details or execution plan as of now.
Regards
Chandan
June 10, 2013 at 9:50 am
THe problem you may have is with all the Nested Derived tables.
How many records are being populated into the TEMP tabe as im not sure you need the compound clustered index and a Non-clustered covering index may be more beneficial.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 10, 2013 at 10:35 am
Jason-299789 (6/10/2013)
THe problem you may have is with all the Nested Derived tables.How many records are being populated into the TEMP table as i m not sure you need the compound clustered index and a Non-clustered covering index may be more beneficial.
Thanks for replying.You are correct when you mention the derived tables. Looks same like CTEs to me unless they work the other way.
I think if it is working as CTEs as I have observed , then some sort of recursion is happening.
I would try creating one covering index on temp table as it contains a million records but still to me, the run time of 60 minutes looks too much.:w00t:
Regards
Chandan
June 11, 2013 at 1:21 am
Be careful with CTE's they arnt always the best way to go, but try them to see what happens.
You might also get an improved performance by switching the CTE's so that they load Temporary tables, but it would need testing to find the optimal solution.
Let us know the solution come up with.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 11, 2013 at 1:28 am
Jason-299789 (6/11/2013)
Be careful with CTE's they arnt always the best way to go, but try them to see what happens.You might also get an improved performance by switching the CTE's so that they load Temporary tables, but it would need testing to find the optimal solution.
Let us know the solution come up with.
Yes, you are bang on. I am not very comfortable with CTEs when dealing with large sets of data. So the first thing I am in process of doing is to get rid of derived tables and substitute with temp table. It will be redoing some stuff but that will give me some comparison on I\O statistics.
I will update my progress in this thread.
Regards
Chandan
June 11, 2013 at 2:14 am
Substitute each reference to the temp table #records with the actual query used to generate the temp table, then eliminate unnecessary table references. This takes you back to a "raw" query which is your starting point for a new, faster query. Keep a copy of this as your baseline. Study the Actual Plan until you are familiar with it and make a note of any obvious issues such as table scans, hash joins. Clarify how the tables relate to each other and if any of those table references are views. If they are, look at subbing them out too.
Then rewrite the query from scratch. Give yourself a guide time to completion, say two hours. Ignore the code of the original query, the only aspect of it that you are interested in now is the result set, as a reference to check that your new query is generating the correct results.
-- Identify the core query (pretty much everything else is LEFT JOINed to this)
SELECT
r.wagerId,
r.tournamentId,
r.contestId,
r.playerId,
r.partnerId,
w.dtCompleted,
w.gameId,
-1 AS gameTypeId,
currency,
ISNULL(SUM(w.amount),0) AS amount
FROM PlayerMatchedStakesSummary w
JOIN Game g ON w.gameId = g.gameId
JOIN #records r ON w.playerId = r.playerId
AND w.tournamentId = r.tournamentId
AND w.wagerId = r.wagerId
AND w.contestId = r.contestId
WHERE g.gamePlayTypeId <> 8
GROUP BY r.wagerId, r.tournamentId, r.contestId, r.playerId, r.partnerId, w.gameId, w.dtCompleted, currency
-- substitute table reference '#records' with the query which generates the table:
SELECT
r.wagerId,
r.tournamentId,
r.contestId,
r.playerId,
r.partnerId,
w.dtCompleted,
w.gameId,
-1 AS gameTypeId,
currency,
ISNULL(SUM(w.amount),0) AS amount
FROM PlayerMatchedStakesSummary w
INNER JOIN Game g ON w.gameId = g.gameId
INNER JOIN (
SELECT
pms.playerId,
pms.partnerId,
pms.tournamentId,
pms.wagerId,
pms.contestId,
pms.dtCompleted
FROM PlayerMatchedStakesSummary pms
INNER JOIN PartnerPlayer pp1 ON pms.playerId = pp1.playerId
WHERE pms.dtCompleted BETWEEN @from AND @to
AND pms.partnerId = @partnerId
) r ON w.playerId = r.playerId
AND w.tournamentId = r.tournamentId
AND w.wagerId = r.wagerId
AND w.contestId = r.contestId
WHERE g.gamePlayTypeId <> 8
GROUP BY r.wagerId, r.tournamentId, r.contestId, r.playerId, r.partnerId, w.gameId, w.dtCompleted, currency
-- reorganise query to eliminate unnecessary references
SELECT
r.wagerId,
r.tournamentId,
r.contestId,
r.playerId,
r.partnerId,
w.dtCompleted,
w.gameId,
-1 AS gameTypeId,
currency,
ISNULL(SUM(w.amount),0) AS amount
FROM PlayerMatchedStakesSummary w
INNER JOIN Game g
ON w.gameId = g.gameId
INNER JOIN PartnerPlayer pp1
ON pp1.playerId = w.playerId
WHERE g.gamePlayTypeId <> 8
AND w.dtCompleted BETWEEN @from AND @to
AND w.partnerId = @partnerId
GROUP BY r.wagerId, r.tournamentId, r.contestId, r.playerId, r.partnerId, w.gameId, w.dtCompleted, currency
-- check the results
-- check the results again
-- check the results one last time
-- continue with remaining references to temp table '#records'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply