Best way to query a enormous database table
 

Hi,

I'm working with MySQL as back-end and I must perform a heavy select
against a 250 columns table that could store near 1 million records
aproximately.

What's the best way to do this? I'm newbie to Java language and I've
started with:

ResultSet rs = stmt.executeQuery("SELECT ....");

However, the JDK is throwing me OutOfmemotyException while querying the
table, that today has got "only" 18000 records.

I would really appreciate any ideas.

Thanks very much in advance.

Re: Best way to query a enormous database table
 

> ResultSet rs = stmt.executeQuery("SELECT ....");
>
> However, the JDK is throwing me OutOfmemotyException while querying the
> table, that today has got "only" 18000 records.
>
> I would really appreciate any ideas.

Are you trying to read all records into the java program?
It is better to let the database do the selection based on a where clause,
so that you only get the records needed.
The size of the table should not be a problem.

Also note that a common newbie error is to use Strings instead of
StringBuffers if you are doing a lot of String processing on the data.

Maybe a bit more code could help....

Allan

Re: Best way to query a enormous database table
 

On 19.10.2006 15:11 Lonifasiko wrote:
> Hi,
>
> I'm working with MySQL as back-end and I must perform a heavy select
> against a 250 columns table that could store near 1 million records
> aproximately.
>
> What's the best way to do this? I'm newbie to Java language and I've
> started with:
>
> ResultSet rs = stmt.executeQuery("SELECT ....");
>
> However, the JDK is throwing me OutOfmemotyException while querying the
> table, that today has got "only" 18000 records.
>
> I would really appreciate any ideas.

If it's throwing th OOME during executeQuery() you should check the docs
for the MySQL driver on how to disable the caching. I assume that the
driver tries to load all rows into memory.

If this happens during your processing of the rows, then the answer is
simple: Don't keep all rows in memory :)

Thomas

--
It's not a RootKit - it's a Sony

Re: Best way to query a enormous database table
 

If you are selecting the rows to display them on some sort of a UI, try
to use pagination and limit the set you pick up from the database.
Refer to MySql manual for 'limit' clause. This will help you restrict
the number of rows returned. You can change the limit values for every
page and select a managable number of records rather than the entire
table.

-cheers,
Manish

Re: Best way to query a enormous database table
 

I'm agreeing with the other posters.. turn off caching, make more
selective queries (no full table scans) and pagination with query
limits if applicable. Maybe you should also think of normalizing that
table as well. Maybe it would be easier to troubleshoot if you gave an
example of how you planned to use the data after you retrieved it. In
most cases you shouldn't need every record stored in memory.

--Nick
http://www.lla50.com


Lonifasiko wrote:
> Hi,
>
> I'm working with MySQL as back-end and I must perform a heavy select
> against a 250 columns table that could store near 1 million records
> aproximately.
>
> What's the best way to do this? I'm newbie to Java language and I've
> started with:
>
> ResultSet rs = stmt.executeQuery("SELECT ....");
>
> However, the JDK is throwing me OutOfmemotyException while querying the
> table, that today has got "only" 18000 records.
>
> I would really appreciate any ideas.
>
> Thanks very much in advance.

Re: Best way to query a enormous database table
 

I'm agreeing with the other posters.. turn off caching, make more
selective queries (no full table scans) and pagination with query
limits if applicable. Maybe you should also think of normalizing that
table as well. Maybe it would be easier to troubleshoot if you gave an
example of how you planned to use the data after you retrieved it. In
most cases you shouldn't need every record stored in memory.

--Nick
http://www.lla50.com


Lonifasiko wrote:
> Hi,
>
> I'm working with MySQL as back-end and I must perform a heavy select
> against a 250 columns table that could store near 1 million records
> aproximately.
>
> What's the best way to do this? I'm newbie to Java language and I've
> started with:
>
> ResultSet rs = stmt.executeQuery("SELECT ....");
>
> However, the JDK is throwing me OutOfmemotyException while querying the
> table, that today has got "only" 18000 records.
>
> I would really appreciate any ideas.
>
> Thanks very much in advance.

Re: Best way to query a enormous database table
 

Hi and thanks for both the replies.

In fact, I'm going to filter data from database using a where clause.
It was just an example ;-)

I know it's not a good idea to load into memory all rows. I'll see for
an alternative way to do it.

I'll also take a look at the MySQL driver properties for the caching
issue you mention.

Thanks very much.

Re: Best way to query a enormous database table
 

in message <1161263513.848268.235160@i42g2000cwa.googlegroups .com>,
Lonifasiko ('mloichate@gmail.com') wrote:

> I'm working with MySQL as back-end and I must perform a heavy select
> against a 250 columns table that could store near 1 million records
> aproximately.

I would get someone who knows what they're doing to design the database
schema first. Then I would use a proper heavyweight database engine (such
as Postgres). Then I would look up the SQL keywords 'LIMIT' and 'COUNT'.

Trying to schlurp a whole table into RAM is not a good idea at the best of
times.

--
simon@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; Woz: 'All the best people in life seem to like LINUX.'
;; <URL:http://www.woz.org/woz/cresponses/response03.html>

Re: Best way to query a enormous database table
 

On Fri, 20 Oct 2006 05:03:47 +0800, Lonifasiko wrote
(in article <1161291827.187874.144450@k70g2000cwa.googlegroups .com>):

> Hi and thanks for both the replies.
>
> In fact, I'm going to filter data from database using a where clause.
> It was just an example ;-)
>
> I know it's not a good idea to load into memory all rows. I'll see for
> an alternative way to do it.
>
> I'll also take a look at the MySQL driver properties for the caching
> issue you mention.
>
> Thanks very much.
>



you listen to any of these noobies and you will get yourself into trouble,

Cashing , pagation , limiting queries "normalization of data" WTF!!!

1. Bring only the columns you absolutely need into the client.
2. start by limiting your query using the where clause.
3. USE A SCROLLABLE RESULTSET. / UPDATABLE SCROLLABLE RESULTSET.
This is part of the ODBC standard.

this allows you A "window" on your RECORD selection.

you setup:
how many records you want to see in the "window",
if you want to scroll forward/backward or both,
if the resultset is updatable.
if the updatable resultset is visible by other users.



ultimately you do ONE query, then let the ODBC driver do the hard work,
using "count " and other shitty solutions means you need to issue multiple
queries.


Steve

Re: Best way to query a enormous database table
 

Lonifasiko wrote:
> Hi,
>
> I'm working with MySQL as back-end and I must perform a heavy select
> against a 250 columns table that could store near 1 million records
> aproximately.

Those are just numbers. They don't necessarily correlate to the
complexity of the execution of the query, or the size of the result set.
It would be more meaningful if you showed your table definition, and
the output of "EXPLAIN <yourquery>;"

Indexes can make a dramatic difference in the work a query must do.
Are your tables indexed in a way that is meaningful to your "heavy" select?

> What's the best way to do this? I'm newbie to Java language and I've
> started with:


> ResultSet rs = stmt.executeQuery("SELECT ....");

> However, the JDK is throwing me OutOfmemotyException while querying the
> table, that today has got "only" 18000 records.

Of course it does. You need to query on a cursor, which may not be
possible on the MySQL JDBC driver.

You can keep track of how many trips you have made, and use LIMIT and
OFFSET in your queries, and then have precise control of the resultset
chunks. This has concurrency problems if you cannot lock while in process.

stmt.setFetchSize(##something reasonable##) might help.

This is from the manual
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWA RD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

It will lock the connection until you are done, which might be good or
bad depending on your situation.

The bottom line is that the ResultSet default behavior is to suck the
entire result into a Vector. I think it is horrible that it is a path
to a runtime exception, personally.