none
performance problem with .ToList() RRS feed

  • Question

  • hi,

    i have a linq  to sql querry that go's something like this:

     

    using(var db = databasecontex())

    {

    var pepole = db.personTable.Where(p=>p.age > 20);

    var pepoleList  = new List<PersonTable>(pepole.ToList());

    // it take forever!

    }

     

    the pepole retrives about 700 resultes and the ToList()

    takes forever to run! and if return the IEnuereble the context get dispose so the data is unexesible...

     

     

    how can i fix this??

    thanks in advance

    jony

    Monday, August 22, 2011 8:07 AM

Answers

  • hi,

    thanks for the help i figured out the problem it wasn't really related to the ToList... 

    i will ask a new question tomorrow... thge problam is with the loadWith...

     

     

    thank you very much anyway! [=

    • Marked as answer by jony feldman Monday, August 22, 2011 5:31 PM
    Monday, August 22, 2011 5:31 PM

All replies

  • Do you have a secondary index on the person table AGE column? If not, then you are doing a full table scan to get a subset back.

    When you are timing commands, watch out for when the access to the database happens. It doesn't happen on the first line (var people = db.personTable ...), it happens when you first try to access the data (i.e. on the people.ToList() ). So that is doing two things - reading the database and converting to a list.

     

     

    Monday, August 22, 2011 9:12 AM
  • sorry i forgot to write a few things,

     

    i also do an orderby on linq so it should read from the db on that line,

    and second its a where not select age so i do retrieve a table of data not only the age,

     

    so why does the ToList takes so long??

    Monday, August 22, 2011 10:02 AM
  • Hi Jony,

    I'm sorry but I'm not sure that I understand your comments - so please accept my apologies if I am not answering correctly (or if I am stating the obvious).

     

    i also do an orderby on linq so it should read from the db on that line,

    An orderby does help SQL chose which index to use in the selection but if you don't have an index on AGE then it will still do a full table scan before narrowing it down to the WHERE clause.

     

    and second its a where not select age so i do retrieve a table of data not only the age,

    I think you mean that you are selecting all columns, not just AGE. So that means that the pepole var is actually an IEnumerable<PersonTable> or IQueryable<PersonTable>. That is what I understood originally so it doesn't make any difference to my original comment.

    so why does the ToList takes so long??

    My experience of ToList() is that it doesn't carry much overhead.

     

    Could you humour me and do the following to try to narrow down where the slowness is?

        DateTime t1 = DateTime.Now;
        var pepole = db.personTable.Where(p=>p.age > 20);

        DateTime t2 = DateTime.Now;
        int x = pepole.Count();
     
        DateTime t3 = DateTime.Now;
        var pepoleList  = new List<PersonTable>(pepole.ToList());
     
        DateTime t4 = DateTime.Now;

     

    My guess is that the long time will be between t2 and t3 because that is when the query is actually executed in the database.

    Monday, August 22, 2011 11:26 AM
  • thanks for the help 

     

    but that's what i was trying to tell you,

    i tried that, the count was lighting fast

    the .ToList is the problem 12 sec for 700 results to be exact ...

    why? =[ 

    Monday, August 22, 2011 3:44 PM
  • hi,

    thanks for the help i figured out the problem it wasn't really related to the ToList... 

    i will ask a new question tomorrow... thge problam is with the loadWith...

     

     

    thank you very much anyway! [=

    • Marked as answer by jony feldman Monday, August 22, 2011 5:31 PM
    Monday, August 22, 2011 5:31 PM
  • Hi Jony. I'm glad you found the source of your problem & sorry that I wasn't able to help in the end :).
    Monday, August 22, 2011 10:42 PM
  • you actualy did help me figure out the problem [=

     

    this is my new thread with the real problem:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/c30ffb8d-aa6d-4c83-a39f-c4b1e26173bb

     

    so thank you! [=

    Tuesday, August 23, 2011 5:23 AM