Re: How to speedup inserts via ADO ?

Le 16/02/2010 4:57, Hiroshi Inoue a écrit :
> Arnaud Lesauvage wrote:
>> Inserts via ODBC are a lot slower than via native psql connection.
>> For instance, I have a very basic insert statement that inserts one row
>> in a table.
>> If I execute it 1000 times in pgAdmin, it returns in 718ms.
>> If I execute it via ADO (with a direct query : Connection.Execute
>> "INSERT INTO..."), it takes 15s to complete.
>>
>> I checked psqlODBC's commlog, but all I see in it are the INSERT
>> statements that I issued. So there must be a lot of overhead somewhere,
>> but I don't understand where it comes from, and more important I don't
>> understand how I could get rid of it.
>>
>> Are there some connection parameters that I could use to speed things up ?
>
> 1. Do inserts in a transction.
> 2. Set the "Level of rollback on errors" option to Transacion.
> 3. Use a prepared Command object.

1. I think I tried this, but do you mean that I should issue "BEGIN"
and "COMMIT" statements manually (via my connection's "execute" method),
or should I use ADO's BeginTrans and CommitTrans method on the connection ?
2. OK I think I haven't tried this, I left it to the default setting (no
idea what this is)
3. OK I could try this, but the table is very large and this would be
very cumbersome to code. That would be a command with about 100
parameters, I'd rather just issue a "INSERT INTO pgsql_table SELECT *
FROM local-table".

I'll activate mylog also. I thought that commlog was enough but I forgot
about mylog.

Regards
--
Arnaud