From: | Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu> |
---|---|
To: | Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp> |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: How to speedup inserts via ADO ? |
Date: | 2010-02-16 09:43:18 |
Message-ID: | 4B7A68B6.2040302@codata.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2010-02-16 14:38:53 | Re: How to speedup inserts via ADO ? |
Previous Message | Hiroshi Inoue | 2010-02-16 03:57:52 | Re: How to speedup inserts via ADO ? |