I have a query that can return several thousand rows (and this may get bigger) and i'm currently using a prefetch with it that has started to cause errors.

Here is the query that gets executed:

Query Executed: Query: SELECT [dbo].[location].[LocationID], [dbo].[location].[ParentLocationID], [dbo].[location].[Location], [dbo].[location].[LocationTypeID], [dbo].[location].[ShortName], [dbo].[location].[LocationCode], [dbo].[location].[Description], [dbo].[location].[Active], [dbo].[location].[Visible], [dbo].[location].[lft] AS [Left], [dbo].[location].[rgt] AS [Rgt] FROM [dbo].[location] WHERE ( ( [dbo].[location].[LocationID] IN (@LocationID1, @LocationID2, @LocationID3, @LocationID4, @LocationID5, @LocationID6, @LocationID7, @LocationID8, [.....snip.......],@LocationID2259, @LocationID2260, @LocationID2261)))

as you can see, the prefetch query for the Locations contains over 2000 parameters and a large number of parameters degrades performance drastically.

Would it be possible to have the prefetch routines split up the queries into "chunks"?

Thanks, Michael.

ParameterisedPrefetchPathThreshold setting (search for it in the manual) is provided to enable you to tune prefetch.

Please have alook at it and feel free to come back.

i looked in the manual for that setting and a) i'm not really sure how i'm supposed to set it, and b) i haven't set it to anything outside the defaults so shouldn't the generated code be using a subquery instead of a list of parameters?

Michael,

This looks like an interesting problem. I could see this happening where a user can multi-select locations, or something like this. Could you explain your problem a little bit more.

Also i think the max params could be half of the total locations. If you put together some logic and use NOT IN. for example to select all locations but 1, you would use NOT IN on that one location.

As far as your second question

I think you can do this using the DataAccessAdapter.FetchEntityColleciton method. I'm pretty sure this method fills the collection (appends). Rather than overwrites. So you could just chunk the where clause into separate filters and FetchEntityColleciton several times

Gabe

maird wrote:

i looked in the manual for that setting and a) i'm not really sure how i'm supposed to set it, and b) i haven't set it to anything outside the defaults so shouldn't the generated code be using a subquery instead of a list of parameters?

Do you pass a large FieldCompareRangePredicate? If so, that won't work with a lot of values as each value is passed as a parameter.

i'm not using a CompareRangePredicate but I am using a CompareSetPredicate that uses a subquery.

The main query executes fine, but the above sql which comes from the prefetch to load the location objects blows up.

I'm using the self-servicing model and a [collection].GetMulti to load my primary objects so i'm not sure where i'm supposed to specify the ParameterisedPrefetchPathThreshold (or why the defaul isn't generating a subquery already).

I can post both my code and the generated SQL for my primary query if that would help...

maird wrote:

i'm not using a CompareRangePredicate but I am using a CompareSetPredicate that uses a subquery.

The main query executes fine, but the above sql which comes from the prefetch to load the location objects blows up.

I'm using the self-servicing model and a [collection].GetMulti to load my primary objects so i'm not sure where i'm supposed to specify the ParameterisedPrefetchPathThreshold (or why the defaul isn't generating a subquery already).

I can post both my code and the generated SQL for my primary query if that would help...

The code which performs the query is OK. We need as much info as possible to be able to reproduce it here. You don't have to post a 2200+ parameter query

The code which performs the query is OK. We need as much info as possible to be able to reproduce it here. You don't have to post a 2200+ parameter query Wink

ok.

Here is the generated SQL for the primary query

    Query: SELECT TOP 2147483647 [dbo].[incident].[IncidentID], [dbo].[incident].[IncidentNumber], [dbo].[incident].[IncidentCategoryID], [dbo].[incident].[IncidentStatusID], [dbo].[incident].[Title], [dbo].[incident].[ReportDate], [dbo].[incident].[ReporterID], [dbo].[incident].[ReporterPhone], [dbo].[incident].[IncidentDate], [dbo].[incident].[InvestigationStartDate], [dbo].[incident].[LocationID], [dbo].[incident].[OtherLocation], [dbo].[incident].[ProcessID], [dbo].[incident].[OtherProcess], [dbo].[incident].[Equipment], [dbo].[incident].[Description], [dbo].[incident].[DescriptionLong], [dbo].[incident].[ActivityDuringIncident], [dbo].[incident].[ImmediateAction], [dbo].[incident].[ImmediateCauses], [dbo].[incident].[BestPractices], [dbo].[incident].[InsuranceNotified], [dbo].[incident].[InsuranceNotifiedDate], [dbo].[incident].[InsuranceClaim], [dbo].[incident].[InsuranceInfo], [dbo].[incident].[Preventable], [dbo].[incident].[DrugTestPerformed], [dbo].[incident].[Recurrence], [dbo].[incident].[OnPremises], [dbo].[incident].[OffPremisesLocation], [dbo].[incident].[ActivityID], [dbo].[incident].[OtherActivity], [dbo].[incident].[ContractingCompanyID], [dbo].[incident].[OtherContractingCompany], [dbo].[incident].[PPEWorn], [dbo].[incident].[OtherPPE], [dbo].[incident].[IncidentLevelID], [dbo].[incident].[RiskTypeID], [dbo].[incident].[SeverityID], [dbo].[incident].[ProbabilityID], [dbo].[incident].[RiskLevelID], [dbo].[incident].[Downtime], [dbo].[incident].[DowntimeAmount], [dbo].[incident].[DowntimeUnitCode], [dbo].[incident].[RCAPerformed], [dbo].[incident].[TitleV], [dbo].[incident].[Active], [dbo].[incident].[Visible], [dbo].[incident].[HistoryID], [dbo].[incident].[ExtraFields] FROM [dbo].[incident]  WHERE ( ( ( [dbo].[incident].[IncidentNumber] LIKE @IncidentNumber1) AND [dbo].[incident].[IncidentID] IN (SELECT [dbo].[incident].[IncidentID] FROM (((((( [dbo].[listitem] [LPA_r2]  INNER JOIN [dbo].[role_has_permission]  ON  [LPA_r2].[ListItemID]=[dbo].[role_has_permission].[RoleID] AND ( [dbo].[role_has_permission].[PermissionID] = @PermissionID2)) LEFT JOIN [dbo].[location_has_role] [LPA_l3]  ON  [LPA_r2].[ListItemID]=[LPA_l3].[RoleID] AND ( [LPA_l3].[PersonID] = @PersonID3)) LEFT JOIN [dbo].[incident_has_role] [LPA_i1]  ON  [LPA_r2].[ListItemID]=[LPA_i1].[RoleID] AND ( [LPA_i1].[PersonID] = @PersonID4)) LEFT JOIN [dbo].[location_has_role_qualifier] [LPA_l5]  ON  [LPA_l5].[LocationHasRoleID]=[LPA_l3].[ID]) RIGHT JOIN [dbo].[role_has_permission_qualifier]  ON  [dbo].[role_has_permission].[ID]=[dbo].[role_has_permission_qualifier].[RoleHasPermissionID]) LEFT JOIN [dbo].[location] [LPA_l4]  ON  [LPA_l4].[LocationID]=[LPA_l3].[LocationID]) WHERE ( ( ( [dbo].[incident].[ActivityID] = [LPA_l5].[QualifierID] OR [LPA_l5].[QualifierID] = @QualifierID5) AND ( [dbo].[incident].[IncidentStatusID] = [dbo].[role_has_permission_qualifier].[QualifierID] OR [dbo].[role_has_permission_qualifier].[QualifierID] IS NULL) AND [dbo].[incident].[LocationID] IN (SELECT [dbo].[location].[LocationID] FROM [dbo].[location]  WHERE ( [dbo].[location].[lft] >= [LPA_l4].[lft] AND [dbo].[location].[rgt] <= [LPA_l4].[rgt]))) OR ( [dbo].[incident].[IncidentID] = [LPA_i1].[IncidentID] AND ( [dbo].[incident].[IncidentStatusID] = [dbo].[role_has_permission_qualifier].[QualifierID] OR [dbo].[role_has_permission_qualifier].[QualifierID] IS NULL))))))
    Parameter: @IncidentNumber1 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: %2%.
    Parameter: @PermissionID2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 463.
    Parameter: @PersonID3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0b990ccd-3a30-4a5d-a0c1-545126a52c18.
    Parameter: @PersonID4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0b990ccd-3a30-4a5d-a0c1-545126a52c18.
    Parameter: @QualifierID5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 443.

this executes fine and returns several thousand rows. The problem comes when the prefetch tries load the assocatied locations and generates SQL like this:

    Query: SELECT [dbo].[location].[LocationID], [dbo].[location].[ParentLocationID], [dbo].[location].[Location], [dbo].[location].[LocationTypeID], [dbo].[location].[ShortName], [dbo].[location].[LocationCode], [dbo].[location].[Description], [dbo].[location].[Active], [dbo].[location].[Visible], [dbo].[location].[lft] AS [Left], [dbo].[location].[rgt] AS [Rgt] FROM [dbo].[location]  WHERE ( ( [dbo].[location].[LocationID] IN (@LocationID1, @LocationID2, @LocationID3, @LocationID4, @LocationID5, @LocationID6, @LocationID7, @LocationID8, @LocationID9, @LocationID10, @LocationID11, @LocationID12, @LocationID13, 
[....SNIP...]
 @LocationID5199)))

any thoughts?

Please post the CODE which you wrote to execute these filters, that would give us insight in exactly which prefetch paths are executed with which filters.

