Archives of the TeradataForum

Message Posted: Fri, 26 Nov 2010 @ 12:17:18 GMT

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

Faible Mou wrote:

SPs are transformed into a C-program and compiled into a ".so" on Unix or a ".DLL" on windows with an exported function, this file is stored within Teradata.

When you CALL the SP, it's materialized (and cached) in the file system of the node your PE is running on.

So it runs on a single node, but only the flow control statements, the SQL still runs the same as always.

If you got an endless loop without any SQL, you might see a single cpu on that node running at a 100%.

And this is not recorded as cpu usage in Teradata, this is one of the reasons DBAs don't like it.

Another one is cursors, these are *evil* (unless you use them in the way JK described). Imagine a cursor iterating over a 1 million row answer set serially using "fetch next", this is bad on any DBMS (even if you don't use nested cursors), but in a parallel DBMS it's worst case.

You might have a 100 node system, but the cursor is running on a single node. Probably all of those cursors might be re-written using OLAP functions or recursie SQL, both run in parallel.

No, an SP is part of the calling session.

In ANSI mode there must be a COMMIT after or within the SP. In an explicit transaction in Teradata mode using BT there must be an ET after or within the SP.

In an implicit transaction in Teradata mode each statement in an SP is a separate transaction unless you bundle them into a multistatement request using BEGIN/END.

Btw, you usually don't find newbies reading so thoroughly through the documentation. Well done for a wimp ;-)

Dieter

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