Hi, I have a prefetch path that is very slow/not optimized. Basically I'd like to replace the sub-SELECT with an INNER JOIN. Below please find the tool set versions, the c# code used and the resulting SQL. When we processing a lot of claims we get a sql timeout (after 5 minutes).

I appreciate any help you can provide. Thanks!

Tool sets and versions. • Post the LLBLGen Pro version + buildnr. o Version 1.0.2005.1 Final (February 19th, 2006) • When it's a problem occurring at runtime, post the Runtime library version. o Support classes version 1.0.20051.60216 • When an exception is thrown, post the stacktrace. o n/a • Post the template group + .NET version you're using. o Generator configuration: Adapter scenario (Full / Safe) (1.0.2005.1.10232005) o Template set: C# template set for SQL Server (1.0.2005.1)(1.0.2005.1.111705) • Post the database type and version you're using. o SQL Server 9.0.4211

C# code:

public Collection<CmcClclClaimEntity> FetchClaims(int maximumNumberToFetch)
{
    RelationPredicateBucket claimsBucket = new RelationPredicateBucket();
    IPredicateExpression whichClaims = new PredicateExpression();
    whichClaims.Add(CmcClclClaimFields.MovedToPmpdatabase == MovedToPmpDatabaseIndicator.InProgress.Id);
    claimsBucket.PredicateExpression.Add(whichClaims);

    return FetchTypedList(new CmcClclClaimEntityFactory(), claimsBucket, GetEntityPrefetchPath(), null, maximumNumberToFetch);
}

private static IPrefetchPath2 GetEntityPrefetchPath()
{
    IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CmcClclClaimEntity);
    SortExpression sorter = new SortExpression(CmcClstStatusFields.ClstStsDtm | SortOperator.Ascending);
    prefetchPath.Add(CmcClclClaimEntity.PrefetchPathTblClststatus, 0, null, null, sorter);
    prefetchPath.Add(CmcClclClaimEntity.PrefetchPathTblCddlline);
    prefetchPath.Add(CmcClclClaimEntity.PrefetchPathTblCdmlline);

    return prefetchPath;
}

protected Collection<EntityType> FetchTypedList(IEntityFactory2 entityFactory,
    IRelationPredicateBucket filterBucket,
    IPrefetchPath2 prefetchPath,
    ISortExpression sortExpression,
    int maximumNumberToFetch)
{
    EntityCollection collection = FetchEntityCollection(entityFactory, 
        filterBucket, prefetchPath, sortExpression, maximumNumberToFetch);
    return EntityCollectionTranslator.TranslateEntityCollectionToList<EntityType>(collection);
}

protected EntityCollection FetchEntityCollection(IEntityFactory2 entityFactory,
 IRelationPredicateBucket filterBucket,
 IPrefetchPath2 prefetchPath, 
 ISortExpression sortExpression,
 int maximumNumberToFetch)
{
    if (entityFactory == null) throw new ArgumentNullException("entityFactory");
    //Null prefetchPath parameter is OK
    EntityCollection collection = new EntityCollection(entityFactory);

    DataAccessAdapter.CommandTimeOut = 600;
    try
    {
        DataAccessAdapter.FetchEntityCollection(collection, 
            filterBucket, 
            maximumNumberToFetch,
            sortExpression, 
            prefetchPath);
    }
    catch (ORMQueryExecutionException ex)
    {
        string exceptionMessage = string.Format(
            "An error occurred while attempting to fetch records from the database. Table: {0}",
            GetRelatedTableName());

        Log.For(this).Error(exceptionMessage, ex);
        throw new DataAccessException(exceptionMessage, ex);
    }

    return collection;
}

public static Collection<TEntity> TranslateEntityCollectionToList<TEntity> 
    (EntityCollectionBase2 collectionToTranslate)
{
    Collection<TEntity> returnList = new Collection<TEntity>();
    foreach (TEntity entity in collectionToTranslate)
    {
        returnList.Add(entity);
    }

    return returnList;
}

Resulting SQL: 1. I'm not posting the sql to get the main claim table (CmlClclClaimEntity). I captured the SQL using SQL Analyzer in SSMS while the application was running. This is the SQL to get the CmcClstStatus and the CmcCddlLine, which are the first 2 prefetch paths. the 3rd prefetch path would have a join pretty much the same as shown below.

@MovedToPmpdatabase1 varchar(1),@MovedToPmpdatabase2 varchar(1)',@MovedToPmpdatabase1='I',@MovedToPmpdatabase2='I'

