| Author |
Topic |
|
kmarshba
Starting Member
24 Posts |
 Posted - 2003-01-10 : 23:17:19
|
| I haven't been able to find much information regarding the Eager Spool, other than it showing up in my Execution Plans over and over. My searches, virtually everywhere, turn up no information.Since it uses tempdb I would really like to avoid it's use. It seems to be required when using correlated subqueries. My current thought is to use a table variable for each subquery and then joining those in the final query. If I figure it out before receiving a reply I'll post my findings.Thanks in advance,Kevin M. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-10 : 23:22:55
|
Stellar search technique Kevin, this was only the 2nd thing returned by Google "sql server eager spool":http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_7qgc.aspThat's right, just cause I'm not in the same office with you anymore doesn't mean I'm not gonna smack you every now and again.  Are you gonna be at Mike's moving party tomorrow?Oh yeah, how about posting the query and describing what you want to do? We might be able to find a way to avoid the correlated subquery.Edited by - robvolk on 01/10/2003 23:34:12 |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-01-10 : 23:25:23
|
| I think google has been banned by "that company". It is just too much of an intelligent solution to pass corporate research guidelines Damian |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-10 : 23:26:30
|
| Yeah, I hear Dennis Franz was gonna start doing Google commercials and they got pissed off at him. |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-01-10 : 23:27:03
|
quote:
I think google has been banned by "that company". It is just too much of an intelligent solution to pass corporate research guidelines
Nextel uses SearchKing.com for its research.JustinExpect 0x80040106 |
|
|
|
kmarshba
Starting Member
24 Posts |
Posted - 2003-01-10 : 23:37:45
|
| Thanks for nothing Volk, how about something useful. I've already read this (which is the same text in bol).I meant "useful" search results...I'll post the query at a later date, however it doesn't help answer the question.I should have mentioned that the Eager spool shows up with standard inner/outer joins as well, which you obviously didn't know because you felt you could help me solve the issue by rewriting the query w/o a subquery.I'll be at Mike's after 2pm. I've got kids to take to the doctor, etc. How about you? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-10 : 23:46:53
|
No kids and no doctor myself, and it's interesting how you'll end up there mysteriously close to when we'll be finished moving him...if not lonnnnnnnng after. Coincidence? Or.....CONSPIRACY? quote:
I should have mentioned that the Eager spool shows up with standard inner/outer joins as well, which you obviously didn't know because you felt you could help me solve the issue by rewriting the query w/o a subquery.
Or I could've obviously not known it because you OBVIOUSLY didn't indicate it in your original post. Oooooooh, shocker.  quote:
I'll post the query at a later date, however it doesn't help answer the question.
That's true, most of your queries don't.Edited by - robvolk on 01/10/2003 23:52:00 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-01-10 : 23:59:50
|
KevinI think it has to do with the Append and Replace Data options in your DTS packages. Damian |
|
|
|
kmarshba
Starting Member
24 Posts |
Posted - 2003-01-11 : 00:05:36
|
| Actually the question is related to numerous queries which are optimized pretty well, using indexes that are reindexed nightly. They reference transactional tables which would lose performance with additional indexes. So, I am trying to find other ways to squeeze water out of a rock.I've also seen it in the execution plans on many other occasions w/o having the time to research it fully.Make sure you ask Fem whether he figured out if his VPN would still work since he was moving to a new apartment. |
|
|
|
kmarshba
Starting Member
24 Posts |
Posted - 2003-01-11 : 00:19:25
|
quote:
I think it has to do with the Append and Replace Data options in your DTS packages.
Wow, mistakes can't be hidden from anyone.Actually, it was the Copy Objects task. I unchecked the "Copy All Objects" option and selected the tables I wanted to transfer. Well, I subsequently forgot to uncheck the "Include All Dependent Objects" box, which is checked by default I might add. I personally feel that is an erroneous default. Regardless, I intentionally did not uncheck the "Drop Destination Objects First" box since I did want to replace the tables that were in production.Smile as you imagine my facial expression change as I watch the tables I had selected transfer correctly and just when I think it's done I see several other tables initiate the dropping and transferring of data.Well, the reps lost about 3.5 days of status/call history. Thank the Lord this was around 30 reps versus about 400 we're supporting now.Lesson: um, avoid wizards and wizard type interfaces.Edited by - kmarshba on 01/11/2003 00:26:48 |
|
|
|
|
Can I avoid an Eager Spool?