Re: opinions on this SQL statement

All I can tell is that horrors like this (with average size 3GL program masquerading as an SQL statement) indeed run much faster in Oracle than Informix. I learned it hard way: one of my developers (insufficiently supervised) wrote five-page selects like that. They were ten times slower on Informix than Oracle... Bonzi ----- Original Message ----- From: "sumGirl" <emebohw@netscape.net> To: <informix-list@iiug.org> Sent: Tuesday, May 25, 2004 3:13 PM Subject: 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;> > sending to informix-list