[EDIT: Sorry for the bad title, but I can't change it now anymore ]

I had major trouble with a query that (all of a sudden) was not being executed. So I started looking into the code and I found the query that is causing all the trouble (I reformated it for better reading, e.g. removed the database name):

Generated Sql query: 
    Query: SELECT DISTINCT [LiveTeamGameEvent].[SessionId],
    [LiveTeamGameEvent].[GameId],
    [LiveTeamGameEvent].[EventId],
    [LiveTeamGameEvent].[TeamSi],
    [LiveTeamGameEvent].[RunningScore],
    [LiveTeamGameEvent].[CourtX],
    [LiveTeamGameEvent].[CourtY]
    FROM [LiveTeamGameEvent] 
    WHERE ([LiveTeamGameEvent].[SessionId] IN (
        SELECT [LiveGameEvent].[SessionId]
        FROM [LiveGameEvent]
        WHERE (((
            ([LiveGameEvent].[SessionId] = @SessionId1
                AND [LiveGameEvent].[GameId] = @GameId2
                AND [LiveGameEvent].[Period] = @Period3)
            OR ([LiveGameEvent].[SessionId] = @SessionId4
                AND [LiveGameEvent].[GameId] = @GameId5
                AND [LiveGameEvent].[Period] = @Period6)
            OR ( [LiveGameEvent].[SessionId] = @SessionId7
                AND [LiveGameEvent].[GameId] = @GameId8
                AND [LiveGameEvent].[Period] = @Period9)
            OR ( [LiveGameEvent].[SessionId] = @SessionId10
                AND [LiveGameEvent].[GameId] = @GameId11
                AND [LiveGameEvent].[Period] = @Period12)
            OR ( [LiveGameEvent].[SessionId] = @SessionId13
                AND [LiveGameEvent].[GameId] = @GameId14
                AND [LiveGameEvent].[Period] = @Period15))))
    )
    AND [LiveTeamGameEvent].[GameId] IN (
        SELECT [LiveGameEvent].[GameId]
        FROM [LiveGameEvent]  WHERE ( ( ( (
                [LiveGameEvent].[SessionId] = @SessionId16
                AND [LiveGameEvent].[GameId] = @GameId17
                AND [LiveGameEvent].[Period] = @Period18)
            OR ( [LiveGameEvent].[SessionId] = @SessionId19
                AND [LiveGameEvent].[GameId] = @GameId20
                AND [LiveGameEvent].[Period] = @Period21)
            OR ( [LiveGameEvent].[SessionId] = @SessionId22
                AND [LiveGameEvent].[GameId] = @GameId23
                AND [LiveGameEvent].[Period] = @Period24)
            OR ( [LiveGameEvent].[SessionId] = @SessionId25
                AND [LiveGameEvent].[GameId] = @GameId26
                AND [LiveGameEvent].[Period] = @Period27)
            OR ( [LiveGameEvent].[SessionId] = @SessionId28
                AND [LiveGameEvent].[GameId] = @GameId29
                AND [LiveGameEvent].[Period] = @Period30))))
    )
    AND [LiveTeamGameEvent].[EventId] IN (
        SELECT [LiveGameEvent].[EventId]
        FROM [LiveGameEvent] 
        WHERE ((((
                [LiveGameEvent].[SessionId] = @SessionId31
                AND [LiveGameEvent].[GameId] = @GameId32
                AND [LiveGameEvent].[Period] = @Period33)
            OR ( [LiveGameEvent].[SessionId] = @SessionId34
                AND [LiveGameEvent].[GameId] = @GameId35
                AND [LiveGameEvent].[Period] = @Period36)
            OR ( [LiveGameEvent].[SessionId] = @SessionId37
                AND [LiveGameEvent].[GameId] = @GameId38
                AND [LiveGameEvent].[Period] = @Period39)
            OR ( [LiveGameEvent].[SessionId] = @SessionId40
                AND [LiveGameEvent].[GameId] = @GameId41
                AND [LiveGameEvent].[Period] = @Period42)
            OR ( [LiveGameEvent].[SessionId] = @SessionId43
                AND [LiveGameEvent].[GameId] = @GameId44
                AND [LiveGameEvent].[Period] = @Period45))))
    ))
    Parameter: @SessionId1 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period3 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 3.
    Parameter: @SessionId4 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period6 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 2.
    Parameter: @SessionId7 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId8 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period9 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 1.
    Parameter: @SessionId10 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId11 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period12 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 4.
    Parameter: @SessionId13 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId14 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period15 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0.
    Parameter: @SessionId16 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId17 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period18 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 3.
    Parameter: @SessionId19 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId20 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period21 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 2.
    Parameter: @SessionId22 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId23 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period24 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 1.
    Parameter: @SessionId25 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId26 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period27 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 4.
    Parameter: @SessionId28 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId29 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period30 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0.
    Parameter: @SessionId31 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId32 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period33 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 3.
    Parameter: @SessionId34 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId35 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period36 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 2.
    Parameter: @SessionId37 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId38 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period39 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 1.
    Parameter: @SessionId40 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId41 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period42 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 4.
    Parameter: @SessionId43 : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId44 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2424.
    Parameter: @Period45 : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0.

So if you look at this monster of a query, you can easily get it down to:

SELECT t.*
FROM liveTeamGameEvent t
    JOIN liveGameEvent e ON
        e.sessionId = t.sessionId
        AND e.gameId = t.gameId
        AND e.eventId = t.eventId
WHERE e.sessionId = '87D78023AAD9F9081FDC08674D97D0FB'
AND e.gameId = 2424
AND e.period IN (0, 1, 2, 3, 4)

What could I do wrong to make LGP go nuts over this simple example? And what else do you need from me to figure this out?

Thanks

Please post the code snippet that generates this query. And... what is the real problem? (the big of the query, speed, exception...)

daelmo wrote:

Please post the code snippet that generates this query. And... what is the real problem? (the big of the query, speed, exception...)

the problem is that the query runtime exceeds the maximum:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath)

And here is the code snippet:

adapter.FetchEntity(game, getPrefetchPath());

and this is the relevant part of the getPrefetchPath() method:

IPrefetchPath2 game = new PrefetchPath2(EntityType.LiveGameEntity);

// split for player and coach events
IPrefetchPathElement2 teamEvent = LiveGameEventEntity.PrefetchPathLiveTeamGameEvent;
teamEvent.SubPath.Add(LiveTeamGameEventEntity.PrefetchPathLivePlayerGameEvent);
teamEvent.SubPath.Add(LiveTeamGameEventEntity.PrefetchPathLiveCoachGameEvent);

// to the periods
// a) team periods of the game
IPrefetchPathElement2 gamePeriod = LiveGameEntity.PrefetchPathLiveGamePeriod;
gamePeriod.SubPath.Add(LiveGamePeriodEntity.PrefetchPathLiveGameTeamPeriod)
    .SubPath.Add(LiveGameTeamPeriodEntity.PrefetchPathLiveTeamSiToLiveGame);

// b) to the events of the game
gamePeriod.SubPath.Add(LiveGamePeriodEntity.PrefetchPathLiveGameEvent)
    .SubPath.Add(teamEvent);

// add the periods to the game
game.Add(gamePeriod);

Is this enough to get a picture, or do you want more documentation?

The "simplified" query that I posted earlier runs in 0.2sec btw. The query generated by LGP (I reformated it again below) takes ... I can't even tell. I interrupted at 45min. [EDIT: It took 56min]

SELECT DISTINCT LiveTeamGameEvent.SessionId,
    LiveTeamGameEvent.GameId,
    LiveTeamGameEvent.EventId,
    LiveTeamGameEvent.TeamSi,
    LiveTeamGameEvent.RunningScore,
    LiveTeamGameEvent.CourtX,
    LiveTeamGameEvent.CourtY
    FROM LiveTeamGameEvent
    WHERE (LiveTeamGameEvent.SessionId IN (
        SELECT LiveGameEvent.SessionId
        FROM LiveGameEvent
        WHERE (((
            (LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 0)
            OR (LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 1)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 2)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 3)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 4))))
    )
    AND LiveTeamGameEvent.GameId IN (
        SELECT LiveGameEvent.GameId
        FROM LiveGameEvent WHERE ( ( ( (
                LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 0)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 1)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 2)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 3)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 4))))
    )
    AND LiveTeamGameEvent.EventId IN (
        SELECT LiveGameEvent.EventId
        FROM LiveGameEvent
        WHERE ((((
                LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 0)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 1)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 2)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 3)
            OR ( LiveGameEvent.SessionId = '87D78023AAD9F9081FDC08674D97D0FB'
                AND LiveGameEvent.GameId = 2424
                AND LiveGameEvent.Period = 4))))
));

If you take a closer look at the relations, you could actually just prefetch all teamEvents of a given game by just this:

SELECT t.*
FROM liveTeamGameEvent t
WHERE t.sessionId = '87D78023AAD9F9081FDC08674D97D0FB'
AND t.gameId = 2424;

So I'm also wondering why LGP looses the information "on the way" as it processes "down the game tree".

Hi magic,

Please try modifying the ParameterisedPrefetchPathThreshold. For example, if the LiveTeamGameEvent records for certain game is 150, set that value in the _ParameterisedPrefetchPathThreshold _and see what happens. As the documentation says, yo have to test it with your own database and environment to refine that setting.

daelmo wrote:

Hi magic,

Please try modifying the ParameterisedPrefetchPathThreshold. For example, if the LiveTeamGameEvent records for certain game is 150, set that value in the _ParameterisedPrefetchPathThreshold _and see what happens. As the documentation says, yo have to test it with your own database and environment to refine that setting.

Thank you for the advice. I tried to do this:

adapter.ParameterisedPrefetchPathThreshold = 150; // or = 1000;
adapter.FetchEntity(game, getPrefetchPath());

Unfortunately neither a threshold of 150, nor even one of 1000 seem to change the query that is generated when executing FetchEntity().

To give you an idea about the data amount:

"sessionId",                                                         "gameId", "period", "teamEventCount"
87D78023AAD9F9081FDC08674D97D0FB, 2424,     1,           142
87D78023AAD9F9081FDC08674D97D0FB, 2424,     2,           171
87D78023AAD9F9081FDC08674D97D0FB, 2424,     3,           184
87D78023AAD9F9081FDC08674D97D0FB, 2424,     4,           180

What I still don't understand is: Why isn't LGP able to figure out that it could select from LiveTeamGameEvent just by filtering by sessionId and gameId?

It's a little hard to follow what the database schema looks like from what you posted, so this may be way off:

Does the PK on the LiveGameEvent table include three keys? So the FK relationship between it and LiveTeamGameEvent involves three columns?

Prefetch paths and composite keys don't always play nice together, especially if the composite PK tables 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 (LiveGameEvent), 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.

Phil

Also which LLBLGen Pro runtime library version are you using?

Yes, the tables use composite keys. I have attached (a very simple version) of the database diagram for the 4 tables involved in the trouble. Does this help?

I have also updated my signature so that it shows more information about the LGP version I use.

I still don't quite understand why LGP trips over this very simple example ...

Did you try psandler's workaround?

Would you please check the following thread to know how to get teh exact LLBLGen Pro Runtime library version/build: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725 Anyway, would you please make sure you are using the latest build.

Walaa wrote:

Did you try psandler's workaround?

no, I didn't yet. I'm also not quite sure what you want me to do ... do you want me to go into the game prefetching method and manually prefetch the event collection in a second step specifying a prefetch filter based on the session and game id?

And why would this work in a two-step approach if it does not work so far? Or do you want me to prefetch both the events and the team events = make 3 steps?

Walaa wrote:

Would you please check the following thread to know how to get teh exact LLBLGen Pro Runtime library version/build: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725 Anyway, would you please make sure you are using the latest build.

yes, I downloaded the latest program version and re-generated the LGP code. I also downloaded the latest LGP libraries, put them into our project library folder and rebuild the whole project.

Unfortunately the bottom line is: Nothing has changed.

I don't want to sound impolite, but ... can somebody please answer the question I have been asking already multiple times? Why can't LGP cope with this problem? I know programming practice is often not as simple as theory, but I would say that the theory is simple.

So why is LGP not smart enough to select the events by session and game id only? Obviously the period is not of interest since all periods are selected when fetching a whole game.

As far as I can see, the problem is that it tries to select the events without looking at the bigger picture. So it just looks up one level in the path and sees that events of a given game period have to be fetched. But the information that all periods of this game have to be fetched is not taken into account.

magic wrote:

Obviously the period is not of interest since all periods are selected when fetching a whole game.

I'm not sure if I have time this evening to examine this in depth, but there does actually look like there is a problem with your query. Based on the model you posted, it looks like the query should be using SessionId/GameId/EventId, not SessionId/GameId/Period. Maybe check that your project file has all your columns mapped accurately?

Whether that will make the query significantly faster, I don't know (it certainly might).

I will try to post later/tomorrow more details on how we "manually prefetch" when we have performance problems with complex subqueries.

Phil

Just for the sake of testing, would you please set the adapter.ParameterisedPrefetchPathThreshold to 0;

I have double-checked the project in terms of relations and I couldn't see any wrong information.

When I looked through the project, I had another idea: I have manually added a new custom relation LiveGame - LiveGameEvent (1:n) called "LiveGameEventCollectionViaLiveGamePeriod".

I left the option to add new m:n relations automatically on, and something strange happened: All of a sudden, I had another relation "LiveGamePeriodCollectionViaLiveGameEvent" = LiveGame - LiveGamePeriod (m:n).

How can this be? LiveGame already has a 1:n relation to LiveGamePeriod (through the foreign key). How come LGP deducts a second relation to LiveGamePeriod, now being a m:n?

So I ended up removing the automatically generated m:n relation from the project again.

I then tried to run the .NET project with the re-generated LGP code. The changes in the LGP project did not have any effect on the structure of the problematic query.

So I have changed the prefetch path from above like this:

// b) to the events of the game
// gamePeriod.SubPath.Add(LiveGamePeriodEntity.PrefetchPathLiveGameEvent)
game.Add(LiveGameEntity.PrefetchPathLiveGameEventCollectionViaLiveGamePeriod)
    .SubPath.Add(teamEvent);

And now ... I don't get stuck any more, but ... judge yourself (I didn't reformat the queries in terms of line wrapping, but I removed meta-information about the parameters, so that the parameters are readable easier; also if all attributes are selected, I replaced the list with a "*"):

Generated Sql query: 
    Query: SELECT * FROM [LiveGameEvent]  WHERE ( ( ( [LiveGameEvent].[SessionId] = @SessionId1 AND [LiveGameEvent].[GameId] = @GameId2)))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId1 AND [LiveGameEvent].[GameId] = @GameId2))))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId3 AND [LiveGameEvent].[GameId] = @GameId4))))
    Parameter: @SessionId3 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId4 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId5 AND [LiveGameEvent].[GameId] = @GameId6))))
    Parameter: @SessionId5 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId6 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT * FROM [LiveTeamGameEvent]  WHERE ( [LiveTeamGameEvent].[SessionId] IN (SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId1 AND [LiveGameEvent].[GameId] = @GameId2))))) AND [LiveTeamGameEvent].[GameId] IN (SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId3 AND [LiveGameEvent].[GameId] = @GameId4))))) AND [LiveTeamGameEvent].[EventId] IN (SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId5 AND [LiveGameEvent].[GameId] = @GameId6))))))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.
    Parameter: @SessionId3 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId4 = 2424.
    Parameter: @SessionId5 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId6 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId1 AND [LiveGameEvent].[GameId] = @GameId2))))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId3 AND [LiveGameEvent].[GameId] = @GameId4))))
    Parameter: @SessionId3 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId4 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId5 AND [LiveGameEvent].[GameId] = @GameId6))))
    Parameter: @SessionId5 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId6 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LiveTeamGameEvent].[SessionId] FROM [LiveTeamGameEvent]  WHERE ( ( [LiveTeamGameEvent].[SessionId] IN (SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId1 AND [LiveGameEvent].[GameId] = @GameId2))))) AND [LiveTeamGameEvent].[GameId] IN (SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId3 AND [LiveGameEvent].[GameId] = @GameId4))))) AND [LiveTeamGameEvent].[EventId] IN (SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId5 AND [LiveGameEvent].[GameId] = @GameId6)))))))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.
    Parameter: @SessionId3 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId4 = 2424.
    Parameter: @SessionId5 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId6 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId7 AND [LiveGameEvent].[GameId] = @GameId8))))
    Parameter: @SessionId7 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId8 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId9 AND [LiveGameEvent].[GameId] = @GameId10))))
    Parameter: @SessionId9 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId10 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId11 AND [LiveGameEvent].[GameId] = @GameId12))))
    Parameter: @SessionId11 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId12 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LiveTeamGameEvent].[GameId] FROM [LiveTeamGameEvent]  WHERE ( ( [LiveTeamGameEvent].[SessionId] IN (SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId7 AND [LiveGameEvent].[GameId] = @GameId8))))) AND [LiveTeamGameEvent].[GameId] IN (SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId9 AND [LiveGameEvent].[GameId] = @GameId10))))) AND [LiveTeamGameEvent].[EventId] IN (SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId11 AND [LiveGameEvent].[GameId] = @GameId12)))))))
    Parameter: @SessionId7 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId8 = 2424.
    Parameter: @SessionId9 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId10 = 2424.
    Parameter: @SessionId11 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId12 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId13 AND [LiveGameEvent].[GameId] = @GameId14))))
    Parameter: @SessionId13 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId14 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId15 AND [LiveGameEvent].[GameId] = @GameId16))))
    Parameter: @SessionId15 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId16 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId17 AND [LiveGameEvent].[GameId] = @GameId18))))
    Parameter: @SessionId17 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId18 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LiveTeamGameEvent].[EventId] FROM [LiveTeamGameEvent]  WHERE ( ( [LiveTeamGameEvent].[SessionId] IN (SELECT [LiveGameEvent].[SessionId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId13 AND [LiveGameEvent].[GameId] = @GameId14))))) AND [LiveTeamGameEvent].[GameId] IN (SELECT [LiveGameEvent].[GameId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId15 AND [LiveGameEvent].[GameId] = @GameId16))))) AND [LiveTeamGameEvent].[EventId] IN (SELECT [LiveGameEvent].[EventId] FROM [LiveGameEvent]  WHERE ( ( ( ( [LiveGameEvent].[SessionId] = @SessionId17 AND [LiveGameEvent].[GameId] = @GameId18)))))))
    Parameter: @SessionId13 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId14 = 2424.
    Parameter: @SessionId15 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId16 = 2424.
    Parameter: @SessionId17 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId18 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT * FROM [LiveGamePeriod]  WHERE ( ( ( [LiveGamePeriod].[SessionId] = @SessionId1 AND [LiveGamePeriod].[GameId] = @GameId2)))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT * FROM [LiveGameTeamPeriod]  WHERE ( ( ( [LiveGameTeamPeriod].[SessionId] = @SessionId1 AND [LiveGameTeamPeriod].[GameId] = @GameId2 AND [LiveGameTeamPeriod].[PeriodId] = @PeriodId3) OR ( [LiveGameTeamPeriod].[SessionId] = @SessionId4 AND [LiveGameTeamPeriod].[GameId] = @GameId5 AND [LiveGameTeamPeriod].[PeriodId] = @PeriodId6) OR ( [LiveGameTeamPeriod].[SessionId] = @SessionId7 AND [LiveGameTeamPeriod].[GameId] = @GameId8 AND [LiveGameTeamPeriod].[PeriodId] = @PeriodId9) OR ( [LiveGameTeamPeriod].[SessionId] = @SessionId10 AND [LiveGameTeamPeriod].[GameId] = @GameId11 AND [LiveGameTeamPeriod].[PeriodId] = @PeriodId12) OR ( [LiveGameTeamPeriod].[SessionId] = @SessionId13 AND [LiveGameTeamPeriod].[GameId] = @GameId14 AND [LiveGameTeamPeriod].[PeriodId] = @PeriodId15)))
    Parameter: @SessionId1 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId2 = 2424.
    Parameter: @PeriodId3 = 3.
    Parameter: @SessionId4 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId5 = 2424.
    Parameter: @PeriodId6 = 2.
    Parameter: @SessionId7 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId8 = 2424.
    Parameter: @PeriodId9 = 1.
    Parameter: @SessionId10 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId11 = 2424.
    Parameter: @PeriodId12 = 4.
    Parameter: @SessionId13 = "87D78023AAD9F9081FDC08674D97D0FB".
    Parameter: @GameId14 = 2424.
    Parameter: @PeriodId15 = 0.

