none
.Any() performance RRS feed

  • Question

  • Hi all!

    I'm one of the moderators of the LINQ forum in the portuguese version of the MSDN forums.

    We ended up in a discussion there about the performance of the .Any() method in LINQ to SQL / EF.

    Basically, there are some guys who say that it's better to avoid using the Any() method if you'll be using the result of the query to do something later on and, instead of using the Any() call, we should convert the result to a List (ToList) and check the Count of items in this list. Theoretically this would avoid executing the query twice.

    I ran some tests and figured out that both scenarios execute in the same amount of time, what made me think that, maybe, .NET stores the result of the query in memory right after calling the Any() method, so that it wouldn't be necessary to re-execute the query to get the results later on, if needed. Is it true?

    Let me show you the two scenarios that executed in the same amount of time:

    1) Call using Any():

    var result = from p in db.Products
        where p.ListPrice > 10 &&
         p.Color != string.Empty
        select p;
    
    if (result.Any())
     // Do something with the result of the query.
    

    2) Call using ToList():

    var result = from p in db.Products
        where p.ListPrice > 10 &&
         p.Color != string.Empty
        select p;
    
    List<Product> prods = result.ToList();
    
    if (prods.Count > 0)
     // Do something with the result of the query.
    

    Now, the big question... Which of the two ways would be the most correct (and most efficient)?

    Thanks a lot in advance!


    André Alves de Lima
    Visite o meu site: http://andrealveslima.spaces.live.com
    Me siga no Twitter: @andrealveslima
    Monday, June 7, 2010 9:37 PM

Answers

  • #1 will do two db roundtrips, #2 will do a single db roundtrip. That makes #2 more efficient if you always process the result.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, June 8, 2010 2:24 AM
    Answerer
  • Indeed calling Any and other operators described in the documentation will execute the query so if you want that same data used in some other query, then is common sense to store it in a collection, but take this into account, first the info there won't change, so if the database info has been modified then another roundtrip is needed if you need the new info, also when asking information to the database, you only receive was has changed (if you are not cacheing the data), reducing the ammount of data send to the client (optimist concurrency), so all in all there is not a correct way in my way of thinking, is dependant on your case and your needs.

    Also if we are going to be through then it will be like this

    List<Product> prods = (from p in db.Products
        where p.ListPrice > 10 &&
         p.Color != string.Empty
        select p).ToList();
    Why go to all the trouble to maintain a query expresion if you are not going to use it?

    PS: If efficiency is all you need then stored procedures or plain SQL will be a bit better (in the case of stored procedures vs SQL there is also some pros and cons)

    Wednesday, June 9, 2010 12:40 PM

All replies

  • #1 will do two db roundtrips, #2 will do a single db roundtrip. That makes #2 more efficient if you always process the result.


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, June 8, 2010 2:24 AM
    Answerer
  • Kristofer, that is what I thought as well, but no, both methods use one round trip because a query is not sended to the database untill you use it so

    var result = from p in db.Products
        where p.ListPrice > 10 &&
         p.Color != string.Empty
        select p;

    Only store the query expresion but doesn't execute it (deferred execution)

    When Any is called then is executed (one roundtrip) so that point is moot

    The use of one over the other is dependant on usage.

    Hope this help

    Tuesday, June 8, 2010 3:39 PM
  • Serguey,

    After some Sql Profiling sections over here I figured out that Kristofer is right...

    Just one thing... We're not telling that just creating the query would perform a db roundtrip, but, calling Any() will, in fact, execute a round trip and, if I need to use the query results later on, another roundtrip will be made...

    This doesn't happen with the second call, in which only one roundtrip is made...

    Anyway, thanks for the replies!


    André Alves de Lima
    Visite o meu site: http://andrealveslima.spaces.live.com
    Me siga no Twitter: @andrealveslima
    Tuesday, June 8, 2010 9:25 PM
  • Indeed calling Any and other operators described in the documentation will execute the query so if you want that same data used in some other query, then is common sense to store it in a collection, but take this into account, first the info there won't change, so if the database info has been modified then another roundtrip is needed if you need the new info, also when asking information to the database, you only receive was has changed (if you are not cacheing the data), reducing the ammount of data send to the client (optimist concurrency), so all in all there is not a correct way in my way of thinking, is dependant on your case and your needs.

    Also if we are going to be through then it will be like this

    List<Product> prods = (from p in db.Products
        where p.ListPrice > 10 &&
         p.Color != string.Empty
        select p).ToList();
    Why go to all the trouble to maintain a query expresion if you are not going to use it?

    PS: If efficiency is all you need then stored procedures or plain SQL will be a bit better (in the case of stored procedures vs SQL there is also some pros and cons)

    Wednesday, June 9, 2010 12:40 PM
  • Serguey,

    Yep. You're right. The thing is that I was in doubt if .NET would keep in memory somehow the result of the query for a certain amount of time, so that an additional roundtrip wouldn't be necessary if the data was already in cache.

    But, that's not the way that it works. Each time I use the query result (stored in the "var result"), LINQ will do another roundtrip to the database.

    Thanks for the additional information!


    André Alves de Lima
    Visite o meu site: http://andrealveslima.spaces.live.com
    Me siga no Twitter: @andrealveslima
    Wednesday, June 9, 2010 1:08 PM