I see that ConnectionProviderSql has SupportCommandPrepare property which always returns false.
This property is not overrided in PostgreSqlConnectionProvider.
So Postgre XPO provider do not use CommandPool while Postgre really can prepare and reuse prepared commands…
It causes performance degradation due to current version of Postgre SQL has NO internal sql execution plan cache and do optimize query on every execute - it causes a lot CPU resource consumption for every query.
So - why PostgreSqlConnectionProvider do not use Prepare?
Can you optimize this?
It would be good to have an option to turn command pooling on or off and to set pool size.
Hello, Anatoly.
Thank you for your message. We are not aware of this Postgre's feature. We will research whether using the command pool in PostgreSqlConnectionProvider will improve performance and consider enabling it in a future version. We will appreciate it if you share some links on documents where this feature is described.
Currently, you can create a custom PostgreSqlConnectionProvider descendant and override the SupportCommandPrepare property to return True. Refer to the How to create a custom XPO connection provider and then use it in an XAF application article.
https://www.postgresql.org/docs/9.3/static/sql-prepare.html
Thank you for the link.