Query Performance

Ok, stumped here. please excuse the long post, but i thought the sqexplain info was important. I have three identical boxes with two Informix servers and four databases a piece (one production box, that was just restored to two test boxes) Box A is our fastest box and screams, everything runs faster on Box A Box B is our mid-range test box, fast but not very Box C is our about to be decomissioned slow test box. I have a query that when run on box A takes 30 seconds. When the same query is run on box B or C, it comes back in less than a second. here's the kicker.. the sqlexplain.out when run on box A shows: QUERY: ------ select x0.label_id ,TRIM ( BOTH ' ' FROM x0.item_key ) ,TRIM ( BOTH ' ' FROM x0.ucc_code ) ,x0.valid_mult ,TRIM ( BOTH ' ' FROM x1.en_item_desc ) ,x2.cur_price_01 from prod@lawprd_2: "lawson".uccvalmult x0 ,prod:"ingres".en_item_tbl x1 ,outer(prod@lawprd_2: "lawson".oebase x2 ) where ((((x0.item_key = x1.en_item_key ) AND (x0.item_key = x2.r_item ) ) AND (x2.company = 1 ) ) AND (x2.base_name = 'RETAIL' ) ) Estimated Cost: 373046 Estimated # of Rows Returned: 141321 1) informix.x0: REMOTE PATH Remote SQL Request: select x0.label_id ,x0.item_key ,x0.ucc_code ,x0.valid_mult from prod:"lawson".uccvalmult x0 2) informix.x1: INDEX PATH (1) Index Keys: en_item_key Lower Index Filter: informix.x1.en_item_key = informix.x0.item_key NESTED LOOP JOIN 3) informix.x2: REMOTE PATH Remote SQL Request: select x1.cur_price_01 ,x1.r_item ,x1.company ,x1.base_name from prod:"lawson".oebase x1 where ((x1.company = 1 ) AND (x1.base_n ame = 'RETAIL' ) ) (1) Index Keys: company r_item base_name Lower Index Filter: (informix.x2.company = 1 AND (informix.x2.base_name = 'RETAIL' AND informix.x2.r_item = informix.x0.item_key ) ) NESTED LOOP JOIN But, the sqexplain.out on Box B and C show: QUERY: ------ select x0.label_id ,TRIM ( BOTH ' ' FROM x0.item_key ) ,TRIM ( BOTH ' ' FROM x0.ucc_code ) ,x0.valid_mult ,TRIM ( BOTH ' ' FROM x1.en_item_desc ) ,x2.cur_price_01 from prod@lawtst01_2: "lawson".uccvalmult x0 ,prod:"ingres".en_item_tbl x1 ,outer(prod@lawtst01_2: "lawson".oebase x2 ) where ((((x0.item_key = x1.en_item_key ) AND (x0.item_key = x2.r_item ) ) AND (x2.company = 1 ) ) AND (x2.base_name = 'RETAIL' ) ) Estimated Cost: 51 Estimated # of Rows Returned: 10 1) informix.x0: REMOTE PATH Remote SQL Request: select x0.label_id ,x0.item_key ,x0.ucc_code ,x0.valid_mult from prod:"lawson".uccvalmult x0 2) informix.x1: INDEX PATH (1) Index Keys: en_item_key Lower Index Filter: informix.x1.en_item_key = informix.x0.item_key NESTED LOOP JOIN 3) informix.x2: REMOTE PATH Remote SQL Request: select x1.cur_price_01 ,x1.r_item ,x1.company ,x1.base_name from prod:"lawson".oebase x1 where ((x1.r_item = ? ) AND ((x1.compan y = 1 ) AND (x1.base_name = 'RETAIL' ) ) ) NESTED LOOP JOIN My costs and estimated rows comes back way higher on the fast box and the sqexplain.out is nearly the same expcet that on boxes B and C i get x1.r_item in my where clause, but why not on Box A? BOX A 3) informix.x2: REMOTE PATH Remote SQL Request: select x1.cur_price_01 ,x1.r_item ,x1.company ,x1.base_name from prod:"lawson".oebase x1 where ((x1.company = 1 ) AND (x1.base_n ame = 'RETAIL' ) ) BOX B,C 3) informix.x2: REMOTE PATH Remote SQL Request: select x1.cur_price_01 ,x1.r_item ,x1.company ,x1.base_name from prod:"lawson".oebase x1 where ((x1.r_item = ? ) AND ((x1.compan y = 1 ) AND (x1.base_name = 'RETAIL' ) ) ) update statitistcs (as determined by Art's dostats) has been run on all the relevant tables, so why am i losing that where clause on the remote path??? please help, any and all ideas welcome. sorry for the long post. Brandt Edwon DBA Yankee Candle sending to informix-list

On Wed, 18 Jun 2003 08:59:31 -0400, "Brandt Edwin" <bedwin@yankeecandle.com> wrote: Are your table schemas the same? I didn't see the index keys used on 3) from the box b & c. ..long message clipped...

Brandt Edwin wrote: > update statitistcs (as determined by Art's dostats) has been run on all > the relevant tables, so why am i losing that where clause on the > remote path??? I was given to understand that update stats was verboten on Lawson...?

On Wed, 18 Jun 2003 14:48:10 +0100, Obnoxio The Clown <obnoxio@hotmail.com> wrote: Not so . . . You weren't supposed to run update statistics on the earlier versions of Lawson, though. We are running 4gl against Lawson tables as part of our custom subsystem, so we didn't have any choice. Only had a minor issue in an Informix version where we couldn't run UPDATE STATISTICS LOW in certain circumstances. The more current versions of Lawson are using optimizer hints. >Brandt Edwin wrote: > >> update statitistcs (as determined by Art's dostats) has been run on all >> the relevant tables, so why am i losing that where clause on the >> remote path??? > >I was given to understand that update stats was verboten on Lawson...?