Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 18 | Comments: 72

filter by tags archive

ChallengeEfficient querying

time to read 2 min | 303 words

This issue came out in my Alexandria sample app. I wanted to have a Queue of books for each user, and each book has a collection of its authors. The model looks something like this:

image

And the physical data model:

image

So far, so good, and relatively simple and straightforward to work with, right?

Except, what kind of a query would you want to make to get all the books in the user’s queue? Here is the code that uses your results:

bus.Reply(new MyQueueResponse
{
    UserId = message.UserId,
    Timestamp = DateTime.Now,
    Queue = books.Select(book => new BookDTO
    {
        Id = book.Id,
        Image = book.Image,
        Name = book.Name,
        Authors = book.Authors.Select(x => x.Name).ToArray()
    }).ToArray()
});

Hint, note that we need to bring the Book’s image as well, and pay attention to the number of joins you require as well as the number of queries.

More posts in "Challenge" series:

  1. (28 Apr 2015) What is the meaning of this change?
  2. (26 Sep 2013) Spot the bug
  3. (27 May 2013) The problem of locking down tasks…
  4. (17 Oct 2011) Minimum number of round trips
  5. (23 Aug 2011) Recent Comments with Future Posts
  6. (02 Aug 2011) Modifying execution approaches
  7. (29 Apr 2011) Stop the leaks
  8. (23 Dec 2010) This code should never hit production
  9. (17 Dec 2010) Your own ThreadLocal
  10. (03 Dec 2010) Querying relative information with RavenDB
  11. (29 Jun 2010) Find the bug
  12. (23 Jun 2010) Dynamically dynamic
  13. (28 Apr 2010) What killed the application?
  14. (19 Mar 2010) What does this code do?
  15. (04 Mar 2010) Robust enumeration over external code
  16. (16 Feb 2010) Premature optimization, and all of that…
  17. (12 Feb 2010) Efficient querying
  18. (10 Feb 2010) Find the resource leak
  19. (21 Oct 2009) Can you spot the bug?
  20. (18 Oct 2009) Why is this wrong?
  21. (17 Oct 2009) Write the check in comment
  22. (15 Sep 2009) NH Prof Exporting Reports
  23. (02 Sep 2009) The lazy loaded inheritance many to one association OR/M conundrum
  24. (01 Sep 2009) Why isn’t select broken?
  25. (06 Aug 2009) Find the bug fixes
  26. (26 May 2009) Find the bug
  27. (14 May 2009) multi threaded test failure
  28. (11 May 2009) The regex that doesn’t match
  29. (24 Mar 2009) probability based selection
  30. (13 Mar 2009) C# Rewriting
  31. (18 Feb 2009) write a self extracting program
  32. (04 Sep 2008) Don't stop with the first DSL abstraction
  33. (02 Aug 2008) What is the problem?
  34. (28 Jul 2008) What does this code do?
  35. (26 Jul 2008) Find the bug fix
  36. (05 Jul 2008) Find the deadlock
  37. (03 Jul 2008) Find the bug
  38. (02 Jul 2008) What is wrong with this code
  39. (05 Jun 2008) why did the tests fail?
  40. (27 May 2008) Striving for better syntax
  41. (13 Apr 2008) calling generics without the generic type
  42. (12 Apr 2008) The directory tree
  43. (24 Mar 2008) Find the version
  44. (21 Jan 2008) Strongly typing weakly typed code
  45. (28 Jun 2007) Windsor Null Object Dependency Facility

Comments

NC
NC

Plural names?

You know if you applied Plural to your Prof licensing model you could have a Personal License! :o

Richard Dingwall

These days my brain is defaulting to "CQRS" for questions like this...

OmariO

If it were LINQ to SQL I would try the following:

var books = from uq in dc.UserWatingBooks

               where uq.UserId == 

<userid
let b = uq.Book

               select new BookDTO

               { 

                 Id          = b.Id, 

                 Name    = b.Name, 

                 Image    = b.Image, 

                 Authors = (from ba in b.BookAuthors

                                  select ba.Author.Name).ToArray()

                };

bus.Reply(new MyQueueResponse

{

UserId = message.UserId,

Timestamp = DateTime.Now,

Queue = books.ToArray()

});

Ayende Rahien

OmariO,

Think about the SQL that this will generate

Paul Batum

If we use something such as:

var books = session.CreateQuery("select b from Book as b join fetch b.Authors join b.WaitingUsers as u where u.Id = :userId")

                .SetParameter("userId", 1)                    

                .List

<book();

Where b.WaitingUsers is a the reverse side of the Queue relationship, but mapped as a Noop, then the downsides seems to be:

a) Unnecessary join to the User table - I can't see a way to tell NH it only has to go as far as the UsersWaitingBooks table to get the userId.

b) Book image repeated in results. I am thinking that this could be resolved using the new <property feature, and then use a multiquery with a seperate query for the image?

My SQL optimisation skills are pretty weak, I expect there is a much more elegant solution that doesn't rely on fancy mapping features...

Paul Batum

Sigh, I always forget this blog eats angle brackets.

By new feature above, I meant lazy properties.

(property name="Image" lazy="true")

ivos

What about adding a UserWaitingBook class instead of a many-to-many mapping? Also, there're more data that could be there (ok, it's not the requirement, but it could be), for example the date of the book request, the days the user wants to wait for the book, I don't know... but it could have more information, right?

so, with that model, you could do:

var books = session.CreateQuery("select b from WaitingUser u join fetch Book as b join fetch b.Authors where u.Id = :userId")

.SetParameter("userId", 1)

.List();

John Farrell

Doesn't being concerned about this go against the stance you posted on performance and ROI a couple of weeks ago?

I'd wait and see if it matters before tweaking my models or hand writing sql

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. RavenDB 3.0 New Stable Release - 7 hours from now
  2. Production postmortem: The industry at large - about one day from now
  3. The insidious cost of allocations - 2 days from now
  4. Buffer allocation strategies: A possible solution - 5 days from now
  5. Buffer allocation strategies: Explaining the solution - 6 days from now

And 3 more posts are pending...

There are posts all the way to Sep 11, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    01 Sep 2015 - The case of the lying configuration file
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats