I have a timeout problem with the following code (LLBLGen version 3.5 - Adapter):

IRelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.SelectListAlias = "Recipe";

bucket.Relations.Add(SolverRecipeEntity.Relations.ViewLatestSolverRecipeIdsEntityUsingRecipeIdVersionId, "Recipe", "Latest", JoinHint.Inner);

bucket.Relations.Add(new DynamicRelation(EntityType.ViewLatestSolverRecipeIdsEntity, JoinHint.Inner, EntityType.VersioningBatchItemEntity, "Latest", "BatchItem",
        ViewLatestSolverRecipeIdsFields.RecipeId.SetObjectAlias("Latest") == VersioningBatchItemFields.SubjectId.SetObjectAlias("BatchItem")
            & VersioningBatchItemFields.SubjectTypeId.SetObjectAlias("BatchItem") == (int)RP_SubjectType.SolverRecipe));

bucket.PredicateExpression.Add(SolverRecipeFields.Deleted.SetObjectAlias("Recipe") == false & VersioningBatchItemFields.BatchId.SetObjectAlias("BatchItem") == batchKey);

EntityCollection<SolverRecipeEntity> collection = new EntityCollection<SolverRecipeEntity>();
Adapter.FetchEntityCollection(collection, bucket, Prefetches.GetSolverRecipePrefetchPath());

The error occurs on the last line after waiting for 30 seconds. My command timeout is 30 seconds, so that explains that, but raising the command timeout isn't an acceptable solution when the query should be fast.

I've found the problem only occurs when this particular prefetch is included:

IPrefetchPath2 root = new PrefetchPath2(EntityType.SolverRecipeEntity);
root.Add(SolverRecipeEntity.PrefetchPathSolverRecipeIngredients);

If I remove the prefetch to SolverRecipeIngredients, then the query executes in 20ms. However, the query will execute in around 4 seconds if I remove the predicate:

SolverRecipeFields.Deleted.SetObjectAlias("Recipe") == false

The query that LLBLGen generates for the SolverRecipeIngredients (with the deleted predicate) is the following:

Generated Sql query: Query: SELECT [RPDB2].[dbo].[RP_SolverRecipeIngredient].[IngId], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[IngVersionId], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[IsFixed], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[MaxConstraint], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[MinConstraint], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[Quantity], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[Ratio], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[RecipeId], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[RecipeVersionId], [RPDB2].[dbo].[RP_SolverRecipeIngredient].[RoundingWeight] FROM [RPDB2].[dbo].[RP_SolverRecipeIngredient] WHERE ( EXISTS (SELECT [LPA_R1].[RecipeId] FROM (( [RPDB2].[dbo].[RP_SolverRecipe] [LPA_R1] INNER JOIN [RPDB2].[dbo].[RP_View_LatestSolverRecipeIds] [LPA_L2] ON [LPA_R1].[RecipeId]=[LPA_L2].[RecipeId] AND [LPA_R1].[VersionId]=[LPA_L2].[VersionId]) INNER JOIN [RPDB2].[dbo].[RP_VersioningBatchItem] [LPA_B3] ON ( [LPA_R1].[RecipeId] = [LPA_B3].[SubjectId] AND [LPA_B3].[SubjectTypeId] = @p1)) WHERE ( ( ( ( [LPA_R1].[Deleted] = @p2 AND [LPA_B3].[BatchId] = @p3))) AND [RPDB2].[dbo].[RP_SolverRecipeIngredient].[RecipeId] = [LPA_R1].[RecipeId] AND [RPDB2].[dbo].[RP_SolverRecipeIngredient].[RecipeVersionId] = [LPA_R1].[VersionId]))) Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 6. Parameter: @p2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2161.

When run this directly in SQL management studio, this takes 1 minute and 29 seconds.

I would of expected the code to be more like this:

SELECT      *
FROM        RP_SolverRecipeIngredient I
INNER JOIN  RP_View_LatestSolverRecipeIds L ON L.RecipeId = I.RecipeId AND L.VersionId = I.RecipeVersionId
INNER JOIN  RP_SolverRecipe R ON R.RecipeId = L.RecipeId AND R.VersionId = L.VersionId
INNER JOIN  RP_VersioningBatchItem B ON B.SubjectId = R.RecipeId AND B.SubjectTypeId = 6
WHERE       R.Deleted = 0 AND B.BatchId = 2161

This query returns exactly the same number of rows as the query LLBLGen generates, but it takes 60ms... Obviously that's a massive difference 0.006 seconds vs 1 minute and 29 seconds.

Is there something obvious I've done wrong?

That's how prefetchPath works. You proposed query can be achieved if you fetch the SolverRecipeIngredients, using a relation(join) and the appropriate predicates.

As for the timeout exception, you might need to check if there is a deadlock, in the database. Related to transactions and read locks being placed on the requested table.

Walaa wrote:

That's how prefetchPath works. You proposed query can be achieved if you fetch the SolverRecipeIngredients, using a relation(join) and the appropriate predicates.

As for the timeout exception, you might need to check if there is a deadlock, in the database. Related to transactions and read locks being placed on the requested table.

The timeout is caused because the LLBLGen generated query is so ineffecient. See my expected example, vs the actual generated code. Returning the same number of results takes 60ms as apposed to 1 minute and 29 seconds. I'm 100% sure there's no transactions or deadlocks going on.

Is there a better way of doing my initial query with prefetches? Or is my only solution to drop the prefetch and write multiple queries?

Would manually setting up the relation in the designer (and removing the need for the dynamic query) create better generated SQL?

I've explained the difference between prefetchPath queries and Join based queries. What you did manually is a joined based query which still you can do with LLBLGen, don't use preftechPaths if you don't need them.