IEnumerable taking a lot longer than IQueryable to return the results

Question

Consider the following code snippets:

public interface IRepository<T> where T : class
{
    IQueryable<T> GetAll();
    ...
}

public class EmployeeRepository<T> : IRepository<T> where T : class
{
    private Employee db;
    private DbSet<T> dbSet;

    public EmployeeRepository()
    {
        db = new Employee();
        dbSet = db.Set<T>();
    }
    public virtual IQueryable<T> GetAll()
    {
        return dbSet;
    }
}

In the controller I implement it as:

private IRepository<Employee> employeeRepo = null;

public HomeController()
{    
   employeeRepo = new EmployeeRepository<Employee>();
}

Using the above code if I call the employeeRepo.GetAll() method in my MVC controller action I get the result instantly in the view No more than 2 seconds to return the records of around 500 rows. If I change the same code to

public virtual IEnumerable<T> GetAll()
{
  return dbSet.ToList();
}

then it takes about 30 seconds to return the same amount of rows from the exact same table.

So my question is why does IEnumerable<T> take so much longer than IQueryable<T>.

Most of the example I've seen online for repository-pattern use IEnumerable<T> so I'm not sure if I'm going down the correct route by using IQueryable<T>

Update:

public ActionResult Index()
{
  var allEmployees = employeeRepo.GetAll();

  return View(allEmployees);
}

The SQL being executed is

SELECT EMPLOYEEID, EMPLOYEENAME, EMPLOYEEDOB, EMPLOYEETELEPHONE FROM [DBO].[EMPLOYEE]

The exact number of rows returned is 507. I use all of them because they will go into a dropdown list

And when you say you get the result instantly, do you actually populate the view and produce the final html *in those 2 seconds*, or do you mean that the method GetAll returned within 2 seconds? — Lasse V. Karlsen, May 27 '16 at 08:34
yes, ToList materializes objects right away, and in your first approach they will be materialized by asp.net during response generation, but there should not be _that_ much difference anyway (unless you measure it wrong and those 2 seconds are until you see _first_ results, not _all_ results). — Evk, May 27 '16 at 08:36
If you call `EmployeeRepository.GetAll().ToList()`, how long does that take? — Matthew Watson, May 27 '16 at 08:38
@LasseV.Karlsen I used http://getglimpse.com/and it displays that the query took so long to run — Izzy, May 27 '16 at 08:39
So just to verify, you actually did execute the query when using `IQueryable`, you didn't just build the expression and did not execute it? Then yes, it is strange that IEnumerable takes that much longer. — Lasse V. Karlsen, May 27 '16 at 08:39
Can you show the code that executes it? Rather, can you build a [mcve] that has this problem on your end? — Lasse V. Karlsen, May 27 '16 at 08:40
@LasseV.Karlsen If it's `IQueryable` then 2 second and with `IEnumerable` then 30 — Izzy, May 27 '16 at 08:46
I just want to ensure you're timing the right thing. Can you tell me what you're timing, in detail? — Lasse V. Karlsen, May 27 '16 at 08:47
@LasseV.Karlsen I'm timing how long it takes to execute the query — Izzy, May 27 '16 at 08:48
And how do you do that, please be specific. What, **specifically**, are you timing? Are you starting your stopwatch when you hit refresh in the browser, and stopping it when download completes? Or are you starting it before the line GetAll and stopping it after that line? — Lasse V. Karlsen, May 27 '16 at 08:48
@LasseV.Karlsen my apologies, I'm using the glimpse tool. It has a section where it tells me how long the query took to run — Izzy, May 27 '16 at 08:51
Have you considered looking at the actual SQL being executed? Also, can you show the code that uses this set? It would probably be somewhere in your view. Also, how many rows does this query return? Are you using them all? Are you doing pagination in your view, as an example, that would perhaps only read the first 10-20 rows and then stop? — Lasse V. Karlsen, May 27 '16 at 08:53
@Code use tool like firebug to see how long request is executed and how long page is loaded, in both cases. — Evk, May 27 '16 at 08:55
@LasseV.Karlsen I've updated my question again, I'm not doing pagination in the view at the moment — Izzy, May 27 '16 at 09:06
Even 2 seconds is too long (in my opinion) to return 507 rows with those few columns. — Lasse V. Karlsen, May 27 '16 at 09:08
@LasseV.Karlsen If I run the exact query to Management Studio it's instant — Izzy, May 27 '16 at 09:09
If you do a query that returns rows as you process them, and your code that processes them is slow, then it will look like the query is slow but you're actually timing the entire time it took for the rows to be returned and *processed*. — Lasse V. Karlsen, May 27 '16 at 09:11
@LasseV.Karlsen I've just ran the code in the console and the results are `176.4594` total milliseconds for `IQueryable` and `5145.0831` total milliseconds for `IEnumerable` — Izzy, May 27 '16 at 09:26

score 2 · Answer 1 · edited May 27 '16 at 08:35

2

this is because inside Get() you call ToList() which means you are actually execute the query. In the first implementation (IQueryable) query is not executed. It will be executed until you call ToList()

edited May 27 '16 at 08:35

David Pilkington

13,528
3
41
73

answered May 27 '16 at 08:33

apomene

14,282
9
46
72

But 30 seconds is a very long time.. Is that normal? – Izzy May 27 '16 at 08:34
Depends on the query and the database instance. Are you querying against non-indexed columns? How many rows are involved? Does your database server have enough resources? Is it the only thing running on the server? Is the database remote or local? If it's remote, is it onsite or off-site? Do you have sufficient bandwidth available between the web server and the database server? The questions go on and on. – Chris Pratt May 27 '16 at 13:26

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

I believe this is similar to the question asked here. Essentially IQueryable will try and execute within the database as this is the Linq-to-SQL interface and IEnumerable will need to load everything into memory first. Here is another detailed explanation on the difference with performance code.

edited May 23 '17 at 12:15

Community

1
1

answered May 27 '16 at 08:32

SpeedOfSpin

1,611
20
21

1

`IEnumerable` on it's own won't load everything into memory, the the `ToList()` in the OP certainly will cause it to. – Matthew Watson May 27 '16 at 08:37
@MatthewWatson so if I remove `.ToList()` it won't cause me any problems – Izzy May 27 '16 at 08:52
@Code Well it shouldn't do - depends if you are iterating it multiple times later or not. – Matthew Watson May 27 '16 at 08:57
@MatthewWatson I only need to iterate it once in the view – Izzy May 27 '16 at 08:59
If he builds the view using all rows and times this, then it should not matter if he uses IQueryable or IEnumerable.ToList. – Lasse V. Karlsen May 27 '16 at 09:08

IEnumerable taking a lot longer than IQueryable to return the results

2 Answers2