opinions on this SQL statement

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;

sumGirl wrote: > 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; Impossible to answer without information about the tables involved, their indexes and the query plan. As a side note... That developer is working with Informix isn't it? If so, he must do the best he can to optimize it (using the above data) and stop using excuses for a "maybe not well done job". Finally, if he think using an "IN" is "as efficient as possible" maybe he should stop being so blind and start looking (for exemple) at "Oracle SQL Tuning" by Mark Gurry from O'Reilly. Kind regards.

> performance issues we see when this runs MUST be due to the outdated > (his words) database technology that is Informix. Tell him he's a pratt!!! >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; Syntactically correct is not always the most performant, you'll need to post tables schemas, indices and the number of rows involved -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #