Обсуждение: Issue with Save and Release points

Hi,

We are using psqlodbc_09_05_0100-x86 driver to connect to Postgres 9.4 version through a VB app. When we try running continuous inserts from the application, the performance is really slow (18 minutes for upto a lakh records). On monitoring the logs, we found that there are huge number of save release point executions happening.

duration: 0.000 ms

2016-06-16 17:06:40 BST LOG:  statement: RELEASE _EXEC_SVP_1B107E

2016-06-16 17:06:40 BST LOG:  duration: 0.000 ms

2016-06-16 17:06:40 BST LOG:  statement: SAVEPOINT _EXEC_SVP_1B1082F8

2016-06-16 17:06:40 BST LOG:  duration: 0.000 ms

We tried switching this off by setting the “level of Rollback on error” to Nop and also through the connection string in the application but the logs still show these executions. Any pointers as to what might be causing this issue?

Many Thanks

Regards,

Eisha Shetty

ACCENTURE | UK-NEWCASTLE

( +44 7741587433

* e.ratnakar.shetty@accenture.com

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of e.ratnakar.shetty@accenture.com
We tried switching this off by setting the “level of Rollback on error” to Nop and also through the connection string in the application but the logs still show these executions. Any pointers as to what might be causing this issue?

Could you try setting that item to “Transaction”?

Regards

Takayuki Tsunakawa

Thanks Takayuki,

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

We don’t see same performance drawback on inserting directly on the client

Regards

Daniel Machet

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of e.ratnakar.shetty@accenture.com
We tried switching this off by setting the “level of Rollback on error” to Nop and also through the connection string in the application but the logs still show these executions. Any pointers as to what might be causing this issue?

Could you try setting that item to “Transaction”?

Regards

Takayuki Tsunakawa

Hi Hiroshi,

Apologies for the delayed reply,

I’m not sure where to change the connection string to protocol=7.4-1

This is running psqlodbc35W driver on a windows system and when I try configuring it from odbcad32.exe , the protocol section isn’t shown on page 2 or page 3

I tried adding that line verbose in ODBC.ini but same SAVEPOINT and RELEASEPOINT messages are seen for each statement. Also whenever I make a change to the odbc driver using the odbcad32.exe, it overwrites this and removes the ‘protocol=7.4-1’ statement I’ve added.

Regards

Dan

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue

Thanks Takayuki,

 

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

 

We don’t see same performance drawback on inserting directly on the client

 

Regards

Daniel Machet

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

Hi Hiroshi –

I’ve tried setting the protocol by typing Protocol=7.4-1 in the Connect Settings within the odbcad32.exe configuration and I’ve set Level of Rollback to ‘Transaction’ but I’m still seeing savepoints and release statements for each statement within the transaction.

On further analysis, it appears to be the  following query which happens in between the savepoints and Release statements which is taking up majority of the time and is having a significant impact on the time taken to insert around 57000 records:

select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like 'tblcontributions' and n.nspname like 'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum

2016-06-15 09:49:51 BST LOG:  duration: 3.088 ms

Regards

Dan

Hi Hiroshi,

Apologies for the delayed reply,

I’m not sure where to change the connection string to protocol=7.4-1

This is running psqlodbc35W driver on a windows system and when I try configuring it from odbcad32.exe , the protocol section isn’t shown on page 2 or page 3

I tried adding that line verbose in ODBC.ini but same SAVEPOINT and RELEASEPOINT messages are seen for each statement. Also whenever I make a change to the odbc driver using the odbcad32.exe, it overwrites this and removes the ‘protocol=7.4-1’ statement I’ve added.

Regards

Dan

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue

Thanks Takayuki,

 

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

 

We don’t see same performance drawback on inserting directly on the client

 

Regards

Daniel Machet

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

Hi Hiroshi,

Please find attached the mylog from when the job runs (have only switched on for 1 run and then switched off due to the time cost from this)

Regards

Dan

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue

Thanks Takayuki,

 

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

 

We don’t see same performance drawback on inserting directly on the client

 

Regards

Daniel Machet

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

Hi Hiroshi,

This is repeated about 2 times for each INSERT statement, how would I go about changing the driver to cache the result? (I tried switching on connection pooling for the Postgres Unicode driver with a 60 second timeout but those queries didn’t reduce).

Regards

Dan

Hi Daniel,

Looks like the log is a part of SQLStatistics call.
If SQLStatistics calls for a table are repeated, maybe the driver had better cache the result.

regards,
Hiroshi Inoue

Hi Hiroshi,

 

Please find attached the mylog from when the job runs (have only switched on for 1 run and then switched off due to the time cost from this)

 

Regards

Dan

 

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: 17 June 2016 23:55
To: Machet, Daniel <daniel.machet@accenture.com>
Cc: tsunakawa.takay@jp.fujitsu.com; Ratnakar Shetty, E. <e.ratnakar.shetty@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Issue with Save and Release points

 

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue

On 2016/06/17 23:28, daniel.machet@accenture.com wrote:

Thanks Takayuki,

 

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

 

We don’t see same performance drawback on inserting directly on the client

 

Regards

Daniel Machet

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

 

このメッセージにウイルス は検出されませんでした。
AVG によってチェックされました - www.avg.com
バージョン： 2016.0.7640 / ウイルスデータベース：4604/12454 - リリース日：2016/06/19

Thanks Hiroshi,

Is there anywhere I can do some reading up about the SQLStatistics() function and how to cache its results so that ODBC doesn’t need to run these repeatedly for each statement? Will I be able to call SQLStatistics() from VBA?

On a separate note – another way to tackle this might be to issue bulk insert statements but my approach so far has been to issue bulk statements using the postgres syntax from vba

i.e. to populate a string with an insert statement like

INSERT INTO tblA(field1, field2) VALUES

(1, 999),

(2, 888);

And to issue via the driver using following:

ThisDB.Execute strSQL, dbFailOnError

ThisDB is created using :

Set ThisDB = OpenDatabase("", False, False, DBName)

But I keep getting a syntax error for missing ; even though this exact query works on pgAdmin

Regards

Dan

Hi Daniel,

Unfortunately it needs an implementation change.
If you hope, I would try to cache the result of SQLStatistics().

regards,
Hiroshi Inoue

Hi Hiroshi,

 

This is repeated about 2 times for each INSERT statement, how would I go about changing the driver to cache the result? (I tried switching on connection pooling for the Postgres Unicode driver with a 60 second timeout but those queries didn’t reduce).

 

Regards

Dan

 

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: 20 June 2016 14:33
To: Machet, Daniel <daniel.machet@accenture.com>
Cc: tsunakawa.takay@jp.fujitsu.com; Ratnakar Shetty, E. <e.ratnakar.shetty@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Issue with Save and Release points

 

Hi Daniel,

Looks like the log is a part of SQLStatistics call.
If SQLStatistics calls for a table are repeated, maybe the driver had better cache the result.

regards,
Hiroshi Inoue

On 2016/06/20 17:38, daniel.machet@accenture.com wrote:

Hi Hiroshi,

 

Please find attached the mylog from when the job runs (have only switched on for 1 run and then switched off due to the time cost from this)

 

Regards

Dan

 

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: 17 June 2016 23:55
To: Machet, Daniel <daniel.machet@accenture.com>
Cc: tsunakawa.takay@jp.fujitsu.com; Ratnakar Shetty, E. <e.ratnakar.shetty@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Issue with Save and Release points

 

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue

On 2016/06/17 23:28, daniel.machet@accenture.com wrote:

Thanks Takayuki,

 

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

 

We don’t see same performance drawback on inserting directly on the client

 

Regards

Daniel Machet

---

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

Thanks again Hiroshi,

This has given me some direction, I’ll try recode to use a prepared statement (from a little googling this seems to imply using CreateQueryDef()

on vba… its for a word vba script but should be same across office)

I’ll also check if there are alternatives to DAO for opening the database connection… most likely this will be a bridge too far changewise but at least I’ll learn something

Regards

Dan

Hi Daniel,

Thanks Hiroshi,

 

Is there anywhere I can do some reading up about the SQLStatistics() function and how to cache its results so that ODBC doesn’t need to run these repeatedly for each statement? Will I be able to call SQLStatistics() from VBA?

One way is to PREPARE an insert statement and EXECUTE the PREPARED statement repeatedly.

 

On a separate note – another way to tackle this might be to issue bulk insert statements but my approach so far has been to issue bulk statements using the postgres syntax from vba

 

i.e. to populate a string with an insert statement like

 

INSERT INTO tblA(field1, field2) VALUES

(1, 999),

(2, 888);

 

And to issue via the driver using following:

 

ThisDB.Execute strSQL, dbFailOnError

 

 

ThisDB is created using :

 

Set ThisDB = OpenDatabase("", False, False, DBName)

 

But I keep getting a syntax error for missing ; even though this exact query works on pgAdmin

OpenDatabase() seems a DAO method.
Unfortunately it seems DAO( or Access) doesn't allow to insert multiple rows.

regards,
Hiroshi Inoue

Hi Hiroshi,

You got me thinking i.e. for DAO etc and so I started checking how this relates to vba DAO and found an example for recordset as Dynaset using AddNew and Update which seems to have had a positive impact (taking about  a tenth of the time now which should do for us I reckon)… not sure on the policy for posting links – and have just been cc’d in on this mail so am not aware of where to go to mark your post as the correct solution (if you can forward me a url and advise whether I can share the link?)

Regards

Dan

Hi Daniel,

Thanks Hiroshi,

 

Is there anywhere I can do some reading up about the SQLStatistics() function and how to cache its results so that ODBC doesn’t need to run these repeatedly for each statement? Will I be able to call SQLStatistics() from VBA?

One way is to PREPARE an insert statement and EXECUTE the PREPARED statement repeatedly.

 

On a separate note – another way to tackle this might be to issue bulk insert statements but my approach so far has been to issue bulk statements using the postgres syntax from vba

 

i.e. to populate a string with an insert statement like

 

INSERT INTO tblA(field1, field2) VALUES

(1, 999),

(2, 888);

 

And to issue via the driver using following:

 

ThisDB.Execute strSQL, dbFailOnError

 

 

ThisDB is created using :

 

Set ThisDB = OpenDatabase("", False, False, DBName)

 

But I keep getting a syntax error for missing ; even though this exact query works on pgAdmin

OpenDatabase() seems a DAO method.
Unfortunately it seems DAO( or Access) doesn't allow to insert multiple rows.

regards,
Hiroshi Inoue