OK. here we go. this is the code on the search page:

                        
selectFilter = new PredicateExpression();

                        selectFilter.Add(PredicateFactory.Like(IncidentFieldIndex.IncidentNumber, "%" + strQS + "%"));

                        //make the prefetch path
                        IPrefetchPath prefetch = new PrefetchPath((int)EntityType.IncidentEntity);
                        prefetch.Add(IncidentEntity.PrefetchPathLocation);
                        prefetch.Add(IncidentEntity.PrefetchPathIncidentStatus);
                        prefetch.Add(IncidentEntity.PrefetchPathIncidentCategory);

                        IncidentCollection ic = IncidentUtilities.GetIncidentsWithPermission(currentUser, Utils.GetListItem("permission", "VIEW-INC"), selectFilter, null, null, int.MaxValue,1, prefetch);

which calls GetIncidentsWithPermission:

            public static IncidentCollection GetIncidentsWithPermission(PersonEntity person, ListItemEntity permission, IPredicateExpression selectFilter, IRelationCollection relations, ISortExpression sorter, int maximumRows, int startRowIndex, IPrefetchPath prefetch)
            {
                 System.Diagnostics.Trace.Write("Entering " + System.Reflection.MethodBase.GetCurrentMethod().Name, "IncidentUtilities");

                 IncidentCollection allIncidents = new IncidentCollection();
                 IPredicateExpression localSelectFilter = new PredicateExpression();
                 localSelectFilter.Add(selectFilter);
                 localSelectFilter.AddWithAnd(GetIncidentsWithPermissionFilter(person, permission));

                 //return the list

                 allIncidents.GetMulti(localSelectFilter, maximumRows, sorter, relations, prefetch, (startRowIndex / maximumRows) + 1, maximumRows);

                 System.Diagnostics.Trace.Write("Exiting " + System.Reflection.MethodBase.GetCurrentMethod().Name, "IncidentUtilities");
                 return allIncidents;

            }

the really nasty one is GetIncidentsWithPermissionFilter :

public static FieldCompareSetPredicate GetIncidentsWithPermissionFilter(PersonEntity person, ListItemEntity permission)
            {
                 IRelationCollection primaryRelations = new RelationCollection();
                 primaryRelations.ObeyWeakRelations = true;
                 primaryRelations.Add(Role_Has_PermissionEntity.Relations.ListItemEntityUsingRoleID, "role").CustomFilter = new PredicateExpression(Role_Has_PermissionFields.PermissionID == permission.ListItemID);
                 primaryRelations.Add(ListItemEntity.Relations.Location_Has_RoleEntityUsingRoleID, "role", "lhr", JoinHint.Left).CustomFilter = new PredicateExpression(Location_Has_RoleFields.PersonID.SetObjectAlias("lhr") == person.PersonID);
                 primaryRelations.Add(ListItemEntity.Relations.Incident_Has_RoleEntityUsingRoleID, "role", "ihr", JoinHint.Left).CustomFilter = new PredicateExpression(Incident_Has_RoleFields.PersonID.SetObjectAlias("ihr") == person.PersonID);

                 //custom relation for lhr to lhrq
                 IEntityRelation lhrTolhrqRelation = new EntityRelation(RelationType.OneToOne);
                 lhrTolhrqRelation.AddEntityFieldPair(EntityFieldFactory.Create(Location_Has_Role_QualifierFieldIndex.LocationHasRoleID), EntityFieldFactory.Create(Location_Has_RoleFieldIndex.ID));

                 primaryRelations.Add(lhrTolhrqRelation, "lhr", "lhrq", JoinHint.Left);

                 IEntityRelation rhpTorhpqRelation = new EntityRelation(RelationType.OneToMany);
                 rhpTorhpqRelation.AddEntityFieldPair(EntityFieldFactory.Create(Role_Has_PermissionFieldIndex.RoleHasPermissionID), EntityFieldFactory.Create(Role_Has_Permission_QualifierFieldIndex.RoleHasPermissionID));

                 primaryRelations.Add(rhpTorhpqRelation, JoinHint.Left);

                 primaryRelations.Add(Location_Has_RoleEntity.Relations.LocationEntityUsingLocationID, "lhr", "loc", JoinHint.Left);

                 IPredicateExpression locationFilter = new PredicateExpression();

                 IPredicateExpression activityFilter = new PredicateExpression();
                 activityFilter.Add(IncidentFields.ActivityID == Location_Has_Role_QualifierFields.QualifierID.SetObjectAlias("lhrq"));
                 activityFilter.AddWithOr(Location_Has_Role_QualifierFields.QualifierID.SetObjectAlias("lhrq") == Utils.GetListItem("activity", "ALL").ListItemID);

                 locationFilter.Add(activityFilter);

                 IPredicateExpression statusFilter = new PredicateExpression();
                 statusFilter.Add(IncidentFields.IncidentStatusID == Role_Has_Permission_QualifierFields.QualifierID);
                 statusFilter.AddWithOr(PredicateFactory.CompareNull(Role_Has_Permission_QualifierFieldIndex.QualifierID));

                 locationFilter.AddWithAnd(statusFilter);

                 IPredicateExpression incidentLocationJoin = new PredicateExpression();
                 incidentLocationJoin.Add(LocationFields.Left >= LocationFields.Left.SetObjectAlias("loc"));
                 incidentLocationJoin.Add(LocationFields.Rgt <= LocationFields.Rgt.SetObjectAlias("loc"));

                 locationFilter.AddWithAnd(new FieldCompareSetPredicate(IncidentFields.LocationID, LocationFields.LocationID, SetOperator.In, incidentLocationJoin));

                 IPredicateExpression roleFilter = new PredicateExpression();
                 roleFilter.Add(IncidentFields.IncidentID == Incident_Has_RoleFields.IncidentID.SetObjectAlias("ihr"));

                 IPredicateExpression roleStatusFilter = new PredicateExpression();
                 roleStatusFilter.Add(IncidentFields.IncidentStatusID == Role_Has_Permission_QualifierFields.QualifierID);
                 roleStatusFilter.AddWithOr(PredicateFactory.CompareNull(Role_Has_Permission_QualifierFieldIndex.QualifierID));

                 roleFilter.AddWithAnd(roleStatusFilter);

                 IPredicateExpression fullFilter = new PredicateExpression();

                 fullFilter.Add(locationFilter);
                 fullFilter.AddWithOr(roleFilter);

                 FieldCompareSetPredicate test = new FieldCompareSetPredicate(IncidentFields.IncidentID, IncidentFields.IncidentID, SetOperator.In, fullFilter, primaryRelations);

                 return test;
            }

