 |
 |
Archives of the TeradataForum
Message Posted: Thu, 02 Jul 2004 @ 04:59:42 GMT
| Subj: | | Re: Stored procedures and updating seq number with a cursor for loop |
| |
| From: | | Terry Stover |
I've done alot of work on Oracle (nothing on 9 though) and SQL Server. Working with Oracle folks I've found they tend to go with cursors as
the default approach even when standard sql would suffice. I think some of that comes from the earlier versions when they only had a decode
function and didn't support case statements. Also, from a developers perspective it's easier to build the logic incrementally with multiple
statements inside a cursor than to come up with an elegant/complex sql statement.
While stored procedures & cursors aren't as painful as TD, they are still significantly slower than a straight sql statement (Oracle does
handle them better than SQLServer). I still found cursors to be at least a 10-100X performance hit. I always had to challenge the developers to
come up with a non-cursor approach, and I can't recall a cursor based solution coming back faster. You can just treat the cursor solution as a
prototyping exercise, if the performance is OK, then let them slide. If they have problems, then fix them. I always insisted on extensive
application side logging (to db tables, not files) so that it was relatively easy to find the performance problems.
We did use cursors to break really big jobs into pieces to keep from blowing up on transaction logs or tempdb (SQL Server), and redo log &
rollback segments (Oracle). Oracle is more painful since there's more pieces to manage, SQL Server can keep going until you fill up the hard
drives. It seems we were always getting max extents / log (redo & archive) / tablespace / tablespace errors on Oracle though.
| |
