How to increase the speed of searching in a large database?

Thread view:

Ting - 29 Mar 2007 15:49 GMT

Hi all,

I have a very big database that contains one million rows in it. After
an SQL command is executed and the results are assigned to a ResultSet
variable, it takes a long time to print the result out. My code looks
like something similar to this:

ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println(rs.getString(1));
}

And this would take nearly one minute to print the resultset out.

Is there anyway that I can make it faster?

Thanks!
Ting

Reply to this Message

joeNOSPAM@BEA.com - 29 Mar 2007 16:44 GMT

> Hi all,
>
[quoted text clipped - 15 lines]
> Thanks!
> Ting

This is nothing to do with Java, it has to do with
the DBMS, the query, and what has been done to
optimize indexes in the DBMS, and whether you should
be doing the query at all. How many rows are you
selecting?

Joe Weinstein at BEA Systems

Reply to this Message

Bjorn Abelli - 31 Mar 2007 05:06 GMT

"joeNOSPAM@BEA.com" <joe.weinstein@gmail.com> wrote...

>> I have a very big database that contains one million rows in it. After
>> an SQL command is executed and the results are assigned to a ResultSet
[quoted text clipped - 16 lines]
> be doing the query at all. How many rows are you
> selecting?

There is though one thing more connected with Java that *could* speed up
things for you as well, and that is to use a CachedRowSet instead of a
ResultSet.

If you can use it, and if that can increase speed in your case, depends on
which DB you're using, which driver you're using, and how the state of the
network is. In some cases it can speed up things simply as it can minimize
the network traffic, provided the DB/driver can fetch the result in one
single batch. If the network is a bottleneck, the CachedRowSet can then be
useful.

But I'd go with Joe, to check out what you can do on the DBMS first.

/// Bjorn A

Reply to this Message

How to increase the speed of searching in a large database?

Free Magazines