- Home
- LLBLGen Pro
- Bugs & Issues
ParameterisedPrefetchPathThreshold
Joined: 12-Aug-2005
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:
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é
Joined: 12-Aug-2005
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)
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é
Joined: 17-Aug-2003
"Internal error of the query processor", if my rusty german has it right ?
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?
Joined: 12-Aug-2005
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é
Joined: 16-Nov-2005
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.
Joined: 12-Aug-2005
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é
Filename | File size | Added on | Approval |
---|---|---|---|
BrokenQuery.txt | 31,689 | 14-Sep-2007 15:45.35 | Approved |
Joined: 12-Aug-2005
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é
Joined: 17-Aug-2003
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).