ChallengePremature optimization, and all of that…

time to read 6 min | 1174 words

Let us look at the following pieces of code:

public void Consume(MyBooksRequest message)
{
    var user = session.Get<User>(message.UserId);
    
    bus.Reply(new MyBooksResponse
    {
        UserId = message.UserId,
        Timestamp = DateTime.Now,
        Books = user.CurrentlyReading.ToBookDtoArray()
    });
}

public void Consume(MyQueueRequest message)
{
    var user = session.Get<User>(message.UserId);

    bus.Reply(new MyQueueResponse
    {
        UserId = message.UserId,
        Timestamp = DateTime.Now,
        Queue = user.Queue.ToBookDtoArray()
    });
}

public void Consume(MyRecommendationsRequest message)
{
    var user = session.Get<User>(message.UserId);

    bus.Reply(new MyRecommendationsResponse
    {
        UserId = message.UserId,
        Timestamp = DateTime.Now,
        Recommendations = user.Recommendations.ToBookDtoArray()
    });
}

Looking at this, I see that I have a requirement to getting my books, my queues and my recommendations. It appears that getting each datum is going to result in 2 queries, the first to load the User, and the second to lazy load the actual collection that we want to return.

An almost trivial optimization would be to eliminate the lazy loading, right? That would reduce the cost from 6 queries to just 3.

However, that assumption would be wrong. The following client code:

bus.Send(
    new MyBooksRequest
    {
        UserId = userId
    },
    new MyQueueRequest
    {
        UserId = userId
    },
    new MyRecommendationsRequest
    {
        UserId = userId
    });

Produces this SQL:

-- statement #1
enlisted session in distributed transaction with isolation level: Serializable

-- statement #2
SELECT user0_.Id          as Id2_0_,
       user0_.Name        as Name2_0_,
       user0_.Street      as Street2_0_,
       user0_.Country     as Country2_0_,
       user0_.City        as City2_0_,
       user0_.ZipCode     as ZipCode2_0_,
       user0_.HouseNumber as HouseNum7_2_0_
FROM   Users user0_
WHERE  user0_.Id = 1 /* @p0 */

-- statement #3
SELECT currentlyr0_.[User] as User1_1_,
       currentlyr0_.Book   as Book1_,
       book1_.Id           as Id0_0_,
       book1_.Name         as Name0_0_,
       book1_.ImageUrl     as ImageUrl0_0_,
       book1_.Image        as Image0_0_,
       book1_.Author       as Author0_0_
FROM   UsersReadingBooks currentlyr0_
       left outer join Books book1_
         on currentlyr0_.Book = book1_.Id
WHERE  currentlyr0_.[User] = 1 /* @p0 */

-- statement #4
SELECT queue0_.[User]  as User1_1_,
       queue0_.Book    as Book1_,
       queue0_.[Index] as Index3_1_,
       book1_.Id       as Id0_0_,
       book1_.Name     as Name0_0_,
       book1_.ImageUrl as ImageUrl0_0_,
       book1_.Image    as Image0_0_,
       book1_.Author   as Author0_0_
FROM   UsersWaitingBooks queue0_
       left outer join Books book1_
         on queue0_.Book = book1_.Id
WHERE  queue0_.[User] = 1 /* @p0 */

-- statement #5
SELECT recommenda0_.[User]  as User1_1_,
       recommenda0_.Book    as Book1_,
       recommenda0_.[Index] as Index3_1_,
       book1_.Id            as Id0_0_,
       book1_.Name          as Name0_0_,
       book1_.ImageUrl      as ImageUrl0_0_,
       book1_.Image         as Image0_0_,
       book1_.Author        as Author0_0_
FROM   UsersRecommendedBooks recommenda0_
       left outer join Books book1_
         on recommenda0_.Book = book1_.Id
WHERE  recommenda0_.[User] = 1 /* @p0 */


-- statement #7
commit transaction

That seems strange, can you figure out why?

Bonus points for figuring out whatever it would be worth it to do the eager load optimization or not.

