How to make the SQL statement more efficient.

Posted in 2013

Topics: Performance & Tuning, SQL Development & Query Writing

JACK PAPA — Fri, 12 Apr 2013 07:26:13 — source: IIUG Forums & Mailing Lists

Hi All, May i ask for your assistance in tuning the below "where clause" statement. The tables where just linked as synonyms into IDS DB version 7.3.1 My question is, is it possible to trim the below statement to be more efficient? WHERE b.wims_sub_vend = c.wims_sub_vend AND c.wims_sub_vend = e.wims_sub_vend AND b.corp = '001' AND b.res_division = c.res_division AND ((e.creation_date BETWEEN '02/03/2004' AND '02/03/2005' AND e.creation_date > b.arrival_from_date - 360) OR (e.last_fm_date BETWEEN '02/03/2004' AND '02/03/2005' AND e.creation_date > b.arrival_from_date - 360)) AND b.arrival_to_date > '02/03/2004' AND upper(b.creation_userid) = 'SPOT000' AND upper(e.creation_userid) = 'SPOT000' AND c.item = e.item AND b.vendor = c.vendor AND b.log = c.log AND c.log = e.log AND c.vendor = e.vendor AND c.whse = e.whse AND b.corp = c.corp AND c.corp = e.corp AND c.res_division = e.res_division AND c.vend_num = e.vend_num AND b.vend_num = c.vend_num AND upper(e.userid) not in ('PACPVRC' , 'PACPIRC' , 'FERRJ0U') AND (c.perform_1 <> '80' AND c.perform_2 <> '80') AND d.corp = '001' AND d.corp_item_cd = e.corp_item_cd group by b.res_division , b.vendor , b.vendor_name , b.log -- , c.perform_1 -- , c.perform_2 , e.creation_date , b.arrival_from_date , b.arrival_to_date , b.offer_number , e.creation_userid , e.last_fm_date , e.userid, b.merchandiser , d.retail_sect , c.corp , c.division , c.facility , c.vend_num , c.vend_sub_acnt , c.wims_sub_vend , c.cost_area --------------------------------------------SET EXPLAIN OUTPUT---------------- Can someone please interpret the below output? I am not that familiar in tuning IDS. ------------------------------------------------------------------------------- Estimated Cost: 39160 Estimated # of Rows Returned: 1 Maximum Threads: 1 Temporary Files Required For: Group By 1) informix.b: SEQUENTIAL SCAN Filters: (informix.b.corp = '001' AND (informix.b.arrival_to_date > 02/03/2004 AND UPPER(informix.b.creation_userid ) = 'USER000' ) ) 2) informix.c: INDEX PATH Filters: (informix.c.vend_num = informix.b.vend_num AND (informix.c.corp = informix.b.corp AND (informix.c.wims_sub_vend = informix.b.wims_sub_vend AND (informix.c.perform_1 != '80' AND (informix.c.perform_2 != '80' AND informix.c.corp = '001' ) ) ) ) ) (1) Index Keys: res_division vendor log whse item seq_nbr (Parallel, fragments: ALL) Lower Index Filter: (informix.c.log = informix.b.log AND (informix.c.vendor = informix.b.vendor AND informix.c.res_division = informix.b.res_division ) ) NESTED LOOP JOIN 3) informix.e: INDEX PATH Filters: ((((informix.e.creation_date >= 02/03/2004 AND informix.e.creation_date <= 02/03/2005 ) AND informix.e.creation_date > informix.b.arrival_from_date - 360 ) OR ((informix.e.last_fm_date >= 02/03/2004 AND informix.e.last_fm_date <= 02/03/2005 ) AND informix.e.creation_date > informix.b.arrival_from_date - 360 ) ) AND (informix.e.vend_num = informix.b.vend_num AND (informix.e.corp = informix.b.corp AND (informix.e.wims_sub_vend = informix.b.wims_sub_vend AND (UPPER(informix.e.creation_userid ) = 'USER000' AND (UPPER(informix.e.userid ) NOT IN ('PACPVRC' , 'PACPIRC' , 'FERRJ0U' )AND informix.e.corp = '001' ) ) ) ) ) ) (1) Index Keys: res_division vendor log whse item (Parallel, fragments: ALL) Lower Index Filter: (informix.e.item = informix.c.item AND (informix.e.whse = informix.c.whse AND (informix.e.log = informix.b.log AND (informix.e.vendor = informix.b.vendor AND informix.e.res_division = informix.b.res_division ) ) ) ) NESTED LOOP JOIN 4) informix.d: INDEX PATH (1) Index Keys: corp corp_item_cd group_cd (Parallel, fragments: ALL) Lower Index Filter: (informix.d.corp = '001' AND informix.d.corp_item_cd = informix.e.corp_item_cd ) NESTED LOOP JOIN Kindly reply. Thank you.

↪ replying to JACK PAPA

Marcus — Fri, 12 Apr 2013 08:45:20 — source: IIUG Forums & Mailing Lists

