Archives of the TeradataForum

Message Posted: Tue, 04 Oct 2011 @ 14:15:01 GMT

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

Why, oh why, oh why do Stored Procedures perform so appallingly bad in Teradata, when they include Cursor processing ?

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 !

Yes, I was writing each derived row to a Global Temporary Table and committing each one (which thereby slows things down somewhat by it then logging), but even so, it was bad.

And yes, we eventually found a clever Set Based SQL solution, provided some time back from Dieter (who else ?), but why is it that whenever we're faced with something that's inherently 'procedural', the only way to tackle it, at least with any degree of performance guarantee, is write what normally turns out to be complex to write SQL and invariably almost impossible to read SQL ?

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.

I guess this moan is aimed straight at Teradata themselves, but if anyone has some deep knowledge as to why it's the way it is, I'd certainly be interested to hear about it.

Right, got it off my chest, I feel better now .... thanks for listening.

Regards

David Clough
Senior Database Designer
Database Design Group

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