 |
 |
Archives of the TeradataForum
Message Posted: Tue, 04 Oct 2011 @ 18:15:23 GMT
| Subj: | | Re: Stored Procedures - Cursor Processing |
| |
| From: | | Dieter Noeth |
David Clough wrote:
| | Why, oh why, oh why do Stored Procedures perform so appallingly bad in Teradata, when they include Cursor processing ? | |
It's also bad on other DBMSes, but especially bad on Teradata, as you're running a sequential FETCH NEXT on a parallel system.
| | I've been involved in looking at a procedural solution for splitting up text strings - themselves containing numeric keys - deriving
a business equivalent code for those split up numeric values, and then stringing those codes back together. | |
| | We tried two types of solution with Stored Procedures and Cursor processing (one solution even using my one and only ever Recursive
Procedure) but the performance was so bad it was embarrassing ! | |
The recursive version shouldn't be too bad (just consuming lots of spool), but i tested some UDFs to split/concat comma-delimited strings,
these are the fastest way (Teradata's Sergio Ferragut wrote them a few years ago).
| | I say embarrassing above because when I showed it to my Oracle inclined colleague he was at some pains to point out that an
equivalent Procedure in Oracle would be 'blisteringly quick' .... sigh. | |
Most Oracle developers still write lots of (nested) cursors in PL/SQL, it's probably easier for them to manage as they don't have to rack their
brains that much ;-)
On Oracle performance is usually acceptable, but it has never been a parallel database and set based solutions run usually much faster on
Oracle, too.
Dieter
| |
