Re: How to speedup inserts via ADO ?

[Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu>]

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