Improving performance for sql in Informix
Can anyone help me to improve performance below sql. Database usage - informix
SELECT
informix.set_reason_codes.description as reason_code,
count(*) as number_of_calls
FROM
informix.contact_history,
informix.set_reason_codes,
informix.customers
WHERE
(informix.contact_history.reason_code = informix.set_reason_codes.reason_code)
and ( ( informix.set_reason_codes.code_type = 'CONTACT_HISTORY' ) )
and ( informix.contact_history.customerkey = informix.customers.customerkey )
and ( informix.contact_history.call_type = 0 )
group
by informix.set_reason_codes.description
order by
informix.set_reason_codes.description
source to share
You need to get the query plan by running this SQL with EXPLAIN ON, that is:
SET EXPLAIN ON;
SELECT ....
This will write the optimizer plan to a file (actual location depends on OS and connection method).
Once you do this, you are in a much better position to determine the cause of your performance problem. But it usually boils down to one of the following things:
- unlocal indices
- stale or missing index statistics
Messages in the sqexplain file about AUTO INDEX or SERIAL SCAN where you would expect NESTED LOOP to be a pretty good indicator, some tweaking is required. If nothing else, run the query and get no explain output, then do
UPDATE STATISTICS MEDIUM FOR TABLE informix.contact_history;
UPDATE STATISTICS MEDIUM FOR TABLE informix.set_reason_codes;
UPDATE STATISTICS MEDIUM FOR TABLE informix.customers;
If you are getting a completely different performance result and reported in the query plan, you know that your problem is statistics.
It would be helpful to know which version of Informix you are using.
source to share
Learn how to use table aliases to read SQL:
SELECT r.description AS reason_code, COUNT(*) AS number_of_calls
FROM informix.contact_history AS h,
informix.set_reason_codes AS r,
informix.customers AS c
WHERE h.reason_code = r.reason_code
AND r.code_type = 'CONTACT_HISTORY'
AND h.customerkey = c.customerkey
AND h.call_type = 0
GROUP BY r.description
ORDER BY r.description
Avoid unnecessary parentheses. You can discuss the layout, but something along these lines usually looks reasonable.
On another day, we might discuss the merits or demerits of using a custom "informix" as the owner of the tables. I recommend not to do this, but there are those who insist that this is the best choice for them. (I disagree with their reasoning, but the client is always right.)
In terms of performance, you will say in a comment that the indexes are:
- Contact_history uses one index for customer key, date_and_time and reason_code.
- For set_reason_codes, one index for code_type, reason_code
- For clients, one index per client key
Part of your problem lies here. The index will probably be useful to you:
CREATE INDEX fk_contact_history ON contact_history(reason_code);
This will help with the union in ' h.reason_code = r.reason_code
'; the existing index is not suitable for this.
You can use the index:
CREATE INDEX ix_set_reason_codes ON set_reason_codes(reason_code);
Then we get into the meat of the question; you are joining the customers table, but there seems to be no real reason for this - assuming that it customerkey
is actually the primary key of the table customers
.
So, you get the same result from this query:
SELECT r.description AS reason_code, COUNT(*) AS number_of_calls
FROM informix.contact_history AS h,
informix.set_reason_codes AS r
WHERE h.reason_code = r.reason_code
AND r.code_type = 'CONTACT_HISTORY'
AND h.call_type = 0
GROUP BY r.description
ORDER BY r.description
source to share