Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 02 Jul 2004 @ 04:59:42 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


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.



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016