+972 52-548-6969
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:
And the physical data model:
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:
- (01 Jul 2024) Efficient snapshotable state
- (13 Oct 2023) Fastest node selection metastable error state–answer
- (12 Oct 2023) Fastest node selection metastable error state
- (19 Sep 2023) Spot the bug
- (04 Jan 2023) what does this code print?
- (14 Dec 2022) What does this code print?
- (01 Jul 2022) Find the stack smash bug… – answer
- (30 Jun 2022) Find the stack smash bug…
- (03 Jun 2022) Spot the data corruption
- (06 May 2022) Spot the optimization–solution
- (05 May 2022) Spot the optimization
- (06 Apr 2022) Why is this code broken?
- (16 Dec 2021) Find the slow down–answer
- (15 Dec 2021) Find the slow down
- (03 Nov 2021) The code review bug that gives me nightmares–The fix
- (02 Nov 2021) The code review bug that gives me nightmares–the issue
- (01 Nov 2021) The code review bug that gives me nightmares
- (16 Jun 2021) Detecting livelihood in a distributed cluster
- (21 Apr 2020) Generate matching shard id–answer
- (20 Apr 2020) Generate matching shard id
- (02 Jan 2020) Spot the bug in the stream
- (28 Sep 2018) The loop that leaks–Answer
- (27 Sep 2018) The loop that leaks
- (03 Apr 2018) The invisible concurrency bug–Answer
- (02 Apr 2018) The invisible concurrency bug
- (31 Jan 2018) Find the bug in the fix–answer
- (30 Jan 2018) Find the bug in the fix
- (19 Jan 2017) What does this code do?
- (26 Jul 2016) The race condition in the TCP stack, answer
- (25 Jul 2016) The race condition in the TCP stack
- (28 Apr 2015) What is the meaning of this change?
- (26 Sep 2013) Spot the bug
- (27 May 2013) The problem of locking down tasks…
- (17 Oct 2011) Minimum number of round trips
- (23 Aug 2011) Recent Comments with Future Posts
- (02 Aug 2011) Modifying execution approaches
- (29 Apr 2011) Stop the leaks
- (23 Dec 2010) This code should never hit production
- (17 Dec 2010) Your own ThreadLocal
- (03 Dec 2010) Querying relative information with RavenDB
- (29 Jun 2010) Find the bug
- (23 Jun 2010) Dynamically dynamic
- (28 Apr 2010) What killed the application?
- (19 Mar 2010) What does this code do?
- (04 Mar 2010) Robust enumeration over external code
- (16 Feb 2010) Premature optimization, and all of that…
- (12 Feb 2010) Efficient querying
- (10 Feb 2010) Find the resource leak
- (21 Oct 2009) Can you spot the bug?
- (18 Oct 2009) Why is this wrong?
- (17 Oct 2009) Write the check in comment
- (15 Sep 2009) NH Prof Exporting Reports
- (02 Sep 2009) The lazy loaded inheritance many to one association OR/M conundrum
- (01 Sep 2009) Why isn’t select broken?
- (06 Aug 2009) Find the bug fixes
- (26 May 2009) Find the bug
- (14 May 2009) multi threaded test failure
- (11 May 2009) The regex that doesn’t match
- (24 Mar 2009) probability based selection
- (13 Mar 2009) C# Rewriting
- (18 Feb 2009) write a self extracting program
- (04 Sep 2008) Don't stop with the first DSL abstraction
- (02 Aug 2008) What is the problem?
- (28 Jul 2008) What does this code do?
- (26 Jul 2008) Find the bug fix
- (05 Jul 2008) Find the deadlock
- (03 Jul 2008) Find the bug
- (02 Jul 2008) What is wrong with this code
- (05 Jun 2008) why did the tests fail?
- (27 May 2008) Striving for better syntax
- (13 Apr 2008) calling generics without the generic type
- (12 Apr 2008) The directory tree
- (24 Mar 2008) Find the version
- (21 Jan 2008) Strongly typing weakly typed code
- (28 Jun 2007) Windsor Null Object Dependency Facility
Comments
Plural names?
You know if you applied Plural to your Prof licensing model you could have a Personal License! :o
These days my brain is defaulting to "CQRS" for questions like this...
If it were LINQ to SQL I would try the following:
var books = from uq in dc.UserWatingBooks
<userid
let b = uq.Book
bus.Reply(new MyQueueResponse
{
});
OmariO,
Think about the SQL that this will generate
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")
<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...
Sigh, I always forget this blog eats angle brackets.
By new feature above, I meant lazy properties.
(property name="Image" lazy="true")
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();
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