Consider testing the SQL inside a MS SQL tuning tool like MS SQL Tuning Adviser.
Ensure your query is not performing a table scan and is using columns that are indexed.
Pay attention to where the greatest cost is for the query and if Tuning Adviser has any suggestions for the query or for a a new index.
Also, consider limiting the number of rows that the query can return.
In environments with extensive monitoring, database tables can contain a large number of rows. To maintain UMP response time, SLM SQL queries in UMP version 6.0 and later are now limited to 1000 rows and columns to 1024 bytes.
To override the limits, edit the <slm> section of wasp.cfg, add the following parameters, and restart the wasp probe:
- max_rows The maximum number of rows returned
- max_field The maximum length of any column
reloadable = true
cross_context = true
load_on_startup = true
unpack_war = true
path = /slm
max_rows = 10
max_field = 2048