Note that APMSQL works the following way :
APMSQL uses a non-indexed, non sharded Smartstor Hence it takes a lot of time to query standard RDBMS queries, only some SQL queries using Distinct will perform slowly.
So Large queries are at the mercy of the Smartstor database.
What can I do to improve APMSQL performance?
All supported APM releases.
You can consider the following
1) Use a solid-state device for smartstore-DB and also give good amount of memory to the machine.
2) In APM 10.7 SP1, there will be a setting to the directories where to write temporary buffer data instead of the /tmp directory.
3) Add 3-4GB more RAM is required to run APMSQL and MOM/collector on same machine.
4) Running APMSQL on the MOM/collector will reduce network traffic and perform better.