Current filter:
                                You should refresh the page.
                                  • 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.

                                • Michael (DevExpress Support) 10.05.2018

                                  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.

                                • Anatoly Volodko 10.05.2018
                                • Michael (DevExpress Support) 10.08.2018

                                  Thank you for the link. 

                                1 Solution

                                Creation Date Importance Sort by

                                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)

                                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)

                                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 all comments
                                • Klaus Pieper_1 04.24.2019

                                  Does this conflict with Npgsql's AutoPrepare feature?
                                  We use typically "Max Auto Prepare=50"  to "Max Auto Prepare=200" in the connection string and let Npgsql do the rest.

                                • Dennis (DevExpress) 04.24.2019

                                  Hello Klaus,

                                  We have not tested this combination and are not aware of problems with this. You can find more information on PostreSQL-related forums and other public community resources, because this is not directly related to our product. We make "prepared statements" only for repeated SQL commands. If you experience any difficulties with XPO, please provide us with your debuggable samples and repro steps.

                                  Finally, I want to quote https://www.npgsql.org/doc/prepare.html:
                                  "Note that if you're coding directly against Npgsql or ADO.NET, explicitly preparing your commands with Prepare() is still recommended over letting Npgsql prepare automatically. Automatic preparation does incur a slight performance cost compared to explicit preparation, because of the internal LRU cache and various book-keeping data structures. Explicitly preparing also allows you to better control exactly which statements are prepared and which aren't, and ensures your statements will always stay prepared, and never get ejected because of the LRU mechanism."

                                • Klaus Pieper_1 04.25.2019

                                  Hello Dennis,

                                  we'll test it later when we convert our application to 19.1.

                                  We use our own connection provider and used prepared statements for several years. The AutoPrepare feature took some complexity out of a larger application and works quite well.

                                • Dennis (DevExpress) 04.25.2019

                                  Thanks for your feedback, Klaus.

                                • Balázs 05.17.2019

                                  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 (DevExpress) 05.20.2019

                                  Thanks for your feedback, Balázs.