Nodes and AMPs for Query Tuning

Database
Enthusiast

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 !

Tags (3)
4 REPLIES
Enthusiast

Re: Nodes and AMPs for Query Tuning

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

Enthusiast

Re: Nodes and AMPs for Query Tuning

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.

Senior Apprentice

Re: Nodes and AMPs for Query Tuning

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)

Enthusiast

Re: Nodes and AMPs for Query Tuning

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.