Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> ParameterisedPrefetchPathThreshold
 

Pages: 1
Bugs & Issues
ParameterisedPrefetchPathThreshold
Page:1/1 

  Print all messages in this thread  
Poster Message
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 13-Sep-2007 17:43:32.  
Hi there,

LLBLGen 2.5 Adapter Scenario(.NET 2.0) on SqlServer 2000

I dunno really if its a bug or maybe Sql-Server is freaking out. I got a quite complex query involving a prefetch path with 25 elements over 5 levels. Using the default setting of ParameterisedPrefetchPathThreshold produces an exception which I don't really understand:

Code:
System.Data.SqlClient.SqlException: Interner Fehler des Abfrageprozessors: Der Abfrageprozessor konnte keinen Abfrageplan erzeugen. Weitere Informationen erhalten Sie bei Ihrem primären Technischen Support.
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
bei System.Data.SqlClient.SqlDataReader.get_MetaData()
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
bei SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)


Sorry, but I don't know how to translate that into english. Raising ParameterisedPrefetchPathThreshold up to 100 solves the problem. Is there something wrong with the recently introduced prefetch optimization?

Regards,
André
  Top
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 13-Sep-2007 19:22:25.  
Now when I modify the number of parameters of the byte arrays it doesnt work no more. See attached code (the code is generated by a custom tool for modelling LLBLGEN queries)

Code:

public static EntityCollection<OrderArticleEntity> GetOrderArticles(byte[] aOrderArticleIdStatusOrderArticle,byte[] aOrderArticleIdStatusPayment,byte[] aOrderArticleIdStatusFinance,byte[] aOrderArticleIdStatusShipment,DateTime orderDateFrom,DateTime orderDateTo) {
            EntityCollection<OrderArticleEntity> _OrderArticles = new EntityCollection<OrderArticleEntity>();
            IRelationPredicateBucket bucket = new RelationPredicateBucket();

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(OrderArticleFields.IdStatusOrderArticle == aOrderArticleIdStatusOrderArticle);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusPayment == aOrderArticleIdStatusPayment);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusFinance == aOrderArticleIdStatusFinance);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusShipment == aOrderArticleIdStatusShipment);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.PurchaseOrderDate >= orderDateFrom);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.PurchaseOrderDate <= orderDateTo);
            bucket.PredicateExpression.Add(filter);

            ISortExpression sorter = new SortExpression();
            bucket.Relations.Add(OrderArticleEntity.Relations.OrderEntityUsingIdOrder);
            bucket.Relations.Add(OrderContactingEntity.Relations.OrderEntityUsingIdOrder);

            IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.OrderArticleEntity);
            // * Prefetch: OrderVolumeDiscount
            IPrefetchPathElement2 fetchOrderVolumeDiscount =
                /// FirstLevel Prefetch: OrderVolumeDiscount
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderVolumeDiscount,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderVolumeDiscount -> OrderVolumeDiscountEntry
            fetchOrderVolumeDiscount.SubPath.Add(OrderVolumeDiscountEntity.PrefetchPathOrderVolumeDiscountEntry,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticlePriceToken
            IPrefetchPathElement2 fetchOrderArticlePriceToken =
                /// FirstLevel Prefetch: OrderArticlePriceToken
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticlePriceToken,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticlePriceToken -> PriceToken
            fetchOrderArticlePriceToken.SubPath.Add(OrderArticlePriceTokenEntity.PrefetchPathPriceToken,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleTracking
            IPrefetchPathElement2 fetchOrderArticleTracking =
                /// FirstLevel Prefetch: OrderArticleTracking
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleTracking,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleTracking -> Tracking
            fetchOrderArticleTracking.SubPath.Add(OrderArticleTrackingEntity.PrefetchPathTracking,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleType
            IPrefetchPathElement2 fetchOrderArticleType =
                /// FirstLevel Prefetch: OrderArticleType
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleType,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleType -> OrderArticleTypeTranslation
            fetchOrderArticleType.SubPath.Add(OrderArticleTypeEntity.PrefetchPathOrderArticleTypeTranslation,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticlePrice
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticlePrice,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticleNote
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleNote,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleGroup
            IPrefetchPathElement2 fetchOrderArticleGroup =
                /// FirstLevel Prefetch: OrderArticleGroup
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleGroup,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleGroup -> OrderArticleGroupTranslation
            fetchOrderArticleGroup.SubPath.Add(OrderArticleGroupEntity.PrefetchPathOrderArticleGroupTranslation,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: Order
            IPrefetchPathElement2 fetchOrder =
                /// FirstLevel Prefetch: Order
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrder,int.MaxValue,null,null,new SortExpression());
            IPrefetchPathElement2 fetchOrderContactingViaOrder =
                // ** Prefetch: Order -> OrderContacting
                fetchOrder.SubPath.Add(OrderEntity.PrefetchPathOrderContacting,int.MaxValue,null,null,new SortExpression());

            IPrefetchPathElement2 fetchOrderContactingAddress =
                // *** Prefetch: Order -> OrderContacting -> OrderContactingAddress
                fetchOrderContactingViaOrder.SubPath.Add(OrderContactingEntity.PrefetchPathOrderContactingAddress,int.MaxValue,null,null,new SortExpression());
            IPrefetchPathElement2 fetchOrderContactingAddressViaOrder =
                // **** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress    
            fetchOrderContactingAddress.SubPath.Add(OrderContactingAddressEntity.PrefetchPathOrderAddress,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderEmail    
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderEmail,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderTelecommunication    
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderTelecommunication,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderAddressAssignment    
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderAddressAssignment,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticleTranslation
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleTranslation,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderPrintSubstrate
            IPrefetchPathElement2 fetchOrderPrintSubstrate =
                /// FirstLevel Prefetch: OrderPrintSubstrate
            prefetchPath.Add(OrderArticleTypePaperPrintEntity.PrefetchPathOrderPrintSubstrate,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderPrintSubstrate -> OrderPrintSubstrateTranslation
            fetchOrderPrintSubstrate.SubPath.Add(OrderPrintSubstrateEntity.PrefetchPathOrderPrintSubstrateTranslation,int.MaxValue,null,null,new SortExpression());

            using(DataAccessAdapter adapter = new DataAccessAdapter()) {
                adapter.ParameterisedPrefetchPathThreshold = 100;
                adapter.FetchEntityCollection(_OrderArticles,bucket,int.MaxValue,sorter,prefetchPath);
            }
            return _OrderArticles;
        }


Regards,
André


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38044 posts
# Posted on: 13-Sep-2007 19:28:27.  
"Internal error of the query processor", if my rusty german has it right Wink ?

Hmm. that's pretty serious. Do you have all the service packs of the sqlserver version installed? Also, if you run the query (check the query sql via DQE tracing) in query analyzer, does it throw the error there as well?
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 13-Sep-2007 19:32:41.  
Hi Otis,

got all ServicePacks installed. Is there a way to get the query with all parameters rendered? You can possibly understand that I don't want to puzzle the above query together by hand ;-) Got tracing on but it displays a parametrized query.
Regards,

André


  Top
JimHugh
User



Location:
Davis, CA
Joined on:
16-Nov-2005 14:32:25
Posted:
191 posts
# Posted on: 13-Sep-2007 20:54:34.  
Check out the blog at http://cs.rthand.com/blogs/blog_with_righthand/pages/Implementing-more-useful-tracing-for-LLBLGenPro-2.0.aspx

for an implementation for SQL server that does just that.
Jim Hughes  Top
arschr
User



Location:
Atlanta, Georgia; USA
Joined on:
14-Dec-2003 16:57:29
Posted:
887 posts
# Posted on: 13-Sep-2007 22:52:00.  
Or use Sql profiler to capture the query

- Al  Top
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 14-Sep-2007 15:45:21. Goto attachments  
Thanks for the answers. I used Miha's approach to get the query rendered. I still got the same error. Rendered query is attached...

By now I found a workaround...

In the broken query I fetch OrderArticles and thier superior Orders, because I want to work with OrderArticles. The second, working approach fetches Orders and their related OrderArticles. When I got the Orders I iterate through ever OrderArticle in every Order and collect them in a list. The thing that puzzles me is that the second approach works, but the first doesn't. Got any clue?

Regards,
André
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14626 posts
# Posted on: 14-Sep-2007 17:37:35.  
Execuse me, that's a lot of code to inspect.
Would you please simplify it to a couple of lines?

Thanks.


  Top
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 14-Sep-2007 18:11:04.  
Hi Walaa,

simplifying the code was what I was actually trying to achieve before posting that bunch here. :-)

I'm sorry to say, but I wasn't able to reproduce the above explained behaviour after removing some prefetches. The problem is no more urgent as I managed to use a workaround, needless to say I understand that you want to prevent others to run into the same thing.

If I can help in other ways, I'm willing to do what I can.

Regards,
André
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38044 posts
# Posted on: 15-Sep-2007 12:02:54.  
If you run the query directly inside query analyzer do you get the same error?

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Jazz
User



Location:
Berlin, Germany
Joined on:
12-Aug-2005 17:53:10
Posted:
63 posts
# Posted on: 16-Sep-2007 13:29:58.  
Yes, got exactly the same error.

Regards,
André
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38044 posts
# Posted on: 18-Sep-2007 19:16:07.  
Jazz wrote:
Yes, got exactly the same error.

Regards,
André

Then it's something internally in SqlServer, as the query is IMHO a query which should work so I don't think I can do something about it, sorry.

You could call PSS (microsofts personal support services) to get this fixed for you by them. As it's an internal error, it's a bug and they fix bugs for free. You then get an engineer assigned to you who will fix it (or perhaps there's already a hotfix available for this).



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.