Archives of the TeradataForum

Message Posted: Tue, 04 Oct 2011 @ 15:52:40 GMT

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

I have had the same issue, as some processes are inherently procedural and with no way to avoid it.

I.e. Special accounting rules regarding re-statements, running balances and reset rule logic.

In Teradata Set logic is the way to go, 99% time when your loop size grows beyond a reasonable amount. However this is not always a practical solution given the complexity of some SET processing features.

One recommendation based on something I have done, depending on what you are doing, is that if you have access to an ETL tool like Informatica (this is not a pitch just a tool with the appropriate functionality) you can write custom Java Transformations to do whatever procedural processing you may need to do. You can suck the data out of TD relatively fast (FastExport), process it with a programming language like Java which screams compared to SPL on TD, and shove it back down any which way you please (TPUMP, MLOAD, FASTLOAD).

Good for procedural logic on ordered data, Normalization, Denormalization ( outside of the ETL tool native functionality).

Not a pretty solution, but if you have 10 million rows to loop through it will be a lot faster than SPL.

Another thing to look into is External Stored procedures both JAVA and C/C++ (however I am not sure how well that will work as I have never used one) if it is something that you want to be doing on the fly in the DB for reporting or to be used as an intermediate ELT step after you load raw data to the EDW.

There is a book "SQL External Routing Programming" in the TD documentation that gives some examples you may check into that.

Does anyone have advice on using External SPs, any experience with issues regarding performance tradeoff, maintenance, etc...?

Eric W. Barner

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