Home Forums SQL Server 7,2000 T-SQL Cursors - need to help to explain why to not use them RE: Cursors - need to help to explain why to not use them

  • I have similar purge/archive routines.  In dev environment I can run these as single large delete, and they run twice as fast as when cursoring thru and deleting 1 row at a time. 

    But in busy prod environment, single large delete (not that big, say 20-50k rows from 10M row table) never finishes.  That is to say I have never waited long enough to let one of them because a) it takes long time, and b) it causes massive blocking. 

    I think you cannot win your argument, in general, because there are times when you cannot exec one big stmt, either takes too long or blocks too much, or grows txlog too much, etc.  One could argue that your co-worker's approach at least will always perform reliably & predictably, whereas yours may or may not work at all.  But you should be able to prove one way or the other whether yours works and which approach is faster in your environment.

    Be sure to run your test either in prod or prod-like environment to mirror contention issues that will face your real routine.  Measuring IOs may show no differences, I'd be interested in time duration and blocking impact.  Post your results!