May 5, 2009 at 2:01 pm
There is nothing worse than a set based operation on millions upon millions of rows, especially when it hits an error and spends just as much time rolling back the transaciton! So absolutely set based can be a killer with large records sets. Similar, I have had issues as you mentioned with set based operations when using cursors. I can recall one instance where calling a vendors api caused so many locks that SQL eventually came to a halt and reported it was unable to allocate anymore locks. So either one can bring a system to a halt.
I think in essence we are talking the same basic principles and thoughts. I still would run the set based in batches as I originally stated (to avoid the issues you just pointed out about tempdb, etc).
I guess I just see the advantage to review the vendors code. As you said, if its cursor upon cursor avoid it like the plague. If its set based inside, maybe run with a loop. Its a call the poster has to make, but they should not skip the 5 minutes of work to look at other solutions besides the obvious put the api call in a cursor (even though it may end up being the right solution).
May 5, 2009 at 2:11 pm
dmc (5/5/2009)
There is nothing worse than a set based operation on millions upon millions of rows, especially when it hits an error and spends just as much time rolling back the transaciton! So absolutely set based can be a killer with large records sets. Similar, I have had issues as you mentioned with set based operations when using cursors. I can recall one instance where calling a vendors api caused so many locks that SQL eventually came to a halt and reported it was unable to allocate anymore locks. So either one can bring a system to a halt.I think in essence we are talking the same basic principles and thoughts. I still would run the set based in batches as I originally stated (to avoid the issues you just pointed out about tempdb, etc).
I guess I just see the advantage to review the vendors code. As you said, if its cursor upon cursor avoid it like the plague. If its set based inside, maybe run with a loop. Its a call the poster has to make, but they should not skip the 5 minutes of work to look at other solutions besides the obvious put the api call in a cursor (even though it may end up being the right solution).
Then we agree completely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 5:46 am
If you have defined a FOREIGN KEY in your child databases, you could also have a ON DELETECASCADE definition in you child database. When you DELETE from the parent database, all data is erased in cascade.
It's only an idea.
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply