none
Linq is slow on .ToList RRS feed

  • Question

  • I have a query that can get anywhere from 0 to 4000 records return.

    I have it setup to use paging.  My code.....

    Dim qry as  IQueryable(of ItemList) =

    From

     

    tb In Table
    Where tb.RecID = RecID And tb.AnotherID = AnotherID _
    Order By tb.Date Descending, tb.Time Descending _
    Select tb _
    Skip (pageNumber - 1) * itemsPerPage _
    Take itemsPerPage


    The problem is when I go qry.ToList it takes FOREVER to run. There is only 10 items per page.  I looked into using Func but this query is in a wcf service and it only gets called one at a time. Its not in a loop.  My understanding is that Func should be used if it gets called over and over again.  Am I going at this the right way? Should I be using Fun or Compiled Query? Remember thought I am using this with wcf.

    If you think I should use Func or Ling.CompiledQuery? Can you give me a Vb example on how to use these? I had  a hard time find good vb examples.

    Please Help!!!!
    Thanks

    • Edited by apalcer Wednesday, June 24, 2009 6:00 PM Add More thoughts
    Wednesday, June 24, 2009 5:50 PM

All replies

  • From a performance perspective, you may want to profile this to see if the perf issue is in the database, query generation, or service. If this query is static, I would recommend moving to trying compiled query. I have examples in VB using compiled queryies at http://www.thinqlinq.com/Default/LINQ_to_SQL_Compiled_Queries.aspx. It's also included in the LINQ In Action samples that you can download through LINQPad.

    Jim
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Wednesday, June 24, 2009 7:56 PM
    Moderator
  • I am getting an argument error when using CompiledQuery.Compile() or Function. I have never used this before.

    CompiledQuery.Compile(

    Function(Ent As MyEntity,A As Integer,B As Integer,pageNumber As Integer, itemsPerPage As Integer) _
    From n In Ent.Table _
    Where n.A = A And n.B = B _
    Order By n.Date Descending, n.Time Descending _
    Select n _
    Skip (pageNumber - 1) * itemsPerPage _
    Take itemsPerPage))

    What is wrong with this?  I am passing in 4 values A, B and the pageNumber and itemsPerPage.

    Wednesday, June 24, 2009 8:40 PM
  • I had  a hard time find good vb examples.


    Here are some very good examples for LINQ using VB: http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx


    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, June 25, 2009 5:08 AM
  • Thank you for your help!!!   Any suggestion on how to make CompiledQuery.Compile to work with passing in 4 intergers? 

    CompiledQuery.Compile(

    Function(Ent As MyEntity,A As Integer,B As Integer,pageNumber As Integer, itemsPerPage As Integer) _
    From n In
    Ent.Table _
    Where n.A = A And
    n.B = B _
    Order By n.Date Descending, n.Time Descending
    _
    Select
    n _
    Skip
    (pageNumber - 1) * itemsPerPage _
    Take
    itemsPerPage))


    I don't understand why this is painfully slow. I am doing similar queries and they run fine. This is the only one that involves paging. Could that be what is slowing this down?

    • Edited by apalcer Thursday, June 25, 2009 1:28 PM add another question
    Thursday, June 25, 2009 1:17 PM
  • Kinda got it working...but new error.....

     

    ReadOnly query = System.Data.Objects.CompiledQuery.Compile(Of Entity, Integer, Integer, Integer, IQueryable(Of NoteListItem))( _
    Function(ent, A, B, pageNumber) From nt In Entities.NoteList _
    Where ent.A = A And ent.B = B _
    Order By ent.Date Descending, ent.Time Descending _
    Skip ((pageNumber - 1) * 10) _
    Take (10) _
    Select nt)

    Gives this error......
    Count must be a DbConstantExpression or a DbParameterReferenceExpression. Parameter name: Count

    • Edited by apalcer Thursday, June 25, 2009 2:24 PM added notes
    Thursday, June 25, 2009 2:23 PM
  • This may not help you, because I am not sure that Linqpad does VB, but do you use LinqPad?

    Break up the data before getting a list or ordering. Does it come back in a timely fashion? If so then add the skip, then ordering...where does it fail? That might provide a clue as you add more items to happen up to the toList.
    William Wegerson (www.OmegaCoder.Com)
    Thursday, June 25, 2009 3:15 PM
    Moderator
  • Hi apalcer,

    I doubt that compiledquery will make a difference on a simple query like this - most likely you're running into some kind of contention issue db-side (e.g. I/O contention, lock blocking, a 'bad' execution plan or similar). CompiledQuery will just cut down the time spent on optimizing and regenerating SQL client-side but on a query like the one posted this should be in the milliseconds anyway...

    I have a profiling tool for L2S that may help here. You can read an intro to it here: http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html , and you can download the toolkit itself and get a free 45-day trial license from http://www.huagati.com/L2SProfiler/

    Once installed, add a reference to the runtime profiling DLL and add a call to [datacontext].BeginProfiling before running the query and [datacontext].EndProfiling after running the query. It will write a log entry to the log directory passed in to BeginProfiling that you can view using the log viewer included... Alternatively, if you want help interpreting the log result, feel free to post the generated *.l2sprofiler file(s) involved to the L2S profiler forum at http://forum.huagati.com/forum4-huagati-linqtosql-profiler-support.aspx (or email to support@huagati.com ) and I'll be happy to give you my interpretation of it...

    Best regards,
    Kristofer
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    Thursday, June 25, 2009 3:28 PM
    Answerer
  • Something doesn't look right about your query. I think your getting your entity, local variable, and context confused. Assuming you are passing in your context as the first parameter, I think your query should look more like:

    ReadOnly query = System.Data.Objects.CompiledQuery.Compile(Of Entities, Integer, Integer, Integer, IQueryable(Of NoteListItem))( _
    Function(ent, A, B, pageNumber) From nt In
    ent.NoteList _
    Where nt.A = A And
    nt.B = B _
    Order By nt.Date Descending, nt.Time Descending
    _
    Skip
    ((pageNumber - 1) * 10) _
    Take
    (10) _
    Select
    nt)

    That being said, I agree with KristopherA and think that your problem is not in the expression tree compilation, but rather on a different tier. Compiling your query should help give you some boost, but typically only means sub-second performance improvements, which is helpful when called repeatedly (under web stress for example). In the case where the performance is 30 seconds per query, I suspect that the subsecond improvement that the compiled query will give isn't going to give you as much improvement as you are wanting. Your problem is more likely somewhere else in the query pipeline (database, cross domain serialization, etc). I would start by taking the TSQL that is generated from your query and running it directly on the database and see what performance that gives. If it is acceptable, try working your way out to see if you can spot the underlying bottleneck. I haven't tried KristopherA's profiler to know if it will solve your problem or not.

    As for Omega Man's question, LinqPad does have VB support (though the intellisense purchase option only applies to C#).

    Jim Wooley
    www.ThinqLinq.com

    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Thursday, June 25, 2009 8:42 PM
    Moderator