Archives of the TeradataForum

Message Posted: Thu, 01 Jul 2004 @ 11:28:13 GMT

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

Dave Hough stated "Stored procedures run in a single parsing engine, i.e. 1 CPU. That means no parallelism, a tragic thing on a Teradata system."

With a cursor, I don't see how Teradata could run the result in parallel. A cursor insinuates an ordered processing. If you can acheive the same result by avoiding cursors, then I would heartily recommend it.

If you run a standard (non-cursor) query in a stored procedure, it seems to run in the same time as a normal query. I recently developed a stored procedure to perform generic aggregation. The stored procedure is used to build the SQL and then execute it as a single statement. As part of my testing, I ran the aggregation in the stored procedure and then pulled the SQL out of my log table. The run times were essentially the same. I believe that Anita Richards has stated that stored procedures are slightly slower than macros. I believe that this gain is seen in the parsing of the query. But in my case, writing one stored procedure is a lot more efficient that writing hundreds of macros. Plus if you have a process that takes a couple of minutes to run, I doubt that the parser time in significant.

IN short, I think your culprit is the cursor, not the stored procedure.

Glen

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