Hi, the main factor for being slow is the following: 1) informix.b: SEQUENTIAL SCAN Filters: (informix.b.corp = '001' AND (informix.b.arrival_to_date > 02/03/2004 AND UPPER(informix.b.creation_userid ) = 'USER000' ) ) Obviously, you do not have an index that reduces the amount of data to read. So the whole table is scanned. Check the indexes for this table. There will be a problem because of the upper comparison. Only a functional index (I think not available in 7.31) would help here. First, check which columns are selective and then generate an index over these. If you cannot get arount the upper function, there might be a workaround to add a trigger, which modifies the content on the fly during update/insert to be upper case already. Then you could modify the statement to not use the upper function, cause no index mechanism in 7.31 will help here. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "JACK PAPA" <informix2009@gmail.com> An: ids@iiug.org Gesendet: Freitag, 12. April 2013 09:26:13 Betreff: How to make the SQL statement more efficient. [30041] Hi All, May i ask for your assistance in tuning the below "where clause" statement. The tables where just linked as synonyms into IDS DB version 7.3.1 My question is, is it possible to trim the below statement to be more efficient? WHERE b.wims_sub_vend = c.wims_sub_vend AND c.wims_sub_vend = e.wims_sub_vend AND b.corp = '001' AND b.res_division = c.res_division AND ((e.creation_date BETWEEN '02/03/2004' AND '02/03/2005' AND e.creation_date > b.arrival_from_date - 360) OR (e.last_fm_date BETWEEN '02/03/2004' AND '02/03/2005' AND e.creation_date > b.arrival_from_date - 360)) AND b.arrival_to_date > '02/03/2004' AND upper(b.creation_userid) = 'SPOT000' AND upper(e.creation_userid) = 'SPOT000' AND c.item = e.item AND b.vendor = c.vendor AND b.log = c.log AND c.log = e.log AND c.vendor = e.vendor AND c.whse = e.whse AND b.corp = c.corp AND c.corp = e.corp AND c.res_division = e.res_division AND c.vend_num = e.vend_num AND b.vend_num = c.vend_num AND upper(e.userid) not in ('PACPVRC' , 'PACPIRC' , 'FERRJ0U') AND (c.perform_1 <> '80' AND c.perform_2 <> '80') AND d.corp = '001' AND d.corp_item_cd = e.corp_item_cd group by b.res_division , b.vendor , b.vendor_name , b.log -- , c.perform_1 -- , c.perform_2 , e.creation_date , b.arrival_from_date , b.arrival_to_date , b.offer_number , e.creation_userid , e.last_fm_date , e.userid, b.merchandiser , d.retail_sect , c.corp , c.division , c.facility , c.vend_num , c.vend_sub_acnt , c.wims_sub_vend , c.cost_area --------------------------------------------SET EXPLAIN OUTPUT---------------- Can someone please interpret the below output? I am not that familiar in tuning IDS. ------------------------------------------------------------------------------- Estimated Cost: 39160 Estimated # of Rows Returned: 1 Maximum Threads: 1 Temporary Files Required For: Group By 1) informix.b: SEQUENTIAL SCAN Filters: (informix.b.corp = '001' AND (informix.b.arrival_to_date > 02/03/2004 AND UPPER(informix.b.creation_userid ) = 'USER000' ) ) 2) informix.c: INDEX PATH Filters: (informix.c.vend_num = informix.b.vend_num AND (informix.c.corp = informix.b.corp AND (informix.c.wims_sub_vend = informix.b.wims_sub_vend AND (informix.c.perform_1 != '80' AND (informix.c.perform_2 != '80' AND informix.c.corp = '001' ) ) ) ) ) (1) Index Keys: res_division vendor log whse item seq_nbr (Parallel, fragments: ALL) Lower Index Filter: (informix.c.log = informix.b.log AND (informix.c.vendor = informix.b.vendor AND informix.c.res_division = informix.b.res_division ) ) NESTED LOOP JOIN 3) informix.e: INDEX PATH Filters: ((((informix.e.creation_date >= 02/03/2004 AND informix.e.creation_date <= 02/03/2005 ) AND informix.e.creation_date > informix.b.arrival_from_date - 360 ) OR ((informix.e.last_fm_date >= 02/03/2004 AND informix.e.last_fm_date <= 02/03/2005 ) AND informix.e.creation_date > informix.b.arrival_from_date - 360 ) ) AND (informix.e.vend_num = informix.b.vend_num AND (informix.e.corp = informix.b.corp AND (informix.e.wims_sub_vend = informix.b.wims_sub_vend AND (UPPER(informix.e.creation_userid ) = 'USER000' AND (UPPER(informix.e.userid ) NOT IN ('PACPVRC' , 'PACPIRC' , 'FERRJ0U' )AND informix.e.corp = '001' ) ) ) ) ) ) (1) Index Keys: res_division vendor log whse item (Parallel, fragments: ALL) Lower Index Filter: (informix.e.item = informix.c.item AND (informix.e.whse = informix.c.whse AND (informix.e.log = informix.b.log AND (informix.e.vendor = informix.b.vendor AND informix.e.res_division = informix.b.res_division ) ) ) ) NESTED LOOP JOIN 4) informix.d: INDEX PATH (1) Index Keys: corp corp_item_cd group_cd (Parallel, fragments: ALL) Lower Index Filter: (informix.d.corp = '001' AND informix.d.corp_item_cd = informix.e.corp_item_cd ) NESTED LOOP JOIN Kindly reply. Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

IDS (Informix Dynamic Server)