John R Pierce <pierce@hogranch.com> wrote:
> On 8/27/2013 6:49 PM, 高健 wrote:
>> For a query and insert action,
>> Firstly , the data is pull into private memory of the backend
>> process which is service client.
> if you're just writing this data into another table, why not do
> it all in SQL ?
>
> INSERT INTO newtable (field1,field2,field3,...) SELECT <your
> complex 3 million row query here>;
>
> that will do the whole thing without having to move any data into client
> space. this will be faster and more memory efficient.
Agreed.
> now, if your Java client HAS to process the data its selecting and do
> complex stuff with it that you just don't think SQL can/should do, then
> you'll need to use a CURSOR.
Since this is Java, if the data is going to be dragged back to the
client and pushed back across to the server, this is probably the
right way to go about that:
http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company