|
View previous topic :: View next topic
|
| Author |
Message |
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
Hi
I have a stored proc which has a db2 select cursor on a table with two non unique indexes. The SP works fine if i pass input values for these two indexes used in the where clause in the case of the result of large number of rows. However if i pass an input which has to fetch 10 records the SP fails with the Time out error. imit = "0000000002" CPU seconds ("0000110000" service units) derived from "". SQLSTATE=57014 .
i executed the query of the cursor in command editor and it gave me results for all sets of inputs.
I am not able to understand how the cursor fails when it can retrieve 1000 rows for one set of inputs and give a time out error when it has to fetch lesser number of rows.
The table has been inserted with some 10000 rows just a day back, will a
reorg on the table help? How can i correct this problem to make the SP work for all sets of inputs?
Please help me with this. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If the problem query needs to search 10 million rows (10000x10000) this may contribute to the time-out. . . .
| Quote: |
| I am not able to understand how the cursor fails when it can retrieve 1000 rows for one set of inputs and give a time out error when it has to fetch lesser number of rows. |
The 1000 row "hit" may be direct while the problem run generates a Cartesian Product . . .
Possibly i misunderstand. |
|
| Back to top |
|
|
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
The Cursor in this SP actually has a where clause with the two index fields in an IN condition, (each IN condition having a sub query). IT is like
select * from table A
where A.sub_id in (select sub_id from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id)
and A.cnt_id in (select cnt_id from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id)
If this query forms a cartesian product for the desired result set, can anyone suggest me a way to avoid this? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
doesn't following SQL return the same result ?
| Code: |
select * from table A
where sub id = :ws-sub-id
and cnt_id = :ws-cnt-id |
which idot coded this :
A.cnt_id in (select cnt_id from table A where sub id = :ws-sub-id and cnt_id = :ws-cnt-id)
it's like : give me all colors where color = 'BLACK'. I can tell you upfront only 'BLACK' will be returned. Only you will read through 10000 rows, return 2000 times 'BLACK' and then sort those 2000 to give the result : 1 time 'BLACK'.
The number of rows returned are usually irrelevant for the CPU-usage. It's the number of rows evaluated that matters. |
|
| Back to top |
|
|
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
GuyC
i missed to include some more, its not tat simple.
The table has data stored in diff ways. the where clause ll use four diff fields to fetch data. all rows will have values for first two fields, sub_id and cnt_id. the other two rows cntl_no and ps_id might hve or might not have values. The actual query ll be like this.
select * from table A
where
A.sub_id in
(select sub_id from table A
where
(sub id = :ws-sub-id
or :ws-sub-id = ' ')
and (cnt_id = :ws-cnt-id
or :ws-cnt-id = ' ')
and (cntl_no = :ws-cntl-no
or :ws-cntl-no = ' ')
and (ps_id = :ws-ps-id
or :ws-ps-id = ' ')
)
and A.cnt_id in
(select cnt_id from table A
where
(sub id = :ws-sub-id
or :ws-sub-id = ' ')
and (cnt_id = :ws-cnt-id
or :ws-cnt-id = ' ')
and (cntl_no = :ws-cntl-no
or :ws-cntl-no = ' ')
and (ps_id = :ws-ps-id
or :ws-ps-id = ' ')
)
If the input is given only for cntl-no then it has to fetch all the rows for the corresponding cnt_id and sub_id. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Well no wonder it isn't performant.
each subselect is probably a complete Tablescan of TableA building two resultsets.
And then for each row of tableA, you probably have a scan through one or two of those resultsets.
I stil don't understand why you use those in-constructs instead of simply putting the criteria in the main where. |
|
| Back to top |
|
|
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
GuyC
The requirement needs the query to be this way, mostly because the table has been designed that way and data is inserted in two ways in it. Instead of having a new table design, inserting data differently has been followed. That is why the query looks like this. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Suppose your table has following rows (I left out ws-cntl-id)
| Code: |
Sub cnt ps
A 1 X
A 2 Y
A 3 Z
B 1 Z
B 2 Y
B 3 X |
now your query with as input parm :
ws-sub = ''
ws-cnt = ''
ws-ps = 'X'
will return 4 rows:
| Code: |
Sub cnt ps
A 1 X
A 3 Z
B 1 Z
B 3 X
|
Are you sure that is what you want ?
the following query will return only two rows:
| Code: |
select * from table A
where (sub id = :ws-sub-id or :ws-sub-id = ' ')
and (cnt_id = :ws-cnt-id or :ws-cnt-id = ' ')
and (cntl_no = :ws-cntl-no or :ws-cntl-no = ' ')
and (ps_id = :ws-ps-id or :ws-ps-id = ' ') |
output
| Code: |
Sub cnt ps
A 1 X
B 3 X
|
anyway, instead of having this (col = :hv or :hv = '' ) you can always have multiple SQL in your code and execute the correct one
if hv = ''
exec sql1
else
exec sql2 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| The requirement needs the query to be this way, |
I suspect it is no part of the "requirement". . .
I suspect the requirement is that the query return the proper result(s) and not dim the data center lights due to excessive resource usage. . . |
|
| Back to top |
|
|
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
GuyC
Yes the query should return 4 rows just like your example, the query i have put here will return 4 rows as IN condition is used in the sub query.
Your suggestion of using multiple queries instead of the existing one will improve the performance? Are you sure about it?
Dick
As i said the insertion of data into the table has been done in a way that is not in accordance with the table definition, this was done to minimize the cost and now the issue surfaces while retrieving the records, that too after a large number of data has been inserted into the table using one shot queries. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
it's not my job to be sure.
You're lucky I'm giving suggestions. It's your job to evaluate , implement and test. |
|
| Back to top |
|
|
ajaikumar andy robert
Warnings : 1
New User
Joined: 01 Dec 2006
Posts: 23
Location: India
|
|
|
|
| Thank you. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| As i said the insertion of data into the table has been done in a way that is not in accordance with the table definition, |
Today feels like another Monday. . . 
The table definition is what it is. An insert will do just that - insert a row. How can data be inserted that is not in accordance with the table definition?
Sounds like more than a little is somewhat out of control and will probably worsen unless it is reviewed and corrected. . . |
|
| Back to top |
|
|
|
|
