I am assured by a developer that the following SQL statement has been
lovingly crafted to be as efficient as possible and that the
performance issues we see when this runs MUST be due to the outdated
(his words) database technology that is Informix. Can you guys take a
look and let me know what you think of its structure, from purely a
SQL best practice/theoretical standpoint?

INSERT INTO fileoddity SELECT f.file_key, l.oddity_no,
CASE WHEN c.data_type = 1 THEN l.char_data ELSE NULL END,
CASE WHEN c.data_type = 2 THEN l.int_data ELSE NULL END,
CASE WHEN c.data_type = 3 THEN l.dec_data ELSE NULL END,
CASE WHEN c.data_type = 4 THEN l.data_data ELSE NULL END
FROM batchload s, files f, loadoddity l, clntoddity c
WHERE s.membernum = f.membernum AND s.oddity_link
IN (SELECT capt_oddity_lnk FROM tmp_in_threshold)
AND s.clnt_id = f.clnt_id
AND s.oddity_link = l.oddity_link
AND l.oddity_no = c.oddity_no
AND c.oddity_level = 'F'
AND c.entity_id = 141
AND f.file_key > 12596584;

Hi,

Please look in http://www-306.ibm.com/software/data.../pubs/library/.

Gustavo.

So, what does the Optimizer explanation tell you? It's joining several tables (Indexes?) and using the "IN" which tends to promote bad optimizer choices.

Statistics up to date?

Based upon what SET EXPLAIN tells you, you may need to add some optimizer directives to force a given path.