Increasing the performance for the sql in informix

Question

Hi, Can any one help me in increasing the performance of below sql. Database using is 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

Answer 1

You need to obtain the query plan by running this SQL with EXPLAIN ON, ie:

SET EXPLAIN ON;
SELECT ....

This will write the optimiser's plan to a file (actual location depends on OS and connection method).

Once you have this, you'll be in a much better position to identify the cause of your performance problem. But usually, it boils down to one of these things:

• inappropriate indexes
• out of date or missing index statistics

Messages in the sqexplain file about AUTO-INDEXes or SEQUENTIAL SCANs where you would have expected NESTED LOOP (index joins) are a pretty good indicator some tuning is required. If nothing else, run the query and get the explain output, then execute,

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 get a dramatically different result in performance and reported in the query plan, you know your problem is statistics related.

It would be useful to know what version of Informix you're running, too.

Answer 2

Learn how to use table aliases to make the SQL readable:

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

Avoiding superfluous brackets helps too. You can debate the layout - but something along these lines usually looks reasonable.

Another day, we can debate the merits or lack of merits in using user 'informix' as the owner of the tables - I recommend against doing so, but there are those who insist that it is the best choice for them. (I disagree with their reasoning, but the customer is always right.)

Regarding performance, you say in a comment that the indexes are:

• For contact_history one index for customerkey, date_and_time and reason_code.
• For set_reason_codes one index for code_type, reason_code
• For customers one index for customerkey

Part of your problem lies here. You would probably benefit from an index:

CREATE INDEX fk_contact_history ON contact_history(reason_code);

This would help with the join on 'h.reason_code = r.reason_code'; the existing index is no use for that whatsoever.

You might benefit from an index:

CREATE INDEX ix_set_reason_codes ON set_reason_codes(reason_code);

Then we get to the meat of the issue; you join with the customer table, but there doesn't seem to be any actual reason to do so - assuming that the customerkey is in fact the primary key of the customers table.

So, you would 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