Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Slow connections

  Asked By: Ricky    Date: May 12    Category: Java    Views: 666

Were just about to release our new system. One problem were having is
with one of the methods that grabs data from the database does a
logical comparison and inserts the relevant results into another

For the first 65 or so passes it's fine, but then it really slows
down. The more data it has to sort the slower it gets. This is a real
problem as we plan to have hundreds even thousands of possible sorts.

The routine goes something like this.

It gets some information from the database as a resultset.
it then passes through each record, it has to make a couple of
subsequent connections to the database to get some extra information
about each record. (I know this is inefficent but were having
problems optimising the SQL) It puts all the information into the
required format and inserts it into a table. Then goes back for the
next record.

It's taking a few seconds to do the first 60 or so but after that it
can take upto 5 minuites to complete the whole routine! (Which
averages out to about 80 records at the moment) .



3 Answers Found

Answer #1    Answered By: Herbert Weaver     Answered On: May 12

It appears to me that going back  an "connecting" to the database  is
your log jam. It would be best to create an SQL Join on the "other"
information you have to get as you iterate through the resultset.

From your description, you get the result set, then as you process
each record, you have to go back to the database to get more
information. If you are "connecting" to the database for each data
request, then you are spending ALOT of time getting and releasing
connections and that can be very costly. IF YOU CANNOT DO AN SQL
Join, then you might want to share the connection you got for the
first query with all subsequent connections. You do not have to
close the connection every time you do an SQL call, each "Statement"
can use the same connection, Pass it in as a parameter to the method
doing the next getting of the information.

You might also want to check "WHERE" the log jam is by using a Log
and logging the start and end time in specific areas (entrance into
and exit from certain methods... the start of I/O the end of an
I/O... the start of your result set processing... etc). Or you could

System.out.println("Entered such and such a method: " + new

as a logging method to the console....

But I think your reall problem  is too much database access. It
appears quick when you have a few records, but as you scale into the
hundreds and thousands of records, you have a problem. Look at the
design of your database and use "joins" effectively to get just "ONE"
result set. Then, you don't have to revisit the database and scaling
to a larger record  load is not as time consuming...

Answer #2    Answered By: Richie Smith     Answered On: May 12

I would follow Stephen's suggestions as he is right. You might be
waiting on a free connection to the database  as your program loops
through the records. If that doesn't help then I can make a
suggestion that you explicitly call the gc System.gc();

Answer #3    Answered By: Emma Brown     Answered On: May 12

Also, doing the sorting in java is EXTREEMLY slow!! I recently had
the same problem. If at all possible, use the database  to do the
sorting as well. After all, they're designed to do that kind of

Didn't find what you were looking for? Find more on Slow connections Or get search suggestion and latest updates.