More posts in "Challenge" series:

  1. (03 Jun 2022) Spot the data corruption
  2. (06 May 2022) Spot the optimization–solution
  3. (05 May 2022) Spot the optimization
  4. (06 Apr 2022) Why is this code broken?
  5. (16 Dec 2021) Find the slow down–answer
  6. (15 Dec 2021) Find the slow down
  7. (03 Nov 2021) The code review bug that gives me nightmares–The fix
  8. (02 Nov 2021) The code review bug that gives me nightmares–the issue
  9. (01 Nov 2021) The code review bug that gives me nightmares
  10. (16 Jun 2021) Detecting livelihood in a distributed cluster
  11. (21 Apr 2020) Generate matching shard id–answer
  12. (20 Apr 2020) Generate matching shard id
  13. (02 Jan 2020) Spot the bug in the stream
  14. (28 Sep 2018) The loop that leaks–Answer
  15. (27 Sep 2018) The loop that leaks
  16. (03 Apr 2018) The invisible concurrency bug–Answer
  17. (02 Apr 2018) The invisible concurrency bug
  18. (31 Jan 2018) Find the bug in the fix–answer
  19. (30 Jan 2018) Find the bug in the fix
  20. (19 Jan 2017) What does this code do?
  21. (26 Jul 2016) The race condition in the TCP stack, answer
  22. (25 Jul 2016) The race condition in the TCP stack
  23. (28 Apr 2015) What is the meaning of this change?
  24. (26 Sep 2013) Spot the bug
  25. (27 May 2013) The problem of locking down tasks…
  26. (17 Oct 2011) Minimum number of round trips
  27. (23 Aug 2011) Recent Comments with Future Posts
  28. (02 Aug 2011) Modifying execution approaches
  29. (29 Apr 2011) Stop the leaks
  30. (23 Dec 2010) This code should never hit production
  31. (17 Dec 2010) Your own ThreadLocal
  32. (03 Dec 2010) Querying relative information with RavenDB
  33. (29 Jun 2010) Find the bug
  34. (23 Jun 2010) Dynamically dynamic
  35. (28 Apr 2010) What killed the application?
  36. (19 Mar 2010) What does this code do?
  37. (04 Mar 2010) Robust enumeration over external code
  38. (16 Feb 2010) Premature optimization, and all of that…
  39. (12 Feb 2010) Efficient querying
  40. (10 Feb 2010) Find the resource leak
  41. (21 Oct 2009) Can you spot the bug?
  42. (18 Oct 2009) Why is this wrong?
  43. (17 Oct 2009) Write the check in comment
  44. (15 Sep 2009) NH Prof Exporting Reports
  45. (02 Sep 2009) The lazy loaded inheritance many to one association OR/M conundrum
  46. (01 Sep 2009) Why isn’t select broken?
  47. (06 Aug 2009) Find the bug fixes
  48. (26 May 2009) Find the bug
  49. (14 May 2009) multi threaded test failure
  50. (11 May 2009) The regex that doesn’t match
  51. (24 Mar 2009) probability based selection
  52. (13 Mar 2009) C# Rewriting
  53. (18 Feb 2009) write a self extracting program
  54. (04 Sep 2008) Don't stop with the first DSL abstraction
  55. (02 Aug 2008) What is the problem?
  56. (28 Jul 2008) What does this code do?
  57. (26 Jul 2008) Find the bug fix
  58. (05 Jul 2008) Find the deadlock
  59. (03 Jul 2008) Find the bug
  60. (02 Jul 2008) What is wrong with this code
  61. (05 Jun 2008) why did the tests fail?
  62. (27 May 2008) Striving for better syntax
  63. (13 Apr 2008) calling generics without the generic type
  64. (12 Apr 2008) The directory tree
  65. (24 Mar 2008) Find the version
  66. (21 Jan 2008) Strongly typing weakly typed code
  67. (28 Jun 2007) Windsor Null Object Dependency Facility