Thread: Issue with Save and Release points

Issue with Save and Release points

From
Date:

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.

 

 

 

Re: Issue with Save and Release points

From
"Tsunakawa, Takayuki"
Date:

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

 

 

 

Re: Issue with Save and Release points

From
Date:

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.

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: 17 June 2016 02:41
To: Ratnakar Shetty, E. <e.ratnakar.shetty@accenture.com>; pgsql-odbc@postgresql.org
Cc: Machet, Daniel <daniel.machet@accenture.com>
Subject: RE: Issue with Save and Release points

 

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

 

 

 

Re: Issue with Save and Release points

From
"Inoue, Hiroshi"
Date:
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.


Re: Issue with Save and Release points

From
"Inoue, Hiroshi"
Date:
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.

 



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


Re: Issue with Save and Release points

From
Date:

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


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.

 

Re: Issue with Save and Release points

From
Date:

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

 

 

 


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: Machet, Daniel
Sent: 19 June 2016 22:44
To: 'Inoue, Hiroshi' <h-inoue@dream.email.ne.jp>
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 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


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.

 

Re: Issue with Save and Release points

From
Date:

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.

 

Attachment

Re: Issue with Save and Release points

From
Date:

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.

 




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

 

Re: Issue with Save and Release points

From
"Inoue, Hiroshi"
Date:
Hi Daniel,

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

regards,
Hiroshi Inoue

On 2016/06/20 22:58, daniel.machet@accenture.com wrote:

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.

Re: Issue with Save and Release points

From
"Inoue, Hiroshi"
Date:
Hi Daniel,

On 2016/06/21 17:33, daniel.machet@accenture.com wrote:

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

Re: Issue with Save and Release points

From
"Relyea, Mike"
Date:
>> On a separate note – another way to tackle this might be to issue bulk insert statements but my approach so far has
beento 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

Daniel,

If this is an Access limitation, you may be able to work around it by creating a pass-through query to run your SQL.  I
usethis function to create a pass-through on the fly.  I then run the query and delete it when I'm done.
 

Function DefineQuery(strName As String, _
                    strConnect As String, _
                    intTimeout As Integer, _
                    strSql As String, _
                    boolReturnsRecords As Boolean _
                    )
'A function to create a query given the listed parameters
On Error GoTo ErrorHandler
Dim db As dao.Database
Dim qrydef As dao.QueryDef
Dim StsBar As Variant

Set db = CurrentDb

StsBar = SysCmd(acSysCmdSetStatus, "Defining the query...")

db.QueryDefs.Delete (strName) 'Delete the query first if it exists
'Create the query
create_query:
Set qrydef = db.CreateQueryDef(strName)
    qrydef.Connect = strConnect
    qrydef.ODBCTimeout = intTimeout
    qrydef.sql = strSql
    qrydef.ReturnsRecords = boolReturnsRecords

StsBar = SysCmd(acSysCmdClearStatus)
    
ErrorHandler:
Select Case Err.Number
    Case 0
        Err.Clear
    Case 2501
        Err.Clear
    Case 3125
        MsgBox "The query name " & strName & " is not valid.  Make sure it does not contain any punctuation and is not
longerthan 64 characters."
 
    Case 3141
        MsgBox "I couldn't define the query."
    Case 3265
        Err.Clear
        GoTo create_query
    Case 3151
        MsgBox "Connection to database was lost.  Please close and reopen this program."
    Case 3359
        MsgBox "I couldn't create the query properly.  Please close and reopen this program."
    Case Else
        Dim test As Variant
        Dim strCommand As String
        strCommand = "Define Query"
        test = EmailError(Err.Number, Err.Description, strCommand)
End Select
End Function


Mike

Re: Issue with Save and Release points

From
Date:

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

 


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: 21 June 2016 00:25
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,

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

regards,
Hiroshi Inoue

On 2016/06/20 22:58, daniel.machet@accenture.com wrote:

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.

Re: Issue with Save and Release points

From
Date:

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

 


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: 22 June 2016 11:48
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,

On 2016/06/21 17:33, daniel.machet@accenture.com wrote:

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

Re: Issue with Save and Release points

From
Date:
Thanks Mike, 

This is vba for Word, not sure if that makes any difference. Just tried rewriting the query using querydefs (as
demonstratedbelow), issued the bulk sql insert statement as strSql but am still receiving 3137 Missing semicolon (;) at
endof SQL statement.
 


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
useof 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
ofinformation security and assessment of internal compliance with Accenture policy.
 

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



-----Original Message-----
From: Relyea, Mike [mailto:Mike.Relyea@xerox.com] 
Sent: 22 June 2016 14:53
To: pgsql-odbc@postgresql.org
Cc: Machet, Daniel <daniel.machet@accenture.com>
Subject: RE: [ODBC] Issue with Save and Release points

>> 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

Daniel,

If this is an Access limitation, you may be able to work around it by creating a pass-through query to run your SQL.  I
usethis function to create a pass-through on the fly.  I then run the query and delete it when I'm done.
 

Function DefineQuery(strName As String, _
                    strConnect As String, _
                    intTimeout As Integer, _
                    strSql As String, _
                    boolReturnsRecords As Boolean _
                    )
'A function to create a query given the listed parameters On Error GoTo ErrorHandler Dim db As dao.Database Dim qrydef
Asdao.QueryDef Dim StsBar As Variant
 

Set db = CurrentDb

StsBar = SysCmd(acSysCmdSetStatus, "Defining the query...")

db.QueryDefs.Delete (strName) 'Delete the query first if it exists 'Create the query
create_query:
Set qrydef = db.CreateQueryDef(strName)
    qrydef.Connect = strConnect
    qrydef.ODBCTimeout = intTimeout
    qrydef.sql = strSql
    qrydef.ReturnsRecords = boolReturnsRecords

StsBar = SysCmd(acSysCmdClearStatus)
    
ErrorHandler:
Select Case Err.Number
    Case 0
        Err.Clear
    Case 2501
        Err.Clear
    Case 3125
        MsgBox "The query name " & strName & " is not valid.  Make sure it does not contain any punctuation and is not
longerthan 64 characters."
 
    Case 3141
        MsgBox "I couldn't define the query."
    Case 3265
        Err.Clear
        GoTo create_query
    Case 3151
        MsgBox "Connection to database was lost.  Please close and reopen this program."
    Case 3359
        MsgBox "I couldn't create the query properly.  Please close and reopen this program."
    Case Else
        Dim test As Variant
        Dim strCommand As String
        strCommand = "Define Query"
        test = EmailError(Err.Number, Err.Description, strCommand) End Select End Function


Mike

Re: Issue with Save and Release points

From
Date:

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

 

 


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: 22 June 2016 11:48
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,

On 2016/06/21 17:33, daniel.machet@accenture.com wrote:

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