Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 01 Jul 2004 @ 00:50:37 GMT


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


Subj:   Re: Stored procedures and updating seq number with a cursor for loop
 
From:   Hough, David A

Stored procedures run in a single parsing engine, i.e. 1 CPU. That means no parallelism, a tragic thing on a Teradata system. I ran a simple test at V2R3 and again at V2R4 to underscore this point for my users: select count(*) versus select with cursor set rowcount=rowcount+1. The cursor version ran 1000X slower that the parallel count(*) version to get the same answer. If you can parallelize the request, I'll bet it will run in seconds.

< uninformed opinion >
It appears to me that DBAs from other platforms (esp. Oracle) use stored procedures heavily, and from that I infer that the performance is comparable to (though perhaps not the same as) SQL statements. I'm guessing that since the data store is relatively monolithic under Oracle (as opposed to partitioned as in Teradata), there's no loss of performance because of loss of parallelism.
< /uninformed opinion >

I would love to hear comments from someone with deep experience on both platforms.

Our user community is only now (V2R5.1) starting to use stored procedures, and then mostly for DDL maintenance using dynamic SQL. There's very little interest in cursors unless the result set is tiny.


/dave hough



     
  <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: 15 Jun 2023