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
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 |