LLBLGen Version: 2.6 Final (October 9th, 2009) Runtime libary version: 2.6.9.1202 Template group: Adapter Target platform: .NET 3.5 Selected preset: SD.Presets.Adapter.General2008 Template binding sorted on precedence: SD.TemplateBindings.SharedTemplates.NET20, SD.TemplateBinding.OracleSpecific.NET20, SD.TemplateBinding.Linq, SD.TemplateBindings.SharedTemplates.BackwardsCompatibility.NET20 Database: Oracle 9i

I get slow performance on all prefetch paths where compound keys are used.

An example:

/// <summary>
        /// Gets the material for a suborder.
        /// </summary>
        /// <param name="suborder"></param>
        /// <returns></returns>
        public Material GetMaterial(Suborder suborder)
        {
            Material material = null;

            PropertyValue materialNamePropertyValue = suborder.PropertyValues.FirstOrDefault(pv => pv.Property.ID == PROPERTY_MATERIALNAME);
            PropertyValue materialVersionPropertyValue = suborder.PropertyValues.FirstOrDefault(pv => pv.Property.ID == PROPERTY_MATERIALVERSION);

            if (materialNamePropertyValue != null && materialVersionPropertyValue != null && !string.IsNullOrEmpty(materialNamePropertyValue.Value) && !string.IsNullOrEmpty(materialVersionPropertyValue.Value))
                material = new MaterialConverter().FetchOrConvert(ServiceAgentFactory.Instance.GetMasterDataManagementServiceAgent().GetMaterial(materialNamePropertyValue.Value, materialVersionPropertyValue.Value), transaction.ConvertedObjects);

            return material;
        }

        public IList<Suborder> GetByCriteria(long suborderTypeID, States stateID, long userID, PrefetchPathType prefetchPathType)
        {
            IList<Suborder> suborders = new List<Suborder>();
            LinqMetaData linqMetaData = new LinqMetaData(this.transaction.DataAccessAdapter);
            
            IPrefetchPath2 path = null;
            if (prefetchPathType == PrefetchPathType.Full)
            {
                path = ConstructPrefetchPath();
            }
            else if (prefetchPathType == PrefetchPathType.Minimal)
            {
                path = ConstructMinimalPrefetchPath();
            }
            IQueryable<SuborderEntity> suborderQuery =
                linqMetaData.Suborder.WithPath(path);

            if (suborderTypeID > 0)
            {
                suborderQuery = suborderQuery.Where(s => s.Subordertypeid == suborderTypeID);
            }

            if (stateID > 0)
            {
                suborderQuery = suborderQuery.Where(s => s.Stateid == stateID.ToLong());
            }

            IDictionary<long, User> users = new Dictionary<long, User>();
            UserConverter userConverter = new UserConverter();

            if (userID > 0)
            {
                suborderQuery = suborderQuery.Where(s => s.Order.Userid == userID);
                // only retrieve this user
                users.Add(userID, userConverter.FetchOrConvert(ServiceAgentFactory.Instance
                    .GetMasterDataManagementServiceAgent().GetUser(userID, PrefetchPathType.Minimal), transaction.ConvertedObjects));
            }
            else
            {
                // retrieve all users
                foreach (User user in userConverter.FetchOrConvert(ServiceAgentFactory.Instance
                    .GetMasterDataManagementServiceAgent().GetUsers(PrefetchPathType.Minimal), transaction.ConvertedObjects))
                    users.Add(user.ID, user);
            }

            AccountConverter accountConverter = new AccountConverter();
            IEnumerable<long> accountsToFetch = suborderQuery
                .Where(s => s.Order.Accountid.HasValue)
                .Select(s => s.Order.Accountid.Value).Distinct();
            List<Account> accounts = accountConverter.FetchOrConvert(
                ServiceAgentFactory.Instance.GetMasterDataManagementServiceAgent()
                .GetAccounts(accountsToFetch, prefetchPathType), transaction.ConvertedObjects).ToList();

            IDictionary<ItemsSources, IList<PropertyItem>> externalPropertyItems = new Dictionary<ItemsSources, IList<PropertyItem>>();
            ExternalPropertyItemConverter externalPropertyItemConverter = new ExternalPropertyItemConverter();

            SuborderConverter suborderConverter = new SuborderConverter();

            foreach (SuborderEntity suborderEntity in suborderQuery)
            {
                Suborder suborder = suborderConverter.FetchOrConvert(suborderEntity, transaction.ConvertedObjects);
                suborder.Order.User = users[suborder.Order.User.ID];
                suborder.Order.Account = accounts.SingleOrDefault(a => a.ID == suborder.Order.Account.ID);
                                
                foreach (SuborderPropertyGroup suborderPropertyGroup in suborder.SuborderPropertyGroups)
                {
                    foreach (SuborderProperty suborderProperty in suborderPropertyGroup.SuborderProperties)
                    {
                        if (suborderProperty.Property != null && suborderProperty.Property.ItemsSource.HasValue)
                        {
                            if (!externalPropertyItems.ContainsKey((ItemsSources)suborderProperty.Property.ItemsSource))
                            {
                                suborderProperty.Property.Items = externalPropertyItemConverter.FetchOrConvert(
                                    ServiceAgentFactory.Instance.GetMasterDataManagementServiceAgent()
                                    .GetLookuplist((masterdata.LookupList)suborderProperty.Property.ItemsSource),
                                    transaction.ConvertedObjects).ToList().ConvertAll<PropertyItem>(i => (PropertyItem)i);
                                externalPropertyItems.Add((ItemsSources)suborderProperty.Property.ItemsSource, suborderProperty.Property.Items);
                            }
                        }
                    }
                }
                suborders.Add(suborder);
            }

            return suborders;
        }

