Re: Select Query hangs up on IDS 9.40.

vpatel66@gmail.com wrote: > Michael Krzepkowski wrote: > >>vpatel66@gmail.com wrote: >> >>>We recently copied data from IDS 7.x to IDS 9.40 and we discovered >>>following SQL statements does not return any data even after couple of >>>hours on IDS 9.4, but it was running fine on IDS 7.x, >>> >>>select a.item_num, a.whse_code, sum(b.committed_qty) committed_qty >>> from em_itemcfg a, ord_l b, item_w >>> where b.item_num = a.item_num >>> and b.whse_code = a.whse_code >>> and b.committed_qty > 0 >>> and a.disc_code is not null >>> and length(a.disc_code) > 0 >>> and b.cust_num in (select cust_num from customer >>> where cust_group[3,4] = "XX") >>> and item_w.item_num between "0" and "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ" >>> and item_w.whse_code = 'XXX' >>> and item_w.repl_code between "0" and "ZZZZZZZZZZZZZZZZ" >>> and a.item_num = item_w.item_num >>> and a.whse_code = item_w.whse_code >>> group by 1, 2>>> >>>If I remove the following statement from where clause on IDS 9.40, it >>>works fine and returns data within 5 minutes. >>> >>> and item_w.item_num between "0" and "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ" >>> >>>I checked the indexes and all three tables has index on item_num and >>>whse_code field. >>> >>>I checked the onstat -u and read counts increasing but slowly for the >>>SQL statement which hangs up. >>> >>>Anyone has any idea why the query hangs on 9.40 with that where clause? >>> >>>Thank you.... >>> >>>Vipul >>> >> >>1. Look at the OPTCOMPIND on the old vs new system. Try value of zero >>(it is in onconfig). >>2. set sqexplain and check optimizer path on both instances. >>3. Have you executed "update statistics" according to all rules? >>4. If this query comes from Elite software have you tried to filter on >>whse_code and item_num on ord_l table? >> >>Post output from sqexplain and number of rows in each of the tables, >>that might help. >> >>HTH >> >>Michael Krzepkowski > > > Thank you Michael for your help, following is the information you have > requested. > > In begening we had OPTCOMPIND set to 2 and we had record lock problem > with some small tables, so we set it to 0 (zero) and all record lock > problem solved. > > Yes, we have executed update statistics. > > Number of rows in ord_l 20200000, in item_w 440000 and in em_itemcfg > 220000 > > Also I tried to run the SQL Statement with set optimization high and > set optimization low, but it didn't help. > > Following sqexplain.out is with OPTCOMPIND set to 0 (zero). > > > QUERY: > ------ > select a.item_num, a.whse_code, sum(b.committed_qty) committed_qty > from em_itemcfg a, ord_l b, item_w > where b.item_num = a.item_num > and b.whse_code = a.whse_code > and b.committed_qty > 0 > and a.disc_code is not null > and length(a.disc_code) > 0 > and b.cust_num in (select cust_num from customer > where cust_group[3,4] = "EL") > and item_w.item_num between "0" and "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ" > and item_w.whse_code = 'NJ1' > and item_w.repl_code between "0" and "ZZZZZZZZZZZZZZZZ" > and a.item_num = item_w.item_num > and a.whse_code = item_w.whse_code > group by 1, 2> > Estimated Cost: 9266890 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Group By > > 1) guest.b: INDEX PATH > > Filters: (guest.b.committed_qty > 0.000 AND guest.b.cust_num = > ANY <subquery> ) > > (1) Index Keys: item_num whse_code (Key-First) (Serial, > fragments: ALL) > Lower Index Filter: guest.b.item_num >= '0' > Upper Index Filter: guest.b.item_num <= > 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' > Key-First Filters: (guest.b.whse_code = 'NJ1' ) > > 2) guest.a: INDEX PATH > > Filters: (LENGTH (guest.a.disc_code ) > 0 AND guest.a.disc_code > IS NOT NULL ) > > (1) Index Keys: item_num whse_code (Serial, fragments: ALL) > Lower Index Filter: (guest.b.item_num = guest.a.item_num AND > guest.b.whse_code = guest.a.whse_code ) > NESTED LOOP JOIN > > 3) informix.item_w: INDEX PATH > > Filters: (informix.item_w.repl_code <= 'ZZZZZZZZZZZZZZZZ' AND > informix.item_w.repl_code >= '0' ) > > (1) Index Keys: item_num whse_code (Serial, fragments: ALL) > Lower Index Filter: (guest.a.item_num = > informix.item_w.item_num AND guest.a.whse_code = > informix.item_w.whse_code ) > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 1720 > Estimated # of Rows Returned: 649 > > 1) informix.customer: SEQUENTIAL SCAN > > Filters: informix.customer.cust_group[3,4] = 'EL' > > > > QUERY: > ------ > select a.item_num, a.whse_code, sum(b.committed_qty) committed_qty > from em_itemcfg a, ord_l b, item_w > where b.item_num = a.item_num > and b.whse_code = a.whse_code > and b.committed_qty > 0 > and a.disc_code is not null > and length(a.disc_code) > 0 > and b.cust_num in (select cust_num from customer > where cust_group[3,4] = "EL") > -- and item_w.item_num between "0" and "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ" > and item_w.whse_code = 'NJ1' > and item_w.repl_code between "0" and "ZZZZZZZZZZZZZZZZ" > and a.item_num = item_w.item_num > and a.whse_code = item_w.whse_code > group by 1, 2> > Estimated Cost: 3819550 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Group By > > 1) guest.b: SEQUENTIAL SCAN > > Filters: ((guest.b.committed_qty > 0.000 AND guest.b.cust_num = > ANY <subquery> ) AND guest.b.whse_code = 'NJ1' ) > > 2) guest.a: INDEX PATH > > Filters: (LENGTH (guest.a.disc_code ) > 0 AND guest.a.disc_code > IS NOT NULL ) > > (1) Index Keys: item_num whse_code (Serial, fragments: ALL) > Lower Index Filter: (guest.b.item_num = guest.a.item_num AND > guest.b.whse_code = guest.a.whse_code ) > NESTED LOOP JOIN > > 3) informix.item_w: INDEX PATH > > Filters: (informix.item_w.repl_code <= 'ZZZZZZZZZZZZZZZZ' AND > informix.item_w.repl_code >= '0' ) > > (1) Index Keys: item_num whse_code (Serial, fragments: ALL) > Lower Index Filter: (guest.a.item_num = > informix.item_w.item_num AND guest.a.whse_code = > informix.item_w.whse_code ) > NESTED LOOP JOIN > > Subquery: > --------- > Estimated Cost: 1720 > Estimated # of Rows Returned: 649 > > 1) informix.customer: SEQUENTIAL SCAN > > Filters: informix.customer.cust_group[3,4] = 'EL' > > > I tried the SQL Statement which hangs with OPTCOMPIND value 2 and it > ran fine. Fllowing is sqexplain output. > > QUERY: > ------ > select a.item_num, a.whse_code, sum(b.committed_qty) committed_qty > from em_itemcfg a, ord_l b, item_w > where