Archives of the TeradataForum

Message Posted: Wed, 10 Dec 2008 @ 20:21:22 GMT

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

Jeff may have some additional thoughts, but here are some to get started.

People who are new to Teradata will tend to use SP's and cursors especially if they have experience in another database that encourages that. Such a combination will typically negate all of the parallel processing power of Teradata. This is because cursors and row at a time processing work serially not in parallel. SQL works in parallel, therefore it is better to use that.

I've personally seen instances where an algorithm in an SP (using cursors) converted to pure SQL ran over 500 times faster than the original row at a time SP.

Thus it could be argued that it makes sense to "ban" SP's for people new to Teradata, especially if they are coming from another environment where SP's are the norm.

Having said that, there are also several documented instances where SP's work better than pure SQL. One that immediately comes to mind is where a series of single AMP requests (i.e. small number of rows targetted via PI) need to conditionally be performed (i.e. run SQL 1, if result is X run SQL 2 otherwise SQL 3, if that result is ...). The conditional execution of queries is determined based upon some input parameters and the outcome of the previous request. The alternatives were:

* use a SP and put the conditional logic in it;

* make multiple individual requests to the database under the control of an external program;

* put complex conditional logic into a series of queries in a macro that catered for all variations (single AMP rapidly became all AMP all rows operations).

In performance terms the SP won (this contest) hands down.

Hope this makes sense

Glenn Mc

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