private IPrefetchPath2 ConstructMinimalPrefetchPath()
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SuborderEntity);
            prefetchPath.Add(SuborderEntity.PrefetchPathDatasource);
            prefetchPath.Add(SuborderEntity.PrefetchPathOrder);
            prefetchPath.Add(SuborderEntity.PrefetchPathVerificationitemstatus).SubPath.Add(VerificationitemstatusEntity.PrefetchPathVerificationitem);
            prefetchPath.Add(SuborderEntity.PrefetchPathState);
            IPrefetchPathElement2 suborderTypePrefetchPath = prefetchPath.Add(SuborderEntity.PrefetchPathSubordertype);

            IPrefetchPathElement2 typepropertyhighlightPrefetchPath = suborderTypePrefetchPath.SubPath.Add(SubordertypeEntity.PrefetchPathTypepropertyhighlight);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathPropertygroup);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathProperty);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathPropertyitem);

            IPrefetchPathElement2 suborderPropGroupLinkPath = prefetchPath.Add(SuborderEntity.PrefetchPathSuborderpropgrouplink);
            suborderPropGroupLinkPath.SubPath.Add(SuborderpropgrouplinkEntity.PrefetchPathPropertygroup);

            IPrefetchPathElement2 suborderPropertyLinkPath = suborderPropGroupLinkPath.SubPath.Add(SuborderpropgrouplinkEntity.PrefetchPathSuborderpropertylink);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathProperty);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathPropertygroup);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathSuborder);

            IPrefetchPathElement2 propertyValuePrefetchPath = prefetchPath.Add(SuborderEntity.PrefetchPathPropertygroupvalue).SubPath.Add(PropertygroupvalueEntity.PrefetchPathPropertyvalue);
            propertyValuePrefetchPath.SubPath.Add(PropertyvalueEntity.PrefetchPathProperty);

            return prefetchPath;
        }

Primary keys table ORD_SUBORDERPROPERTYLINK: SUBORDERID, PROPERTGROUPID, PROPERTYID Primary keys table ORD_SUBORDERPROPGROUPLINK: SUBORDERID, PROPERTYGROUPID

What I see in the generated SQL is the following: SELECT "ORDMGR"."ORD_SUBORDER"."ID" AS "Id", "ORDMGR"."ORD_SUBORDER"."ORDERID" AS "Orderid", "ORDMGR"."ORD_SUBORDER"."SUBORDERTYPEID" AS "Subordertypeid", "ORDMGR"."ORD_SUBORDER"."CUSTOMERREFERENCE" AS "Customerreference", "ORDMGR"."ORD_SUBORDER"."STATEID" AS "Stateid", "ORDMGR"."ORD_SUBORDER"."REMARKFROMREQUESTER" AS "Remarkfromrequester", "ORDMGR"."ORD_SUBORDER"."REQUESTEDDUEDATE" AS "Requestedduedate", "ORDMGR"."ORD_SUBORDER"."CONFIRMEDDUEDATE" AS "Confirmedduedate", "ORDMGR"."ORD_SUBORDER"."INSERTTIMESTAMP" AS "Inserttimestamp", "ORDMGR"."ORD_SUBORDER"."UPDATETIMESTAMP" AS "Updatetimestamp", "ORDMGR"."ORD_SUBORDER"."CLOSEDATETIME" AS "Closedatetime", "ORDMGR"."ORD_SUBORDER"."DATASOURCEID" AS "Datasourceid" FROM "ORDMGR"."ORD_SUBORDER" WHERE ("ORDMGR"."ORD_SUBORDER"."ID" IN ( SELECT "ORDMGR"."ORD_SUBORDERPROPERTYLINK"."SUBORDERID" AS "Suborderid" FROM "ORDMGR"."ORD_SUBORDERPROPERTYLINK" WHERE (( "ORDMGR"."ORD_SUBORDERPROPERTYLINK"."SUBORDERID" IN ( SELECT "ORDMGR"."ORD_SUBORDERPROPGROUPLINK"."SUBORDERID" AS "Suborderid" FROM "ORDMGR"."ORD_SUBORDERPROPGROUPLINK" WHERE (("ORDMGR"."ORD_SUBORDERPROPGROUPLINK"."SUBORDERID" IN ( SELECT "LPLA_1"."ID" AS "Id" FROM "ORDMGR"."ORD_SUBORDER" "LPLA_1" WHERE (((( (("LPLA_1"."SUBORDERTYPEID" = :subordertypeid1 ) ) AND ("LPLA_1"."STATEID" = :stateid2 ) ) ) ) )) ) )) AND "ORDMGR"."ORD_SUBORDERPROPERTYLINK"."PROPERTYGROUPID" IN ( SELECT "ORDMGR"."ORD_SUBORDERPROPGROUPLINK"."PROPERTYGROUPID" AS "Propertygroupid" FROM "ORDMGR"."ORD_SUBORDERPROPGROUPLINK" WHERE (("ORDMGR"."ORD_SUBORDERPROPGROUPLINK"."SUBORDERID" IN ( SELECT "LPLA_1"."ID" AS "Id" FROM "ORDMGR"."ORD_SUBORDER" "LPLA_1" WHERE (((( (("LPLA_1"."SUBORDERTYPEID" = :subordertypeid3 ) ) AND ("LPLA_1"."STATEID" = :stateid4 ) ) ) ) )) ) )) ) )) )

The query does not combine suborderpropertylink suborderid, propertygroupid to retrieve related suborderpropertygrouplink records. Instead LLBLGen creates 2 seperate subqueries one for the relation via suborderid and one for the relation via propertygroupid. This query takes forever.

Is there a way to make use of the compound key relation to suborderpropertygrouplink without having LLBLGen generating two separate subqueries?

Please help!

Please try the ParameterizedPrefetchPathThreshold flag as indicated here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14326

Also please use the latest release of v.2.6 (as you are using a rather old one).

Hi Walaa,

Thanks for you fast reply. I followed your advice and upgraded to latest release 2.6.11.415. I do see speed improvement in simple queries.

