Archives of the TeradataForum

Message Posted: Mon, 14 Nov 2005 @ 16:37:46 GMT


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

Subj:		Re: Stored procedures results

From:		Eric Friedman

Michael Larkins wrote:

We have never needed SP in Teradata and the main reason they seem to be there now is because Oracle required them to do most anything productive...You say you had to develop 100 SPs for branching decisions? Interesting to say the least.

The big problem with a SP is that everything SPL does is pretty much serial in nature. Therefore, it bottlenecks the execution after the SQL finishes for things would otherwise continue as parallel if done entirely in SQL.

If I understand your posting correctly, you seem to be saying that Teradata does not have dynamic SQL? Teradata SP's do have dynamic SQL that you can build from data stored in the DBC views and execute them immediately as part of the SP. From my perspective, this is the best thing to do in a SP to facilitate DB Admin type of operations.

Hi Michael:

First of all, I agree with you that the best thing to do in a SP is dynamic SQL, either built from data stored in the DBC views or from user- defined tables that drive the specific business application. That defines the heart of a table-driven application. I was reacting to a statement by previous poster Geoffrey Rommel and others that Teradata SPs cannot retrieve intermediate results using dynamic SQL without the kludge of writing results to a work table and then using static SQL to read that work table. In my opinion, that defeats the whole purpose of dynamic SQL. Oracle and SQLServer don't have that problem.

Although you're correct that SPs do process things serially, Oracle for one supports parallel SPs that run independently in each processor and then aggregate their individual results together when all parallel processes are done, thus avoiding the bottleneck except for the final aggregation. This penalty is much like the penalty paid for doing an ORDER BY in Teradata, which has to be done in one AMP.

My reference to "branching decisions" related to not to SPs but to BTEQ, which has no branching except for tests on ERRORCODE and ACTIVITYLEVEL. Thus you have to keep bouncing out of BTEQ back to the Unix script to do any branching logic, which means constantly logging in and out of the database, a highly inefficient task.

As for the over 100 SPs we developed, these were not written to implement "branching decisions," but rather to implement the major table-driven system logic of the application within the database server. Unlike MicroStrategy, Business Objects and other middleware applications that move massive amounts of data out of the database server into the application server to do their "magic", our design utilizes the power of the database engine to manipulate the data entirely within the database server, which is far more efficient.

Our SPs range from such simple, low-level modular utility stuff as a TableExists boolean function to make sure a table exists before you try to do a select on it and experience an error, or a sValidNumber(str) boolean function which ensures the argument string is a valid numeric, to highly complex reporting procedures that read multiple tables to collect required information about various dimensions, make decisions, create intermediate tables, generate complex table-driven SQL to populate them, and finally clean up after themselves. The efficiency of doing all this within a stored procedure rather than a third-party application server is indisputable, but the ability to react to end-user input, look up dimensions and parameters in tables, and occasionally process row-by-row within the database server is a requirement.

Regards,

Eric Friedman
Analytic Consultants, Inc.


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

Attachments

Library

Quick Reference