|
View previous topic :: View next topic
|
| Author |
Message |
Pratik Thorat
New User
Joined: 14 Apr 2021
Posts: 1
Location: India
|
|
|
|
Below SQL query in cobol-db2 report program is running for a long time. Requirement is to reduce run-time.
| Code: |
EXEC SQL
SELECT B.column1
INTO hostvar1
FROM table1 A, table2 B
WHERE A.column2 = B.column2
AND A.column3 = B.column3
AND A.column4 = :var1
AND A.column5 = :var2
AND A.column6 = :var3
WITH UR
END-EXEC |
As you can see it is picking column2 and column3 value from table1 based on other three conditions (column 4,5,6 values), matching it (column 2,3) with table2 and picking column1 value from table2.
Is there a better way to write this SQL? Or to do this task in general ?
It's a weekly job and It is running for on avg 9 hours, where this query is taking around 8.5 hours. Yes, there are huge no of records ( 5-6 million)
Coded for you |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Can you run a explain over this query and get us the output please?
That should be the starting point |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
Welcome!
Please use code tags, As suggested , run EXPLAIN or contact DBA so that they can look further into this and provide you suggestions. There may be index lacking, Runstats/Reorg issues or may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins.
This forum can not solve your performance issues but only gives you directions what and where to look for help.
You could split the query into two SELECTs, SELECT from TableA based on var1/2/3 and use those values to do SELECT from TableB to get B.column1. Try it and see if that works. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins. |
This may happen not only with blank values, but with any equal values, like many zeroes to many zeroes, or many ones to many ones. The real data values need to be analyzed to give any advise.
Any complex code MUST be tested step-by-step, starting from minimum operations on minimum amount of data, with MANDATORY verification of ALL intermediate results, and BEFORE seeking for help at forums, or even from the DBA. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| sergeyken wrote: |
| Rohit Umarjikar wrote: |
| may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins. |
This may happen not only with blank values, but with any equal values, like many zeroes to many zeroes, or many ones to many ones. The real data values need to be analyzed to give any advise.
Any complex code MUST be tested step-by-step, starting from minimum operations on minimum amount of data, with MANDATORY verification of ALL intermediate results, and BEFORE seeking for help at forums, or even from the DBA. |
It was a clue to look into this direction as well blank value is just one of those to create such problems and one can only know when analyzed as you said, recently we faced similar situation.
Second if it’s prod issue ( ran only today for long time) then it’s always best to approach for DBA’s expertise as it could be environmental issue or Db2 might have chosen bad access path due to rebind or any changes to code is deployed or different data in the table for this run than last run or something else. They can see more closer than any developer with the accesses and tools.
Let us wait for TS to act than we argue on limited information. |
|
| Back to top |
|
|
|
|
