Re: How to speedup inserts via ADO ?

From: Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu>
To: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: How to speedup inserts via ADO ?
Date: 2010-02-17 10:48:22
Message-ID: 4B7BC976.3040705@codata.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Le 16/02/2010 16:22, Relyea, Mike a écrit :
>> Yes, that's the last option.
>> I would have liked something more straightforward but I think
>> I'll have to go this way.
>> Quite painful to parse the recordset in a plain SQL INSERT
>> though, and it feels like doing in VBA exactly what the ODBC
>> driver is doing.
>
> I agree. I've had to do many such workarounds with my own
> Access/Postgres database. It's not the way things are 'supposed' to
> work but it may end up getting the end result you want.
>
> A word of caution that I bumped into - passthrough queries have a limit
> on the number of characters they can contain. I don't remember what
> that limit is off the top of my head but I'd guess somewhere around
> 65000. If your insert string(s) are very long you may have to split it
> up into more than one passthrough.

I wrote a small function that returns a nice SQL-formatted string when
passed an ADO Field object (escaped with E'' for strings, NULL for
nulls, ISO for dates, etc...).
I juste loop through the Fields collection of my recordset, create the
SQL string and execute the resulting command directly via
Connection.Execute.
Speed is OK now, ~15s for 10.000 records so that is only twice slower
that direct insert in psql ou pgAdmin. Good enough for me !

Since I juste use a VB string to store the query and pass it to the
Execute method, I think the limitation won't apply. VB strings can be
very long (2^31 characters I think).

Thanks for your help

Regards
--
Arnaud Lesauvage

In response to

Browse pgsql-odbc by date

  From Date Subject
Next Message Devrim GÜNDÜZ 2010-03-09 14:48:19 Specifying pg_config path
Previous Message Hiroshi Inoue 2010-02-17 03:43:35 Re: numeric type issue