Archives of the TeradataForum

Message Posted: Wed, 26 Jul 2006 @ 18:15:21 GMT

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

Dave,

All answers I have seen are correct so far. Your JDBC application must walk thru the answer sets. This is normal part of coding for JDBC. Since I am not one of the programmers I do not have a snippit of code of how we handle it. However, there is information about it in the JDBC documentation. Also you can search WWW to find out more from JDBC related sites.

I have not checked into what is returned with each answer set, but I assume it has the errorcode, number of rows, etc. You should probably use this in your APP.

Some things you should use in your Tactical Queires tuning toolbox.

1) Keep queries in Request Cache if at all possible as performance will be much better. To hard to explain all parts here, but some of next points tell how to achieve it. Use as few sessions as possible since request cache is at PE level (really trying to keep like requests on as few PE's as possible). This means session pool at mid-tier is recommended. Look at PMON User Info 'Request Cache hits' versus 'REQUEST COUNT' to see how often you are cached.

2) single amp or few amp processing. Make sure using Primary index in where clause. Can also create Join indexes with alternate PI and/or pre-join information? NOTE: too many join indexes or nested views takes longer to resolve plan. This is not a problem after plan is in Request cache.

3) row level locking. Table level lock goes across all amps. This will negate some of the benefit of single amp step. You do not want steps like these in your explain if single amp step is involved (second step is single amp request).

1) First, we lock Common_Reference.Country for access.

3) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

4) Macro use is preferred over Stored PRocs and SQL. It caches faster and also eliminates Syntax checking. Also allows for better security and tuning changes without application changes.

5) USE Prepared statements to get caching in JDBC. This is like Using statement in BTEQ,

6) Avoid use of complex case statements and other functions. This causes quite a bit of overhead in subsecond queries.

7) Use Priority Scheduler or TASM to give resources where needed.

8) Keep dictionary update/insert activity to a minimum. Don't create/drop tables if possible. Use global temp, 'permanent' temp and volatile tables where possible.

9) Smooth out other workload. Try not to have huge spikes in activity on the box. For example, don't start 15 multiloads at the same time to avoid dictionary contention along with other resources. Spread out the start times.

See Carrie Ballinger's 'Implementing tactical Queires' orange paper.

Thanks,

Bob Diehl
Travelocity.com

Gulping down those Virtual BeersJ

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