CODEWITHSUNDEEP
javamysqlresultset
Maltram
Maltram

Reputation: 49

search resultset by column

I have a problem regarding the Resultset of a large database. (MySQLDB, Java 1.7)

The task is to perform a transformation of all the entries of one column into another database. (e.g. divide every number by three and write them into another database)

As the database contains about 70 columns and a few million rows, my first approach would have been to get a SELECT * and parse the Resultset by columns.

Unfortunately I found no way to parse it this way, as the designated way intends to go through it row by row (while(rs.next()) {} etc).

I don't like this way, as it would create 70 large arrays, I would have had only one per time to reduce memory usage.

So here are my main questions:

  1. Is there a way?
  2. Should I either create a query for every column and parse them (one array at a time but 70 queries) or
  3. Should I just get the whole ResultSet and parse it row by row, writing them into 70 arrays?

Greetings and thanks in advance!

Upvotes: 0

Views: 286

Answers (2)

Sushil Jain
Sushil Jain

Reputation: 507

Why don't you do it with mysql only.

use this query :

create table <table_name> as select <column_name_on_which_you_want_transformation>/3 from <table name>;

Upvotes: 0

Brian Agnew
Brian Agnew

Reputation: 272437

Why not just page your queries ? Pull out 'n' rows at a time, perform the transformation, and then write them into the new database.

This means you don't pull everything up in one query/iteration and then write the whole lot in one go, and you don't have the inefficiencies of working row-by-row.

My other comment is perhaps this is premature optimisation. Have you tried loading the whole dataset, and seeing how much memory it would take. If it's of the order of 10's or even 100's of megs, I would expect the JVM to handle that easily.

I'm assuming your transformation needs to be done in Java. If you can possibly do it in SQL, then doing it entirely within the database is likely to be even more efficient.

Upvotes: 2

Related Questions