Select Query hangs up on IDS 9.40.
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
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
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 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 betwe