Ability to specify query and table hints in SQL Server
Table lock, update lock ( WITH(UPDLOCK) / FOR UPDATE ).
LockMode something like NHibernate.
Also should support controlling parameter sniffing
Thomas Tomiczek commented
+1. This is needed. Those are fundamental hints that just have to come up sometimes.
Steffen Mangold commented
It think this is a good solution becaus it follows up the transaction logic: http://stackoverflow.com/a/26762756/1112048
I need this option to to a pesismistic concurrency control, and it would be very useful if I can use the UPDLOCK in the related entities that I need to load in the include method.
@Marius Vorster: Thanks for the feedback. This is a feature we would like to add in the future. In EF7 we are laying off some of the necessary infrastructure in the form of query annotations, and we would accept a contribution in this area.
In the meanwhile for EF6, at least for very simple cases it is possible to append OPTIONS() at the end of queries using a query interceptor, e.g.:
class SimpleOptimizeForUnknownInterceptor : DbCommandInterceptor
public override void ReaderExecuting(
command.CommandText = command.CommandText +
" OPTION (OPTIMIZE FOR UNKNOWN)";
Of course you will want to add some logic to control when this transformation applies, e.g. add a bool flag to your custom DbContext and then check for the state of that flag inside the interceptor.
Marius Vorster commented
So we can do some query hints from TransactionScope but there is no way to provide 'Optimize for' hints (Parameter sniffing configuration). I have a case in production now where I expect the big standard deviation (Random slow queries) is due to bad parameter sniffing and my hands are tied. Can we please give it priority to include these configurations in Entity Framework in the future.
There are many scenarios where parameter sniffing in SQL server creates serious performance problems. This is very problematic with many EF generated queries, since there is no control that allows you to tell the database optimizer not to use the parameter values or cache a plan. A simple mechanism to hint OPTION (RECOMPILE) on the generated queries would solve most of these issues.
A more sophisticated solution that would allow the generated sql to leverage (Optimize for Unknown) would also be useful.
Parameter sniffing and the related performance problems are a very prevalent issue with SQL server and if you have ever run a real production app you will encounter it time and time again. I'm shocked there is still no workaround in EF (besides creating plan guides in the DB which are very fragile and impossible to manage in a dynamic filter scenario)
If I'm missing another workaround please advise.
I have a dynamic query that changes the number of joins based upon the number of keywords passed in and the query timesout...it never completes via EF. Without even specifying an option the same SQL running in SSMS executes in 3 seconds. We must have the ability to pass query hints or this ORM like many others will be useful only in silo projects with small databases. We need a solution for real world problems.
constantin prigoreanu commented
Eric Piazza commented
Need hints to control parameter sniffing, we have a query that takes 0 seconds with proper index usage, but the EF query uses a bad execution plan and it takes 20 seconds!
Xiaodong Huang commented
Without table hints support, it is almost impossible to write a high concurrrent service without resorting to stored procedure which is what we want to avoid by using EF.
Cosmin Onea commented
I think this is so important to have. I have a long running workflow and when a new message comes to it I need to lock it for a bit. I can query and use change tracker to set the entity as modified and save changes but that requires me to work around the problem instead of having that out of the box.
until then - you can use TransactionScope with ReadUncommitted isolation level.