Let me know if you have any ideas...

The thing is that the prefetch path fetch logic has 2 main routines: one which is called when the # of root entities (returned by your first query) is <= the treshold set, and one which uses a subquery with the filter used for the root entity fetch.

The first one uses an optimization where it produces an IN (var, var, var...) filter, the one which fails. The second one doesn't, it uses a subquery.

If you've set: DaoBase.ParameterisedPrefetchPathThreshold to a value of say 4000, it will opt for the first routine if the # of root entities is <= 4000. The value in DaoBase.ParameterisedPrefetchPathThreshold is static, which means, if you set it ONCE, it will stay that value till you set it again to a different value. Please check your application code if that variable is set to a value somewhere.

Your code seems fine. When you get the exception, a stacktrace is produced as well. In the stacktrace (which you didn't post, though which would be very helpful), does it have solely a call to DaoBase.FetchPrefetchPath, or does it first call DaoBase.FetchPrefetchPath and then call DaoBase.FetchParameterisedPrefetchPath ?

that's the wierd thing. i don't set DaoBase.ParameterisedPrefetchPathThreshold anywhere. unless it was hardcoced into the generated code, but i doubt that....

here's the stack trace:

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: A severe error occurred on the current command.  The results, if any, should be discarded.
A severe error occurred on the current command.  The results, if any, should be discarded.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +169
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IValidator validatorToUse, IEntityFields fieldsUsedForQuery) +389
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IValidator validatorToUse, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +272
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +183
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.FetchParameterisedPrefetchPath(IEntityCollection rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath prefetchPath, ITransaction containingTransaction) +3886
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IValidator validatorToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, Int32 pageNumber, Int32 pageSize) +96
   SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, Int32 pageNumber, Int32 pageSize) +173
   KMI.IncidentUtilities.GetIncidentsWithPermission(PersonEntity person, ListItemEntity permission, IPredicateExpression selectFilter, IRelationCollection relations, ISortExpression sorter, Int32 maximumRows, Int32 startRowIndex, IPrefetchPath prefetch) in web\App_Code\IncidentUtilities.cs:479
   KMI.Search.Page_Load(Object sender, EventArgs e) in web\Search.aspx.cs:45
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Ah, I see what's wrong. You also want paging AND prefetch paths.

That's only going to work if parameterized prefetch paths can be used, e.g. with IN(val, val, val .... ) queries. THe reason for this is that the fetch for the prefetch path subnode (in this case location), requires a filter of the parent to select the subnode entities (in this case the filter used to fetch incidents). Paging requires an additional exponent to the filter: a given subset of the data to fetch. This is normally working OK, as pages aren't very big, otherwise why use paging then?

Ok, looking at your code, I have the feeling your page size is really big. Do you need paging? if not, use another overload, if you do, specify a smaller page size (e.g. 100).

Ahhhh. that makes sense. i use this method from pages that require paging and others that don't. since this is now returning thousands of records, i should probably add paging to it and cut down the size of what's returned.

thanks for your help!