Archives of the TeradataForum

Message Posted: Thu, 22 Jul 1999 @ 17:46:21 GMT

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

Just to provide some information that might be useful. To start with, OLTP seems as much a marketing term as 'new and improved'. I have encountered customers who define OLTP as queries with response times as long as a couple of minutes and I've run into others who required sub-second response times. The one thing in common was that they were doing large volumes of identical (or very similar) queries - all of which they wanted to perform quickly on demand.

I mention this because we really haven't specified what our requirements are for the purpose of this thread. Let's focus on repetitive simple queries with minimal response times.

A query is parsed, optimized, executed and an answer set is returned to the user. To get sub-second response times, we have to assert as much control over these actions as we can. The first performance delay that is encountered is parsing and optimizing. The path time for parsing and optimizing sets the minimum time for a query.

Consider a simple query that does a primary index retrieval: The retrieval of a single row takes less time than it takes to parse and optimize the query (remember that parsing involves things like retrieving table and column definitions and verifying access rights).

After any given SQL has been submitted a couple of times, the plastic steps are cached. What's important to understand is that all submissions of any given query has to be identical for it to be cached. Any change to the SQL causes it to be evaluated as a new statement which will have to be re-parsed.

As I understand it, a newly submitted statement is hashed and that hash value is used to determine if the statement has already been cached (this would infer that even adding a space to the statement would cause it to be re-parsed). To pass new data into a query without causing it to be re-parsed, you'll need to use a USING clause with your SQL.

The next issue is table locking. When your query tries to apply a lock (ie- a read lock), the lock can't be applied until all VProcs have agreed to the lock. For example, I might have a query with a row hash write lock for table ATBL on VProc #1 - so a table level read lock on table ATBL can't be granted until the row hash write lock on VProc #1 has been released. As your system grows, this has more of a performance impact.

If you don't specify a LOCKING clause on your query, then a row hash lock is assumed (which is a single VProc operation). If your query reads a single row from a table, the row hash lock will only lock the single row (actually the hash value) which is to be retrieved.

One thing that has been encouraged in the past is the use of the LOCKING FOR ACCESS clause. When a LOCKING FOR ACCESS is encountered, all the VProcs have to acknowledge the request - even though no lock is actually applied. So if you're trying to maximize response times, then you should avoid the use of the LOCKING FOR ACCESS clause (except if your users are submitting queries against the production loads).

The next issue in maximizing query response times is to make sure that your query is making use of the primary index. You also want to make sure that your query is simple - response times will obviously increase as your query becomes more complex. Anything much more complex than a PI retrieval will start to accrue spool file overhead. The bottom line here is that you'll need to design the tables for your OLTP database to accommodate your application's performance needs.

Depending on your application, additional performance might be gained with multi-statements.

Finally, the amount of data you return to the user affects overall performance.

The Teradata database was not designed for OLTP, but rather DSS with large volumes of data. Despite this, many customers have successfully implemented OLTP-like applications using Teradata where both OLTP and DSS applications have co-existed. Successful co- existence, however, is a balancing act that requires careful design and attentive administration (but that's true of any OLTP application).

One of the characteristics of the Teradata is that as workload increases (or if it is skewed), the overall response times increase. You have probably noticed that the execution times of queries can vary quite a bit. This variation would be unacceptable in many OLTP environments and is something that you need to consider in your design.

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