I have a query which I'm using in a cursor in a PL1 program. I see that the job is taking lot of time (more than 2hrs) to execute.
Query:
--------
SELECT B.ICUSABI, B.ICUSAB, B.ICUSCCC
FROM MCDVSAL A , MCDV014 B
WHERE A.CMECSTY = :DCLMCDVSAL.CMECSTY
AND B.IOPUCTY = '103'
AND (B.ICININO= :DCLMCDVSAL.ICININO AND A.CMECSTY = '10' AND
B.DCINISS= :DCLMCDVSAL.DCINYMM OR
B.IICPBVY= :DCLMCDVSAL.ICININO AND A.CMECSTY = '41' AND
B.DIEIDEY= :DCLMCDVSAL.DCINYMM )
AND A.CCINSCE IN ('MCD','DIS');
As this is the new query which I'm inserting in the program, I had created indexes on the fields used in WHERE condition above. But this didnt make my program run faster. Later, I had contacted a DB2 guy who said that the problem is with the query and to be more specific with the 'OR' clause used in the above query. I had tried to modify the above query as below-
SELECT B.ICUSABI, B.ICUSAB, B.ICUSCCC
FROM MCDVSAL A , MCDV014 B
WHERE A.CMECSTY IN ('10','41')
AND A.CMECSTY = :DCLMCDVSAL.CMECSTY
AND B.IOPUCTY = '103'
AND A.CCINSCE IN ('MCD','DIS')
AND ((B.ICININO= :DCLMCDVSAL.ICININO AND A.CMECSTY = '10' AND
B.DCINISS= :DCLMCDVSAL.DCINYMM) OR
(B.IICPBVY= :DCLMCDVSAL.ICININO AND A.CMECSTY = '41' AND
B.DIEIDEY= :DCLMCDVSAL.DCINYMM ));
But even this didnt make my program execute faster.
Can any one suggest/advice the efficient way in which the above query can be written?
Please let me know in case any additional info is required.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
Code:
SELECT B.ICUSABI,
B.ICUSAB,
B.ICUSCCC
FROM MCDVSAL A ,
MCDV014 B
WHERE A.CMECSTY IN ('10','41')
AND A.CMECSTY = :DCLMCDVSAL.CMECSTY
AND B.IOPUCTY = '103'
AND A.CCINSCE IN ('MCD','DIS')
AND (
( B.ICININO = :DCLMCDVSAL.ICININO
AND A.CMECSTY = '10'
AND B.DCINISS = :DCLMCDVSAL.DCINYMM)
OR
( B.IICPBVY = :DCLMCDVSAL.ICININO
AND A.CMECSTY = '41'
AND B.DIEIDEY = :DCLMCDVSAL.DCINYMM )
);
well, what does the EXPLAIN say?
what is the dcl gen for all the columns referenced say?
and this is a little silly:
WHERE A.CMECSTY IN ('10','41')
AND A.CMECSTY = :DCLMCDVSAL.CMECSTY
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
I am probably missing something, but why is this (MCDVSAL A) in the code? What joins A rows to B rows? The data selected is from only B columns.
How did the host variables for the A table get populated for use in this query?
What appears to be happening is that you are generating a Cartesian product which is going to be the worst possible performance. As i mentioned - i may be missing something.
