How to speedup inserts via ADO ?

Hi all !

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 ?

Thanks a lot for helping !

--
Arnaud Lesauvage

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

Arnaud Lesauvage wrote:
> Hi all !
>
> 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.

Could you send me directly the Mylog output?

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

Arnaud Lesauvage wrote:
> Hi all !
>
> 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.

regards,
Hiroshi Inoue


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

Le 16/02/2010 4:57, Hiroshi Inoue a =E9crit :
> 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 r=
ow
>> 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 somewher=
e,
>> 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"=20
and "COMMIT" statements manually (via my connection's "execute" method),=20
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=20
idea what this is)
3. OK I could try this, but the table is very large and this would be=20
very cumbersome to code. That would be a command with about 100=20
parameters, I'd rather just issue a "INSERT INTO pgsql_table SELECT *=20
FROM local-table".

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

Regards
--
Arnaud

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

OK, there was some confusion in my timings.
The problem is not when inserting directly via ADO. This works fine, and
I have response times roughly equivalent to direct psql queries (10.000
inserts in ~7.5s in ODBC and ~7.0s in pgAdmin).

The problem is with linked tables.
No matter what connection string I use to link them (I use
protocol=7.4-1) and whether I use a transaction or not, it takes
approximatively 20 times more time.
In the previous example, inserting 10.000 rows in the linked table takes
~150s !

I have a mylog output, but it is quite huge.
Are there any known tweaks to make inserts on linked ODBC tables faster?

Thanks a lot !
Regards

--
Arnaud Lesauvage

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

> I have a mylog output, but it is quite huge.
> Are there any known tweaks to make inserts on linked ODBC=20
> tables faster?
>=20

Try creating a passthrough query instead. Put your insert statements
into a passthrough query and run that instead of doing the inserts on a
linked table.

Mike

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

Le 16/02/2010 16:22, Relyea, Mike a =E9crit :
> 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.

Thanks for the tip.
Does it also apply to queries passed directly in VBA via the=20
connection's "execute" method ?
If that's the case, I might try to use an ADO "command" instead.
But defining the parameters of such a command is even worse than parsing=20
them in an INSERT string...

--
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: How to speedup inserts via ADO ?

Le 16/02/2010 16:22, Relyea, Mike a =E9crit :
>> 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=20
passed an ADO Field object (escaped with E'' for strings, NULL for=20
nulls, ISO for dates, etc...).
I juste loop through the Fields collection of my recordset, create the=20
SQL string and execute the resulting command directly via=20
Connection.Execute.
Speed is OK now, ~15s for 10.000 records so that is only twice slower=20
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=20
Execute method, I think the limitation won't apply. VB strings can be=20
very long (2^31 characters I think).

Thanks for your help

Regards
--
Arnaud Lesauvage

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc