Archives of the TeradataForum

Message Posted: Mon, 12 Nov 2001 @ 16:21:43 GMT

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

Excellent post from John Grace - the following points are worthy of re-iteration (imo):

"bad SQL can lead to performance problems. This problem is more acute with Teradata than other RDBMSs, because Teradata offers fewer alternatives for overcoming bad SQL. Anyone with knowledge of the CREATE INDEX command and enough disk space can optimize an Oracle database, just keep adding indexes and hope for the best."

Poor SQL, whether user/developer/tool generated, is the root cause of a significant proportion of performance issues. Not only does Teradata offer few escape routes (no option to simply build another index), but the "always on" parallel architecture can punish all users when the "query from hell" overloads a single amp.

"You need to balance the desire for perfect data distribution with to desire to improve query performance."

There is no point in guaranteeing even distribution with a multi-column UPI (the logical key) if a table is routinely accessed/joined via a single column, in effect the NUPI candidate. Data distribution versus data access is a trade-off that requires close monitoring.

"Third, another problem we frequently see with our Teradata customers is the heavy use of Views. Use of Views as a "solution" is referred to, throughout this thread, to simplify the complex SQL generating tasks of query and OLAP tools that have SQL generating limitations."

Tool vendors are at fault for this approach. It bridges the gap between the "gulp, this Teradata schema is in 3NF, our tool will never cope with that" schema that they are presented with at client sites and the "it will work better if we de-normalise etc" schema that they would rather be querying. I've heard this a lot recently.

"All the tables from the view are included EXPLAIN. Teradata is doing significantly more work than is required to answer your query."

A great shame, but true. This builds on the point I made last week. Different schema approaches are not more or less performant, it's the amount of work that changes according to the approach taken. Give any DBMS less work to do and query times will reduce, simple as that. The quickest way to answer a business question is to access as little data as possible as efficiently as possible. Complex views, complex data structures and poor SQL are the least likely way to achieve this simple goal.

If Geppetto's Workshop can do a good job of re-writing the SQL generated by end-user tools, some of which is truly hopeless, then I'm impressed (I have no connection with this company!).

Paul Johnson.

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