Archives of the TeradataForum

Message Posted: Tue, 29 Oct 2002 @ 11:02:45 GMT

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

Jerry,

We have recently migrated from V2R3 to 4.1.3, and have also noticed occasional performance degradation. We were initially baffled as to why this was happening, but we did manage to identify a combination of 2 queries which appeared to be causing the problem. The first query had been run for many months on the old R3 box, but on occasions it would appear to loop on the R4.1.3 box when run alongside a specific insert into an empty table. By loop, I mean that the query which should take approx. 2 minutes would run for 2 hours plus, eating up I/O and CPU before we cancelled the query. The queries were also showing signs of being skewed, when run separately this is not the case. We used PMON to identify the large queries.

The first query was user generated using a 3 table join between 1 relatively large table (80,000,000 rows) and 2 much smaller tables. The second query was an insert into an empty table from a single table with very simple conditions.

We tried creating stats on the tables in the first query, these were missing as the smaller tables were user generated, however, we still encountered this problem.

This is a single example of a problem which we were able to identify, we are still encountering a wide range of performance issues which we are having trouble identifying. We have periods where it can take up to 2 minutes to run a show table on a relatively quiet box. although we are still encountering these performance issues, the performance of the new box is significantly quicker than the old R3 box.

We raised this with NCR who investigated, but I do not believe that they managed to offer an explanation. The two queries are now both run independently with no problems.

If you manage to find an explanation, any feedback would be appreciated.

Richard.

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