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
Prepared Statements (PostgreSQL)
Re: Prepared Statements (PostgreSQL)
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
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
-
- Posts: 19
- Joined: Sun 11 May 2014 18:28
Re: Prepared Statements (PostgreSQL)
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
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
-
- Posts: 19
- Joined: Sun 11 May 2014 18:28
Re: Prepared Statements (PostgreSQL)
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,?
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,?
Re: Prepared Statements (PostgreSQL)
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
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
Re: Prepared Statements (PostgreSQL)
Thanks for using our product!
Please let us know is any kind assistance from our is required. We always happy to help!
Please let us know is any kind assistance from our is required. We always happy to help!