Ticket T678631
Visible to All Users

How to enable PostgreSQL prepared statements in PostgreSqlConnectionProvider

created 6 years ago (modified 6 years ago)

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.

Comments (3)
DevExpress Support Team 6 years ago

    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.

    DevExpress Support Team 6 years ago

      Thank you for the link.

      Answers approved by DevExpress Support

      created 6 years ago (modified 6 years ago)

      Good news! With XPO v19.1, we have supported prepared SQL statements for PostgreSQL that can be used to optimize performance.

      For this, we implemented the CommandPoolBehavior enumeration with the following values:
       None - no command pooling.
       TransactionNoPrepare - command pooling in the scope of the database transaction without calling command.Prepare().
       Transaction - the same as previous, but with calling command.Prepare().
       ConnectionSession - command pooling in the scope of connection with calling command.Prepare().

      A new protected virtual CommandPoolBehavior property has been added to the ConnectionProviderSql class that you can override for each provider. The ConnectionProviderSql.SupportCommandPrepare property is now marked as obsolete.

      Developers can also set the DevExpress.Xpo.DB.PostgreSqlConnectionProvider.GlobalCommandPoolBehavior property to change command pool behavior for all provider instances. By default, it is set to null, which means CommandPoolBehavior.ConnectionSession for PostgreSQL 8.3 and later and CommandPoolBehavior.None for other versions.

      TEST RESULTS
      Take a look at the following class structure:

      C#
      public class ClassMain : XPObject { ClassCommon common; ClassRef cRef; int counter; DateTime date; string data; string data2; [Size(100)] public string Data { get => data; set => SetPropertyValue(nameof(Data), ref data, value); } [Size(255)] public string Data2 { get => data2; set => SetPropertyValue(nameof(Data2), ref data2, value); } public int Counter { get => counter; set => SetPropertyValue(nameof(Counter), ref counter, value); } public DateTime ItemDate { get => date; set => SetPropertyValue(nameof(ItemDate), ref date, value); } [Association("ClassRef-ClassMains")] public ClassRef CRef { get => cRef; set => SetPropertyValue(nameof(CRef), ref cRef, value); } [Association("ClassCommon-ClassMains")] public ClassCommon Common { get => common; set => SetPropertyValue(nameof(Common), ref common, value); } public ClassMain(Session session) : base(session) { } } public class ClassRef : XPObject { [Size(255)] public string RefData { get => refData; set => SetPropertyValue(nameof(RefData), ref refData, value); } string refData; ClassCommon common; [Association("ClassCommon-ClassRefs")] public ClassCommon Common { get => common; set => SetPropertyValue(nameof(Common), ref common, value); } [Association("ClassRef-ClassMains")] public XPCollection<ClassMain> ClassMains { get { return GetCollection<ClassMain>(nameof(ClassMains)); } } public ClassRef(Session session) : base(session) { } } public class ClassCommon : XPObject { [Association("ClassCommon-ClassMains")] public XPCollection<ClassMain> ClassMains { get { return GetCollection<ClassMain>(nameof(ClassMains)); } } [Association("ClassCommon-ClassRefs")] public XPCollection<ClassRef> ClassRefs { get { return GetCollection<ClassRef>(nameof(ClassRefs)); } } public ClassCommon(Session session) : base(session) { } }

      Test 1. Select data (data set row count: 10000)

      Code
      string someParameter = ""; var query2 = from m in uow.Query<ClassMain>() join r in uow.Query<ClassRef>() on m.CRef equals r join t in uow.Query<ClassCommon>() on m.Common equals t join mm in uow.Query<ClassMain>() on t equals mm.Common into g where r.ClassMains.Count() > 5 && m.Data == someParameter && t.ClassMains.Any() orderby m.Data select new { m.Oid, m.Data, m.Data2, ROid = r.Oid, TOid = t.Oid, TMaxDate = g.Max(ccm => ccm.ItemDate), r.RefData, CommonClassRedsCount = r.Common.ClassRefs.Count() }; ... someParameter = parameters[iterationN]; query2.ToList();

      Operations per second (the greater, the better):
          Command prepare OFF: 261,447745425433
          Command prepare ON (default): 468,246991522444 (+80%)

      Test 2. Select data (data set row count: 10000)

      Code
      string someParameter = ""; var query1 = from m in uow.Query<ClassMain>() join r in uow.Query<ClassRef>() on m.CRef equals r where r.ClassMains.Any() && m.Data == someParameter orderby m.Data select new { m.Oid, m.Data, m.Data2, ROid = r.Oid, r.RefData, CommonClassRedsCount = r.Common.ClassRefs.Count() }; ... someParameter = parameters[iterationN]; query1.ToList();

      Operations per second (the greater, the better):
          Command prepare OFF: 313,55630357617
          Command prepare ON (default): 483,832070541997 (+50% )

      Test 3. Insert data (inserted object count: 14000)
      Command prepare OFF:
          Objects per second: 2869,96351128392
          Test time: 4,8781108 seconds

      Command prepare ON (default):
          Objects per second: 5044,43662236486 (+75%)
          Test time: 2,7753347 seconds (-40%)

        Show previous comments (3)
        Dennis Garavsky (DevExpress) 6 years ago

          Thanks for your feedback, Klaus.

            Seems like a great feature since it really boosts performance, I'm glad it's available.

            You might also want to improve the documentation regarding this feature so it's easier to understand even without this ticket: https://docs.devexpress.com/XPO/DevExpress.Xpo.DB.PostgreSqlConnectionProvider.GlobalCommandPoolBehavior

            Dennis Garavsky (DevExpress) 6 years ago

              Thanks for your feedback, Balázs.

              Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

              Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.