Re: Fastest method to insert data.

I'd be interested to hear thoughts on a "COPY TABLE" method or set of
libraries. In my case I'm reading the table structure of tables in a
Microsoft SQL database, using this information to recreate these tables
in PostgreSql (some mapping of data types was needed of course) then
pulling the data contained in the MS tables and storing them in PG.

In the sequences of actually transferring the data I encountered some
memory problems when using the .addBatch() methods. I probably should
have expected this since the tables I'm seeking to draw data from are
kind of large. I was thinking that using the .addBatch() would be a good
approach since I could treat the whole batch as a transaction helping to
ensure that I pulled everything I intended.

Now I'm reading a record from the MS database and then executing the
insert statement on the PG. database right away. My routines are pretty
rough at this point (I'm still new to the language) but I would have to
imagine that a more sophisticated programmers have probably already
worked this out.

Earlier I was thinking that I would loop through the MS resultset from
and then use and updatable resultset on the PG. side using the
movetoinsertrow and like methods available to an updatable resultset. I
don't think that updatable result sets are supported by the PG. JDBC
driver yet. (This may be in there now) But from a performance point of
view I don't know if it would make a huge difference.

Now, if there were classes that could GULP whole tables from a database
(PG., MS or otherwise) at one shot and recreate these tables in PG. this
would be great!

Sam: Is the direction in which you were thinking?

If this was doable I would have to wonder how much of a difference in
speed would be say in a scenario that is closer to Sam's where a a
tables is being copied both to and from a PG. database versus just a
copy table from the command line of the command interpreter. Maybe Sam's
is a scenario where a stored procedure would be useful.

Dennis

Sam Varshavchik wrote:

>On Fri, Apr 19, 2002 at 12:49:38PM -0700, Barry Lind wrote:
>
>>COPY will always be your best option if speed is the most important factor.
>>
>
>Would COPY issued through JDBC actually work? My understanding is that for
>non-postgres uid clients the COPY command is restricted to using stdin/stdout
>only. Or perhaps it's an artificial limitation imposed by psql. I don't think
>I've seen a COPY TABLE JDBC extension in the documentation.
>
>Resetting System.cin/System.cout to a file, then issuing a COPY TABLE might
>be worth investigating, but that's a hack.
>
>>--Barry
>>
>>Sam Varshavchik wrote:
>>
>>>What would be the fastest way to insert a bunch of data (in 7.1)?
>>>Currently, a bunch of addBatch()es followed by executeBatch() is pretty
>>>fast, but not as fast as COPY TABLE via psql. I suppose I can use a
>>>single executeBatch() to insert the entire table, but AFAIK addBatch()
>>>just buffers the SQL in memory, and I prefer to have some kind of an
>>>upper limit on my memory footprint.
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>message can get through to the mailing list cleanly
>>
>

--
_________
|~~ @|
| ==== | Dennis Roman Gesker ICQ: 194047
| ==== | mailto:dennis(at)gesker(dot)com Fax: 413.740.4653
|_________| http://www.gesker.com