| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-08-11 : 08:24:05
|
A trigger I got some help with here generates the following execution-plan and I'm suspecting that this table spool/eager spool has a massive performance blow: The plan is generated by the following statement: UPDATE table2 SET Volume = dt1.Volume, Volume_euro = dt1.Volume_euro, Stoptime = dt1.Stoptime FROM ( SELECT a.UserID, a.MarketID, a.CompID, a.Request, a.Price, SUM(Volume), SUM(Volume) * @Rate, Stoptime FROM table1 a WHERE a.UserID = @UserID AND a.MarketID = @MarketID GROUP BY a.UserID, a.MarketID, a.CompID, a.Request, a.Price) AS dt1 INNER JOIN table2 c ON dt1.UserID = c.UserID AND dt1.UserID = c.UserID AND dt1.MarketID = c.MarketID AND dt1.CompID = c.CompID AND dt1.Price = c.Price AND dt1.Request = c.Request WHERE dt1.UserID = @UserID AND dt1.MarketID = @MarketID It's basically one table that updates the identical fields in another table with some aggregates and some conditions. This is my exact query, I have not changed it one bit (except for fieldnames/tablenames). Is there anything I can do to make this more efficient?? It is a trigger and is to be run quite a few times so it need to be as fast as possible...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 04:21:42
|
Nobody...?  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-12 : 06:29:22
|
| I don't see the point of including the 'user' table in the subquery....it serves no point given that none of it's fields are included in the calculations...and is not necessary in the join as you can assign the "a.UserID = b.UserID AND a.UserID = @UserID" bit can just be replaced with "a.UserID = @UserID".you could try including 'some index hints'...Also remember a trigger doesn't just run once for each record being updated...it will run for once for the 'set' of records being updated.....you need to ensure you include/join to the 'updated/inserted' recordset...(or whatever it's called)Also...."ON dt1.UserID = c.UserID AND dt1.UserID = c.UserID"...is a repetition. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 08:59:43
|
| user.Balance is the only field from the user-table that is in use but I could set that to a variable instead before the update and not have to do the join. When it comes to index hints I have never used them before but I'll look at it and give it a shot...And thanx for the trigger-tip...I was just made avare of that in another post and it caused quite alot of confusion before I found out... |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-12 : 09:10:03
|
| aaha...not obvious "balance" was from the "user" table....as you mixed your naming conventions...a.col1, a.col2, a.col3, col4...whereas a.col1, a.col2, a.col3, b.col4 would have been absolutely obvious to us 'extermal people' |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-12 : 09:28:46
|
| Yeah, I guess that's just me beeing lazy :) I edited the query in the original post a bit to make it more readable as all the case-statements really don't have anything to do with the issue. I also removed the join on user as I can easily just set this to a variable instead. The table spool is still there however, only moved a bit (as you can see in the updated picture aswell). How would inserting the derived table into a table-variable first and then join on that be performancewise compared to the table-spool? Do you know? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-13 : 10:11:46
|
| That part is out of my league i'm afraid.....I just jumped in with info on the sections that I understood. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-14 : 15:20:38
|
| Lumbago, the table spool operation is basically creating a table variable (called a spool table) to temporarily hold the table in memory for the duration of the query. How big is the derived table recordset? You normally wouldn't see a big increase in performance by going to a table variable. The best thing to do in this instance is to test though. :) MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-14 : 21:45:35
|
| The derived table will usually be no more than 5-6 rows, 25-30 at the most...but I'm not sure how to test these two against each other since creating a table variable first would be two operations. The only way I know to test two queries against eachother is to look at the plan they create and to look at "cost relative to the batch". Are there any other ways? |
|
|
|
|
Table spool/Eager spool