posted 9 years ago
Hello All,
In a nutshell, we have a complex SQL that joins several views , to return several thousand records, to be written to a file. We run this SQL against batch of input data, a part of it always varies in iterations. We are seeing a weird problem, related to performance of this program. In order to meet our SLA , we are expected to complete the file generation with around 200000 records in less than 15 mins every day. For some days, we are able to achieve this and for most of the days, we are not able to meet this and the file generation crossing over an hr.
We are using packaged stored procedures, being called using CallableStatement and iterate over the resultsetl. I added some debug statements, captured the timestamp and noticed that after exection, it is taking longer time to complete rs.next();
I try to explain this with an example.
Briefly, our setup is like below
What I noticed is, the stmt.execute() was quick, completes in few milliseconds. But it takes longer time to ever enter the while loop, for the first time. Once it enters, the looping over resultset is quick. My question is, why and would could be the reason it takes very long time, some times about 15 to 20 mins of time, to just enter the while loop after completing stmt.execute(). This changes for different inputs, but the clogging is happening just before rs.next() is called for the first time.
What can I do to improve this or fix the issue.
Your guidance is highly appreciated?
Note: The SQL that is being called is plain SELECT against several views joinined. There are no write operations involved.