Re: Query Performance

quick correction, i meant to say three identical copies of our databases, on three different boxes. >>> "Brandt Edwin" <bedwin@yankeecandle.com> 06/18/03 08:59AM >>> 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 sending to informix-list