Prepared Statements (PostgreSQL)

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
JamesInform
Posts: 19
Joined: Sun 11 May 2014 18:28

Prepared Statements (PostgreSQL)

Post by JamesInform » Fri 19 Feb 2021 21:13

I recognized that every SQL statement that is executed by a TUniQuery against a PostgreSQL server is prepared, no matter if I use params or not.

Beside that there is an overhead in execution time, it has the effect that PostgreSQL doesn't generate a new execution plan if the same query is triggered more than 5 time. (see e.g. PostgreSQL's settings for "plan_cache_mode")

This is the expected behaviour with prepared statments, but I don't want my statements to be prepared.

Question: How can I prevent TUniQuery from preparing every SQL statement?

If there is no option existing, I would STRONGLY vote for the feature to be implemented with the highest priority possible.

James

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Prepared Statements (PostgreSQL)

Post by oleg0k » Wed 24 Feb 2021 15:24

Hello,
You can disable prepared statements in two ways:
1. Use protocol 2 in TUniConnection: UniConnection.SpecificOptions.Values['ProtocolVersion']:='pv20' , which disables Prepare for all data-aware components associated with the current Connection.
2. Use the UnpreparedExecute property: https://www.devart.com/pgdac/docs/devar ... xecute.htm
To execute multiple identical statements with different parameters, you can explicitly run Prepare once, and then run Execute on statements with different parameters.

wbr, Oleg
Devart Team

JamesInform
Posts: 19
Joined: Sun 11 May 2014 18:28

Re: Prepared Statements (PostgreSQL)

Post by JamesInform » Thu 25 Feb 2021 06:49

Hi Oleg

I have used pv20 protocol in the past, but ran into issue with temporary objects. So pv20 is no option.

But for what reason ever, I wasn't aware of "unpreparedExecute". That solved my issue!

Thanks a lot!

James

JamesInform
Posts: 19
Joined: Sun 11 May 2014 18:28

Re: Prepared Statements (PostgreSQL)

Post by JamesInform » Thu 25 Feb 2021 08:05

Two additional questions:

1. With "unpreparedExecute" set to True, is there any reason for not using a standard TUniQuery as a replacement for TUniScript?

2. Is a sql statement with multiple single statements (separated by semicolon) executed in a single transaction if "unpreparedExecute" set to True,?

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Prepared Statements (PostgreSQL)

Post by oleg0k » Tue 16 Mar 2021 16:51

Hello,
1. TUniQuery with UnpreparedExecute enabled is a preferred choice in terms of performance because TUniScript parses and executes individual statements from the script, whereas TUniQuery sends the entire script to the server. It's up to you which one to choose.
2. Yes, the entire statement will be sent to the server and executed within a single transaction.

wbr, Oleg
Devart Team

evgeniym
Devart Team
Posts: 103
Joined: Thu 13 May 2021 07:08

Re: Prepared Statements (PostgreSQL)

Post by evgeniym » Wed 17 Aug 2022 09:52

Thanks for using our product!
Please let us know is any kind assistance from our is required. We always happy to help!

Post Reply