Archives of the TeradataForum

Message Posted: Fri, 02 Jan 2009 @ 23:05:29 GMT

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

Aman,

You can only run explains on each of the individual queries. You may not get the same plan unless you manually run thru the steps in the order to have the tables be populated the same way as when running the stored proc.

Some other ways to find out what is performing poorly. While it is running you can use Teradata Manager or PMON to monitor the queries as they are occurring. Using this method it is hard to find problems with logic or parts stuck in endless loops.

If you have query logging turned on you can look at the query log tables to find the long running and inefficient queries. This will only help you after the queries have run.

You can add code to the procedure that rights the status of important events or facts to tables in Teradata. You would want to include a timestamp in these tables.

You will probably find it is best to use all three of the techniques above.

This may not be your problem, but one thing I have noticed of the years is that many people try to use row by row processing in stored procedures. This is often very slow. It is best to try to perform set processing (many rows at once) to accomplish the same steps. I have found very few places where set processing could not accomplish the same steps. So I take the process of writing stored procedures as set processing first and then cursor logic only if really needed.

Thanks,

Bob Diehl

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