RE: opinions on this SQL statement

Posted in 2004

Hamilton, Jerry — Tue, 25 May 2004 09:30:28 -0500 — source: Usenet: comp.databases.informix

How many rows in tmp_in_threshold? What kind of indexes do you have on tmp_in_threshold? What kind of stats do you have on tmp_in_threshold? -----Original Message----- From: emebohw@netscape.net [mailto:emebohw@netscape.net] Sent: Tuesday, May 25, 2004 8:14 AM To: informix-list@iiug.org 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

↪ replying to Hamilton, Jerry

Ferronato — 25 May 2004 13:57:26 -0700 — source: Usenet: comp.databases.informix

Only adding st more to Jerry ... If you have too much rows in tmp_in_threshold be sure you have created an index for the column capt_oddity_lnk and update statistics high for the column and 'low' for table. If you can, run it: select unique capt_oddity_lnk from tmp_in_threshold into temp tmp1; create unique index ixtmp1 on tmp1 (capt_oddity_lnk ); update statistics...... run again, changind tmp_in_threshold by tmp1 Regards. R. Ferronato "Hamilton, Jerry" <hamiltoj@fleishman.com> wrote in message news:<c8vlv9$u4i$1@terabinaries.xmission.com>... > How many rows in tmp_in_threshold? > > What kind of indexes do you have on tmp_in_threshold? > > What kind of stats do you have on tmp_in_threshold? > > -----Original Message----- > From: emebohw@netscape.net [mailto:emebohw@netscape.net] > Sent: Tuesday, May 25, 2004 8:14 AM > To: informix-list@iiug.org > 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