Archives of the TeradataForum
Message Posted: Wed, 01 Jul 2004 @ 00:50:37 GMT
| 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
| 
