Re: How to speedup inserts via ADO ?

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