Archives of the TeradataForum

Message Posted: Thu, 03 Nov 2005 @ 17:20:07 GMT

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

Dustin,

I would concentrate on getting better performance on the macros. Here is a list of things I would tackle in order of precedence.

1) Try to make sure macros are single amp lookups if possible.

2) Also make sure they do 'lock row for if single amp, otherwise it will not be a single amp request because there will be a table lock across all amps. access' (can change to other locks if necessary)

3) Try to reduce query complexity if possible. We see a lot more time spent in parsing engine when query is more complex ie more joins, views on views and join index usage.

4) Use priority scheduler to make adjust resource share appropriate with the importance of the request.

5) make sure you are using 'USING' statements in call to macro to ensure immediate cache of plans.

6) Try to get as many requests to use cached plans. Query volume must be high enough on PE to keep request. Two issues not frequent enough volume for request and using too many parsing engines. Plans are cached at the Parsing Engine (PE). The more 'same' requests on the same PE will mean more cached plans. Connections are spread across all Parsing engines in a HOST no matter which Ethernet connection you use. You can tie PE's to different Hosts and allow by id logon by hosts. So basically make sure query volume on each PE is sufficient to keep plans cached. This is done be using fewer sessions or by having fewer PE's per HOST used.

7) make sure you are not running out of amp worker tasks.

I would also look to make sure your network and OLEDB settings are not a bottleneck.

Thanks,

Bob Diehl
Travelcoity.com

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