All Forums Database
GianTD 47 posts Joined 11/14
01 Dec 2014
Nodes and AMPs for Query Tuning

Hello All !
Wondering how Nodes & no. of AMPs effect the run of a query?
 For ex: if Non Prod Env has 10 Nodes but Prod env has 40 nodes.
How it'll effect the same query in both env's?
In that case will it be okay to tune the query in non-prod env?
Also, pls tell the precise way to check NODES & AMPs of the system.
Thanks !

teradatauser2 236 posts Joined 04/12
01 Dec 2014

The number of AMPS affect the row distrubution of the tables. If you have more number of amps, tables will be more evenly distrubuted as compared to lesser node system. So, if there is an all AMP operation, system with more nodes will perform better as it will be more parallel operation.
no of amps- SELECT HASHAMP()+1

GianTD 47 posts Joined 11/14
02 Dec 2014

So, if test & Prod systems have different no. of nodes & amps...what is best way to tune queries? I mean, first tuning efforts in test & again in prod OR it doesn't matter.
Please explain.

dnoeth 4628 posts Joined 11/04
02 Dec 2014

System infomation is also part of the optimization, so different number of nodes/AMPs, different CPU/disks might result in different plans.
In most cases good SQL will perform good in both systems, if you need to get the exact plan you must explain it on prod (or use TSET to change/fake system info and stats on dev to match those on prod)


GianTD 47 posts Joined 11/14
03 Dec 2014

Is it okay to assume, if query is okay in lower number of nodes/AMPs, it will perform better in system with higher number of nodes/AMPs?
All other parameters are same.

You must sign in to leave a comment.