aspose file tools*
The moose likes JDBC and the fly likes Performance issue, while selecting data from a complex SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance issue, while selecting data from a complex SQL" Watch "Performance issue, while selecting data from a complex SQL" New topic
Forums: JDBC Performance

Performance issue, while selecting data from a complex SQL

Kumar Raja
Ranch Hand

Joined: Mar 18, 2010
Posts: 518

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;

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 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.


Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 30309

My guess (based on no data) is that you are running out of RAM and the system is thrashing. Can you gather the statistics for the server while this process is running. You can do this your QA server if you like. See if memory or CPU is maxed out. If that is the case, you can tune the program to be less demanding on that resource.

Another possibility is that another process is getting a write lock on one of the tables involved in your SQL statement.

Are you able to do any of the operations in parallel and merge the data at the end?

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
chris webster

Joined: Mar 01, 2009
Posts: 1651

Sounds like your SQL might need tuning.

Try running SELECT COUNT(*) with the same query body to see if it can identify the matching records quickly. If not, then you'll need to look at your SQL.

Have you run an Explain Plan on the SQL to make sure it's using indexes correctly? Are your join conditions using indexes correctly e.g. you almost certainly need to index your foreign key columns if you are doing lots of joins. Would it be better to query the tables directly, instead of via views, so you have more control over your query? Are your search filters being applied efficiently e.g. are you excluding as much data as possible as early as possible? Are your database stats up to date so the query engine can optimise your query correctly? Are you sorting the output and if so, are you sorting on an indexed column?

Without seeing the actual SQL that you are executing, there's no way to tell if there are problems in the query or not.

No more Blub for me, thank you, Vicar.
Paul Clapham

Joined: Oct 14, 2005
Posts: 18541

And following on from the "short of memory" theory: because the delay takes place before your code gets to read the first record, that suggests that the delay could be caused by copying the entire result set over from the server. (JDBC drivers commonly do this.) Setting the fetch size on the statement might well reduce this delay, but it might just spread the delay around.
Kumar Raja
Ranch Hand

Joined: Mar 18, 2010
Posts: 518

Thanks to you all for your suggestions. I will follow that direction by setting FETCHSIZE and other things. The packaged SQL is on server, which I do not access, so I will have our DBA to find out the plan and other statistics.
subject: Performance issue, while selecting data from a complex SQL