Hi.This Is My First post .If any Mistakes I apologies. Before i posted my query here I searched related to this query.I found few questions and Answer but not similar to the situation I am facing now.
One of My production job was running since last 9 yrs and the elapsed time(.17) was very less.Job was getting completed within a minute.
As a part of recent changes we introduced a Stored procedures for this module.This module reads input file which have everyday almost 2.5 lakhs of record now the job runs for almost 1hr.
Only actions were performed on this modules are
1) We checked out the module using Endevor and processor group got changed than production
2) Without any changes The same production Load(Compiled with diffrent processor group) also took > 1hr in test region
3) Now We introduced a stored procedure.this also takes > 1 Hr
for each record now there are more DB2 table operation
Now i am getting question why this job is taking so much time after Implementation.But unable to reply because the same production source when compiled with different Processor group (Without making any changes to the source) is also taking huge time in test. i am unable to reply bcoz I have no answer for my own for below queries
Before change =(5 Db2 Select +1 Db2 Update or Insert) * 2.5 lakh record s was taking less than a minute In Production
During testing =(5 Db2 Select +1 Db2 Update or Insert) * 2.5 lakh record s is taking more than one hour in Test
After change = (10 Db2 Select + 1 Db2 Update or Insert) *2.5 lakh record is taking more than one hour In prod
I don't have absolutely no control for that 5 Extra DB2 Select becuase that changes were done by some other team
1) How this job was able to complete within 1 minute prior to Implementation. Does change of processor group impacts Execution time i don't think so..
2) What are the areas i need to look into. I have absolutely no clue what to reply..need some Expert advise.