49721

Linq to Entities performance difference between Expression/Func

Question:

I was just testing a simple query that i'm accessing in different ways, but the speed of each can vary by up to 2 seconds. I was hoping someone can clarify why this is the case. My project is in it's very early stages, so I thought I'd make sure I'm doing it right before it gets too big.

Admittedly, my testing style isn't perfect, but i think it's good enough for this.

I'm using a generic Repository and UnitofWork, and I hit the DB (sqlexpress on my local machine) 10,000 times in this while statement. The table only has 64 records. Tests are run in Release mode.

[TestMethod] public void MyTestMethod() { using (var u = new UnitOfWork()) { TestA(u); TestB(u); } }

TestA (Func):

public void TestA(UnitOfWork u) { Stopwatch s = Stopwatch.StartNew(); s.Start(); var x = 0; var repo = u.Repository<MyEntity>(); var code = "ABCD".First().ToString(); while (x < 10000) { var testCase = repo.Single(w => w.Code == code && w.CodeOrder == 0).Name; x++; } s.Stop(); Console.WriteLine("TESTA: " + s.Elapsed); }

TestB (Expression):

public void TestB(UnitOfWork u) { Stopwatch s = Stopwatch.StartNew(); s.Start(); var x = 0; var repo = u.Repository<MyEntity>(); var code = "ABCD".First().ToString(); while (x < 10000) { var testCase = repo.First(w => w.Code == code && w.CodeOrder == 0).Name; x++; } s.Stop(); Console.WriteLine("TESTB: " + s.Elapsed); }

Even though i'm using the calls First() and Single(), they're not the built-in LINQ calls. They're part of my repository.

First() expression (IQueryable)

public TEntity Single(Func<TEntity, bool> predicate) { return dbSet.FirstOrDefault(predicate); }

Single() func (IEnumerable)

public TEntity First(Expression<Func<TEntity, bool>> predicate) { return dbSet.FirstOrDefault(predicate); }

<strong>Output:</strong>

Test Name: MyTestMethod Test Outcome: Passed Result StandardOutput: TESTA: 00:00:02.4798818 TESTB: 00:00:03.4212112

Answer1:

This is not an answer, but just trying to make sure that the test results are more reliable.

Try writing your tests like this:

public long TestA() { using (var u = new UnitOfWork()) { var s = Stopwatch.StartNew(); var x = 0; var repo = u.Repository<MyEntity>(); var code = "ABCD".First().ToString(); while (x < 10000) { var testCase = repo.Single(w => w.Code == code && w.CodeOrder == 0).Name; x++; } s.Stop(); return s.ElapsedMilliseconds; } }

(Obviously TestB is just a minor variant.)

And then your test method becomes:

[TestMethod] public void MyTestMethod() { var dummyA = TestA(); var dummyB = TestB(); var realA = 0L; var realB = 0L; for (var i = 0; i < 10; i++) { realA += TestA(); realB += TestB(); } Console.WriteLine("TESTA: " + realA.ToString()); Console.WriteLine("TESTB: " + realA.ToString()); }

Now your results are likely to be more accurate. Let us know the timings now.

<hr />

Now try changing your tests like this:

public int TestA() { var gc0 = GC.CollectionCount(0); using (var u = new UnitOfWork()) { var s = Stopwatch.StartNew(); var x = 0; var repo = u.Repository<MyEntity>(); var code = "ABCD".First().ToString(); while (x < 10000) { var testCase = repo.Single(w => w.Code == code && w.CodeOrder == 0).Name; x++; } s.Stop(); } return GC.CollectionCount(0) - gc0; }

This should determine how many generation 0 garbage collections are being performed. That might indicate that the performance issues are with your tests and not with the SQL.

Answer2:

<a href="http://msdn.microsoft.com/en-us/library/vstudio/system.linq.queryable.first%28v=vs.100%29.aspx" rel="nofollow">First()</a> with Expression<Func<...>> parameter is an extension method on IQueryable<T> and is used by query providers, like LINQ to Entities. Expression tree you provide is transformed into proper SQL query, which is sent to DB and only necessary rows are returned back to your application.

<a href="http://msdn.microsoft.com/en-us/library/vstudio/bb535050%28v=vs.100%29.aspx" rel="nofollow">First()</a> with Func<...> parameter is an extension method on IEnumerable<T> and is used by LINQ to Objects, which mean all the records from database will be fetched into application memory, and then element will be search as in-memory query, which is implemented as linear search.

You should definitely use the one from IQueryable<T>, because it will be more efficient (as database is optimized to perform queries).

Answer3:

I will list some tests you might wanna try to help you narrow the differences between the operations.

<strong>Check the actual SQL code</strong>

Turn on the debug log for the queries or check it on the SSE logs. It is important since the EF engine should optimize the statements, and you can see what is really beeing sent to the DB.

As you said, the First operation should be faster, since there are optimized SQL operators for that. The Single should be slower since it has to validate all the values, and would scale based on the amount of rows.

<strong>Use the real SQL on the database for a reference test</strong>

Once you have the real SQL you can also check the differences of time elapsed on the database directly. Implement the same C# test on the DB, a Sotred Procedure maybe, and see what happens.

<strong>Try the built-in LINQ for comparison</strong>

I dont know if you already did it for the test, but try to use the native LINQ for a comparison.

I made many tests here using LINQ and there were no differences between the two statements you presented, so it actually could be the Expressions. (I used the SS CE btw).

Also, just for the sake of saying it, remmember to create Indexes for columns involved in heavy operations ;) EF 6.1 has this feature built-in now.

[Index] public String MyProperty{ get; set; }

Let me know if it was helpful.

Recommend

  • Spring + Hibernate manually creating transactions, PROPAGATION_REQUIRED fails. BUG?
  • Is this the correct usage of async/await in MVC with service/repository layer?
  • Generic ObjectContext? objectContext.GetObjectSet?
  • forcing usage of bitwise and instead of boolean and
  • Custom construction with open generics in StructureMap
  • LocalStorage Get Item with Dot/Bracket Notation Not Working with JSON.parse()
  • How to Define Extension Method for ICollection where T : IMyInterface without Specifying T in the Me
  • Why does C++ require breaks in switch statements? [duplicate]
  • Take all items in sub-categories using LINQ
  • spawn random images in canvas in javascript
  • How to handle exception using Timer (Thread) class
  • Hive command line Select query time taken incorrect if its not map reduce job in the background
  • Get predicate execution time in seconds
  • LINQ GroupBy Count
  • Linq Error \"Could not find an implementation of the query pattern for source type 'Syste
  • Deleting a widget from QTableView
  • Many to Many in Linq using Dapper
  • Multiple Left Join LINQ-to-entities
  • How to know which Linq statement produced the SQL on hand during runtime?
  • multidatatrigger with multibinding in ControlTemplate.Triggers
  • Is there a parser equivalent of 'fragment' marking in ANTLR4?
  • jquery validation - waiting for remote check to complete
  • Exception creating JSON with LINQ
  • NUnit 3.0 TestCase const custom object arguments
  • Linq Merge lists
  • Plotting line graph with factors in R
  • How do I display a dialog that asks the user multi-choice questıon using tkInter?
  • Build Successful but not running on simulator
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • FFmpeg Conversion Error
  • What is Eclipse's Declaration View used for?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Jquery - Jquery Wysiwyg return html as a string
  • SVN: Merging two branches together
  • PHP: When would you need the self:: keyword?
  • Proper folder structure for lots of source files
  • How to get Windows thread pool to call class member function?
  • Append folder name and increment by 1 using batch script
  • Net Present Value in Excel for Grouped Recurring CF