SELECT [dbo].[tblCLSTStatus].[CLCL_ID] AS [ClclId], 
[dbo].[tblCLSTStatus].[CLCL_PAY_PR_IND] AS [ClclPayPrInd], 
[dbo].[tblCLSTStatus].[CLST_SEQ_NO] AS [ClstSeqNo], 
[dbo].[tblCLSTStatus].[CLST_MCTR_REAS] AS [ClstMctrReas], 
[dbo].[tblCLSTStatus].[CLST_STS] AS [ClstSts], 
[dbo].[tblCLSTStatus].[CLST_STS_DTM] AS [ClstStsDtm] 
FROM [dbo].[tblCLSTStatus]  
WHERE ( [dbo].[tblCLSTStatus].[CLCL_ID] 
   IN (SELECT [dbo].[tblFacetsClaims].[CLCL_ID] AS [ClclId] 
         FROM [dbo].[tblFacetsClaims]  
        WHERE ( ( ( ( [dbo].[tblFacetsClaims].[MovedToPMPDatabase] = @MovedToPmpdatabase1))))) 
  AND [dbo].[tblCLSTStatus].[CLCL_PAY_PR_IND] 
   IN (SELECT [dbo].[tblFacetsClaims].[CLCL_PAY_PR_IND] AS [ClclPayPrInd] 
         FROM [dbo].[tblFacetsClaims]  
        WHERE ( ( ( ( [dbo].[tblFacetsClaims].[MovedToPMPDatabase] = @MovedToPmpdatabase2)))))) 
     ORDER BY [dbo].[tblCLSTStatus].[CLST_STS_DTM] ASC


SELECT [dbo].[tblCDDLLine].[CLCL_ID] AS [ClclId], 
[dbo].[tblCDDLLine].[CLCL_PAY_PR_IND] AS [ClclPayPrInd], 
[dbo].[tblCDDLLine].[CDDL_SEQ_NO] AS [CddlSeqNo], 
[dbo].[tblCDDLLine].[CDDL_CAP_IND] AS [CddlCapInd], 
[dbo].[tblCDDLLine].[CDDL_COINS_AMT] AS [CddlCoinsAmt], 
[dbo].[tblCDDLLine].[CDDL_COPAY_AMT] AS [CddlCopayAmt], 
[dbo].[tblCDDLLine].[CDDL_DED_AMT] AS [CddlDedAmt], 
[dbo].[tblCDDLLine].[CDDL_PAID_AMT] AS [CddlPaidAmt], 
[dbo].[tblCDDLLine].[LOBD_ID] AS [LobdId], 
[dbo].[tblCDDLLine].[COBIndicator] AS [Cobindicator] 
FROM [dbo].[tblCDDLLine]  
WHERE ( [dbo].[tblCDDLLine].[CLCL_ID] 
   IN (SELECT [dbo].[tblFacetsClaims].[CLCL_ID] AS [ClclId] 
         FROM [dbo].[tblFacetsClaims]  
        WHERE ( ( ( ( [dbo].[tblFacetsClaims].[MovedToPMPDatabase] = @MovedToPmpdatabase1))))) 
  AND [dbo].[tblCDDLLine].[CLCL_PAY_PR_IND] 
   IN (SELECT [dbo].[tblFacetsClaims].[CLCL_PAY_PR_IND] AS [ClclPayPrInd] 
         FROM [dbo].[tblFacetsClaims]  
        WHERE ( ( ( ( [dbo].[tblFacetsClaims].[MovedToPMPDatabase] = @MovedToPmpdatabase2))))))

This is a cut and paste (mostly) verbatim from another thread. I didn't link to the thread because it's pretty long and complex. I have done a few projects with LLBLGen that involve complex prefetch paths on tables with composite keys, and have found that sometimes you need to change your approach slightly to improve performance.

Prefetch paths and composite keys don't always play nice together, especially if the composite PK tables are large. Often the subqueries can get complex, and as they get deeper, they can confuse SQL Server to the point where it's making bad choices for execution plans.

If this is indeed a composite PK/FK, and assuming that using some sort of surrogate key is undesirable (or not an option), one thing you could do is perform the main entity fetch, examine the results via code, then perform the deeper fetches "manually" with additional filtering. You might use a combination of shallow fetches and prefetch fetches, depending on how complex the deeper relationships are. You can then take the resulting entities and tie them together via code.

So essentially you are manually doing what prefetch paths normally do for you automatically, but making it more efficient by gearing it toward the specifics of your data/entity model. We do this in a number of places in our software where we have a deep graph fetch that involves large tables with composite keys.

If you are not using composite keys, ignore all of the above.

HTH,

Phil