I also removed all irrelevant code for clarity:

       public IList<Suborder> GetByCriteria(long suborderTypeID, States stateID, long userID, PrefetchPathType prefetchPathType)
        {
            IList<Suborder> suborders = new List<Suborder>();
            LinqMetaData linqMetaData = new LinqMetaData(this.transaction.DataAccessAdapter);
            
            IQueryable<SuborderEntity> suborderQuery = linqMetaData.Suborder.WithPath(ConstructMinimalPrefetchPath());
            suborderQuery = suborderQuery.Where(s => s.Subordertypeid == suborderTypeID);
            suborderQuery = suborderQuery.Where(s => s.Stateid == stateID.ToLong());
            
            SuborderConverter suborderConverter = new SuborderConverter();

            foreach (SuborderEntity suborderEntity in suborderQuery)
            {
            }

            return suborders;
        }

        private IPrefetchPath2 ConstructMinimalPrefetchPath()
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SuborderEntity);
            prefetchPath.Add(SuborderEntity.PrefetchPathDatasource);
            prefetchPath.Add(SuborderEntity.PrefetchPathOrder);
            prefetchPath.Add(SuborderEntity.PrefetchPathVerificationitemstatus).SubPath.Add(VerificationitemstatusEntity.PrefetchPathVerificationitem);
            prefetchPath.Add(SuborderEntity.PrefetchPathState);
            IPrefetchPathElement2 suborderTypePrefetchPath = prefetchPath.Add(SuborderEntity.PrefetchPathSubordertype);

            IPrefetchPathElement2 typepropertyhighlightPrefetchPath = suborderTypePrefetchPath.SubPath.Add(SubordertypeEntity.PrefetchPathTypepropertyhighlight);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathPropertygroup);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathProperty);
            typepropertyhighlightPrefetchPath.SubPath.Add(TypepropertyhighlightEntity.PrefetchPathPropertyitem);

            IPrefetchPathElement2 suborderPropGroupLinkPath = prefetchPath.Add(SuborderEntity.PrefetchPathSuborderpropgrouplink);
            suborderPropGroupLinkPath.SubPath.Add(SuborderpropgrouplinkEntity.PrefetchPathPropertygroup);

            IPrefetchPathElement2 suborderPropertyLinkPath = suborderPropGroupLinkPath.SubPath.Add(SuborderpropgrouplinkEntity.PrefetchPathSuborderpropertylink);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathProperty);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathPropertygroup);
            suborderPropertyLinkPath.SubPath.Add(SuborderpropertylinkEntity.PrefetchPathSuborder);

            IPrefetchPathElement2 propertyValuePrefetchPath = prefetchPath.Add(SuborderEntity.PrefetchPathPropertygroupvalue).SubPath.Add(PropertygroupvalueEntity.PrefetchPathPropertyvalue);
            propertyValuePrefetchPath.SubPath.Add(PropertyvalueEntity.PrefetchPathProperty);

            return prefetchPath;
        }

My result contains 3221 records and exceeds the max allowed parameters (1000) that Oracle accepts. So it is not an option for me. Is there an other way?

Is there some underlying reason why the 2 additional sub-queries take a long time to run ? Is it related to the volume of data in the database, or is it something that could perhaps be address with some database index tuning ?

Matt

Also, you add a lot of sub path nodes. Remember that for each sub node a query is executed, and the results are merged in-memory. This is in general not slow, but it can be your query is returning more rows than necessary, e.g. in situations where you specify a limit on the sub node (e.g. 10 for the last 10 rows), with or without sorting. The threshold can still be valuable for the queries in the sub nodes, as it's used with every sub node query.

A compound key is problematic however, as the sub query is executed for every field in the compound key (see the generated SQL). The problem is mainly: you can't compare tuples in SQL. So you have a set of tuples (the values for the compound key in the parent objects, whether this is the pk or fk doesn't matter) and if a tuple in the set to fetch matches any of the tuples in the set already fetched (which is supplied by a subquery), it's a candidate to return.

We don't use an exist query here, which might speed up the overall query a bit I think. We'll look into whether we can issue an exists query here, although we do have to do some analysis whether this will result in the same resultset.

so the current query looks something like: SELECT childfields... FROM childtable WHERE childCompoundKeyField1 IN ( SELECT parentCompoundKeyField1 FROM parent WHERE parentfilter) AND childCompoundKeyField2 IN ( SELECT parentCompoundKeyField2 FROM parent WHERE parentfilter)

etc.

an exists query could be: SELECT childfields FROM childtable WHERE EXISTS (SELECT * FROM parent WHERE parentfilter AND (childtable.CompoundKeyField1 = parent.CompoundKeyField1 AND childtable.CompoundKeyField2 = parent.CompoundKeyField2))

It might look like a no-brainer, but this filter is re-used in the fetch for the children of 'child' and other things also might cause different results for this, hence we've to look into whether this indeed works.

ok, tests on v3.1 show good results. We'll now backport this change to v2.6 and v3.0 runtimes and will attach a v2.6 runtime build to this thread for you to test.

See the attached zip. It contains for v2.6, v3.0 and v3.1 the ORMSupportClasses dll which changes the multiple IN clauses into an EXISTS query.

What I'd like to know from you is whether this fixes the issue or not or it creates other edge cases. Thanks in advance.

Hi Otis,

Many thanks. I will test the project with the supplied ORMSupportClasses dll and when finished give you an update of my findings.