CODEWITHSUNDEEP
.netperformanceentity-frameworkienumerableiqueryable
Rowan Freeman
Rowan Freeman

Reputation: 16348

Why is IEnumerable faster than IQueryable?

Introduction

I've been researching the difference between the two. I've decided to get my hands dirty and test them because I want to see the performance difference between the two (if any) and understand why.

I've created a simple console app. I'm using Entity Framework and SQL Server. I've created a model which points to a database table. The table has ~1000 records.

The Tests

Dim _db As New SampleEntities
Dim sw As New Stopwatch

Test 1 - 966ms

Dim allMemos As IEnumerable(Of Memo) = _db.Memos
sw.Start()
allMemos.ToList()
sw.Stop()

Test 2 - 6ms

Dim iEnum As IEnumerable(Of Memo) = _db.Memos.AsEnumerable
sw.Restart()
iEnum.Where(Function(f) f.Active).ToList()
sw.Stop()

Test 3 - 186ms

Dim iQuer As IQueryable(Of Memo) = _db.Memos.AsQueryable
sw.Restart()
iQuer.Where(Function(f) f.Active).ToList()
sw.Stop()

My Findings

Before I began, my research told me that in many cases IQueryable is faster because it is likely to optimise the query so that the filter (i.e. memo is Active) is performed by SQL and so very few results will be returned when ToList() is called.

However, my findings demonstrated the opposite. Why? I was sure that in my test IQueryable would be faster. I deliberately set it up to show this.

"It's because you used iQuer As IQueryable(Of Memo)!"

I thought this too. I had heard that casting my objects as IQueryable might be bad because it has to convert from IEnumerable anyway. So I tried this:

Dim iQuer As IEnumerable(Of Memo) = _db.Memos.AsQueryable

And it did improve performance so that Test 3 took 17ms instead of 186ms. But it's still slower than 6ms (Test 2).

What's going on here?

Upvotes: 3

Views: 826

Answers (1)

tmaj
tmaj

Reputation: 34947

I don't know what _db.Memos returns but in test 2 

Dim iEnum As IEnumerable(Of Memo) = _db.Memos.AsEnumerable

all the records may be loaded into memory there and then.

In test 3 the records are loaded when you call ToList:

  iQuer.Where(Function(f) f.Active).ToList()

You should do 2 things:

  1. Move your stopwatch lines before the assignment and compare results then .
  2. Setup a trace in your db to see where the code actually hits the database.

Upvotes: 2

Related Questions