Обсуждение: 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
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
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
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
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
> I have a mylog output, but it is quite huge. > Are there any known tweaks to make inserts on linked ODBC > tables faster? > 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
Le 16/02/2010 16:22, Relyea, Mike a écrit : > 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 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 them in an INSERT string... -- Arnaud Lesauvage
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