QueryPlanCache in SessionFactory grows until OutOfMemory errors

[Andrijan Jelovic]

Hi.   Here's my StackOverflow post at http://stackoverflow.com/questions/27276129/fluent-nhibernate-memory-leak-with-heap-dump-attached .

For a project of mine, I'm creating a folded Expression statement that can contain on the order of 10000 individual where clauses (so that I can minimize the number of calls to the database).  NHibernate Linq works, but the large expression trees are being cached in queryPlanCache of the session factory (which I've verified in Debugging).  I need to be able to clear the queryPlanCache or prevent it from growing.   I'm using IStatelessSessions throughout my code so I can handle large datasets. 

Here's the heap dump using CLRProfiler at https://app.box.com/s/pvbe9ryfb0v168rka2e3.  Is there a way to prevent the queryPlanCache from being used?

[Andrijan Jelovic]

Hi.  I've solved the problem using reflection to access the planCache field in QueryPlanCache and to set the Hashtable to have a content size from 128 to 8.  The Hibernate 4.0 standard does let the planCache field be resizeable, but it doesn't look like the NHibernate standard lets the field be tunable yet.

[Gunnar Liljas]

Hi!

10000 individual where clauses?! That sounds like some that could be optimized before it hits both NHibernate and the database,

Still, if you really need to clear the query plan cache I guess it can be done using reflection.

Try this:

public static class SessionFactoryExtensions
{
 public static void ClearQueryPlanCache(this ISessionFactory sessionFactory)
 {
 var factory = sessionFactory as SessionFactoryImpl;
 if (factory == null)
 {
 return;
 }
 var queryPlanCacheType = typeof (QueryPlanCache);
 
 var cache = queryPlanCacheType
 .GetField("planCache", BindingFlags.Instance | BindingFlags.NonPublic)
 .GetValue(factory.QueryPlanCache) as SoftLimitMRUCache;


 if (cache != null)
 {
 cache.Clear();
 }
 }
}

Maybe you want to trigger garbage collection too.

[Andrijan Jelovic]

My program is doing a large data synchronization between two databases on addresses.  When I queried the destination database for an address by content, (with the query having to be a query of the destination address' street number, street name, predirection, postdirection, suffix, and postal code), I found that I would have too much time spent on network overhead (opening the connection and transmitting the payload).  The Oracle database has to search the address by content anyways so it makes no difference if it has to search 1 record or 1000 records in the where clause statement.  I can fine tune how many records I want to pull across at once, when I query the addresses by content, but I'm not writing a web application, where the data queried is usually small. 

Thank you for your response.  I took an alternate approach where I set the value of "planCache" to 'new SoftLimitMRUCache(8)'.  I don't think I would have noticed the problem if my .NET program was running in 64 bit mode, but I think .NET programs in 32 bit mode are limited to 1.5 GB of Heap space size.

[Ricardo Peres]

Are you using parameters or are you putting query values directly in the query? For example, in HQL, do you have: CreateQuery("select s from Something s where s.SomeProperty = 1") or CreateQuery("select s from Something where s.SomeProperty = :p").SetParameter("p", 1)?

[Ricardo Peres]

If you are using LINQ, it uses parameters.

[Gunnar Liljas]

Does HQL even support non-parametrized queries?

2014-12-05 5:44 GMT+01:00 Ricardo Peres <rjp...@gmail.com>:

If you are using LINQ, it uses parameters.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[Ricardo Peres]

Yes, it does. Queries are cached based on the HQL + parameter *declarations*, so, two queries with the same HQL and different parameters will only be stored once. If there are so many cached queries, I can only imagine that they are not using parameters, but it's just a wild guess.

RP

[Andrijan Jelovic]

Hi everyone.  I'm using Linq queries.

Here's an example of the address search expression for an individual address

        private IMonad<Option, Expression<Func<Address, bool>>> addressExpressionByContent(
            IDictionary<string, Direction> sortedDirections, IDictionary<string, StreetType> sortedStreetTypes,
            HansenAcctVw hansenAcctvW) {
            var streetNameOption = hansenAcctvW.getStreetname(sortedDirections);
            var houseNumberOption = hansenAcctvW.getHouseNumber();
            var subdesignationOption = hansenAcctvW.getSubdesignation();
            var postalCodeOption = hansenAcctvW.getFormattedPostalCode();
            var predirectionOption = hansenAcctvW.getPredirection();
            var postdirectionOption = hansenAcctvW.getPostdirection(sortedDirections);
            var suffixOption = hansenAcctvW.getSuffix(sortedDirections, sortedStreetTypes);


            var initialAddressExpressionOption = streetNameOption.bind(streetName =>
                houseNumberOption.bind(houseNumber =>
                    suffixOption.map<Expression<Func<Address, bool>>>(suffix => address =>
                        address.CASTNAME == streetName &&
                        address.CASTNO == houseNumber &&
                        address.CASUFFIX == suffix)));
            var predirectionLookup = predirectionOption.map<Expression<Func<Address, bool>>>(predirection =>
                address => address.CAPREDIR == predirection).getValueOrDefault(() =>
                    address => address.CAPREDIR == null || address.CAPREDIR == " ");
            var postdirectionLookup = postdirectionOption.map<Expression<Func<Address, bool>>>(postdirection =>
                address => address.CAPOSTDIR == postdirection).getValueOrDefault(() =>
                    address => address.CAPOSTDIR == null || address.CAPOSTDIR == " ");
            var subdesignationLookup = subdesignationOption.map<Expression<Func<Address, bool>>>(subdesignation =>
                address => address.CASTSUB == subdesignation).getValueOrDefault(() =>
                    address => address.CASTSUB == null || address.CASTSUB == " ");
            var postalCodeLookup = postalCodeOption.map<Expression<Func<Address, bool>>>(postalCode =>
                address => address.CAPOSTCODE == postalCode).getValueOrDefault(() =>
                    address => address.CAPOSTCODE == null || address.CAPOSTCODE == " ");
            Expression<Func<Address, bool>> cityLookup = address => address.CACITY == "REGINA";
            Expression<Func<Address, bool>> addressType = address => address.CAADDRTYPE == "A";
            Expression<Func<Address, bool>> addressKeyNot1 = address => address.ADDRKEY != 1;


            var otherExpressionList = new [] {
                predirectionLookup, postdirectionLookup, subdesignationLookup, postalCodeLookup, cityLookup,
                addressType, addressKeyNot1
            };


            return initialAddressExpressionOption.map(initialAddressExpression => {
                var foldedExpressionBody = otherExpressionList.Aggregate(initialAddressExpression.Body, (u, v) =>
                    Expression.AndAlso(u, v.Body));


                return Expression.Lambda<Func<Address, bool>>(foldedExpressionBody, initialAddressExpression.Parameters[0]);
            });
        }