How to precompile sql queries in EF Code-First?

Question

I am using ASP.Net4.5.1 & EF6.0 Code-First approach.

I realised that queries are taking considerable time to execute. I have used EF Profiler to check the queries & fine-tuned the queries.

I googled & came to know Compiling Linq.

These are the links I got.

• https://stackoverflow.com/questions/32175040/how-to-cache-queries-in-ef-code-first
• How do I precompile an Entity Framework Code-First Query?
• Entity Framework Compiled Query

But none of them that address my concern.

Suppose I have below query.

public IEnumerable<Student> GetStudents()
{
  using(DbContext db = new DbContext()
  {
  IQueryable<Student> query = (from c in db.Students
                                where c.Hobby== "Hockey"
                                select c);
  IEnumerable<Student> students= query.toList<Student>();

  return students;
  }
 }

Now how do I cache the query plan or compile this Linq to increase the performance?

One more thing to ask here, is EF slower than native SQL?

I prefer to use EF/ORM, is it right choice?

Answer 1

Yes, EF is slower than SQL. This is true of any ORM. An ORM must work to transform your code-based "query" into a real SQL query and then it must use the result to instantiate an object graph. However, those are generally things you want an ORM for. Doing this type of stuff manually would be more difficult and more error-prone.

Whether you utilize an ORM is entirely dependent on the needs of your application. Generally, yes, an ORM would be recommended, but if you need extreme performance, it might be necessary to drop into pure SQL. ORMs also varied wildly in their individual performance. EF is known to be a particularly slow ORM. It's among the easiest to use and work with, but if performance is a concern, you're better off with something like Dapper. You can also mix and match ORM and SQL usage. It's not abnormal at all to use EF for standard CRUD and then utilize stored procedures for complex queries.

All that said, the query you have is extremely basic. If you're experiencing performance problems with something like that, either your SQL Server instance is not being given enough resources to work with, your network is incredibly slow, or there's a ton of data in that table and you're not utilizing indexes properly.

On that last point, text searches, in general, are among the slowest to perform with SQL. If there's a particular text-based column that you intend to query, such as Hobby, then you should add an index on it. You can either do this manually at the database, or utilize the [Index] data annotation on the property. Just bear in mind that only fixed-length text-based columns can be indexed. By default, EF will generate string properties as NVARCHAR(MAX). If you want to employ indexing on the column, you'll need to combine [Index] with [MaxLength(N)].

Answer 2

If you want faster data access code, You should consider using Pure ADO.NET code which executes your SQL queries using a SqlCommand and SqlDataReader's ExecuteReader method. But now you need to write some extra code to create a SqlCommand, pass the query and parameters and read the rows from the SqlDataReader.

You should consider some mini ORM's like Dapper. It is way faster than EF. You do not need to write much code as I mentioned above. Dapper will execute your queries and map the result set to your DTOs.

Quick example of using Dapper.

var con= new SqlConnection("YourConnectionStringGoesHere");
var posts = con.Query<Post>("SELECT ID,Name from Post");

Dapper supports reading data from multiple tables (JOIN) and mapping to an object with Navigation properties.

Take a look at their performance numbers

EF/NHibernate will help you to do Rapid development as it is easy to talk to the database with their API/methods. But you pay for the performance.

Also the important thing to remember is to optimize your sql query. Even with pure ADO.NET, You can get poor performance if your sql query is not optimized. Adding proper indexes to your table/columns might also help.

I am not entirely sure about your application, But you should read about caching data so that your database won't be hit all the time. that will generally improve your performance.