Next test case, set the prefetch path threshold (I switched back to the original prefetch path without the direct relation LiveGame - LiveGameEvent):

adapter.ParameterisedPrefetchPathThreshold = 0;
adapter.FetchEntity(game, getPrefetchPath());

If I do that, the execution goes completely nuts:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Too many table names in the query. The maximum allowable is 256.
Incorrect syntax near the keyword 'AND'.
Incorrect syntax near ')'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: Too many table names in the query. The maximum allowable is 256.
Incorrect syntax near the keyword 'AND'.
Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath)

The last query before this exception is from a completely different part of the prefetch path. It contains 576 (!) parameters, so posting it here would create more confusion than help. It has the same problem though: These parameters are 288 pairs of the same session and game id. So the query could be easily optimized to a join of maximum 5 tables with one pair of parameters.

I'm not sure what I should learn from all of that other than no matter what I do, the confusion that LGP creates when transforming the path to SQL is overwhelming.

Hi

To help us investigate this in a bit more depth, could you attach/post the SQL to generate the tables, the .lgp project and some code that demonstrates the issue.

Thanks

Matt

I appreciate the offer to help, but I see some question marks behind the actual "how to": -) Do you want me to create both a .NET and LGP project with the isolated problem? -) Do you also want sample data, or just the database structure?

I also have to get permission to share this information publicly. As you may have noticed, I have been trying to share the minimum necessary information about the project so far. This is because we don't want to share our development publicly.

Ideally we need as much as possible to recreate the problem - so .lgp, database, VS project etc are all good.

You can create a HelpDesk thread which will be private to only you and us to avoid sharing information publically.

Thanks

Matt