Re: opinions on this SQL statement

Please run the select by itself with 'set explain on;' and send the sqexplain.out results. REPLACE -- poorly constructed query portion!!! AND s.oddity_link IN (SELECT capt_oddity_lnk FROM tmp_in_threshold) WITH: AND exists (select 1 from tmp_in_threshold where capt_oddity_lnk = s.oddity_link) Good luck, -Tim emebohw@netscape.n et (sumGirl) To: informix-list@iiug.org Sent by: cc: owner-informix-lis Subject: opinions on this SQL statement t@iiug.org 05/25/2004 09:13 AM Please respond to emebohw 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; sending to informix-list