Ayende @ Rahien

Refunds available at head office

Challenge: Efficient querying

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.

Comments

NC
02/12/2010 11:59 AM by
NC

Plural names?

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

Richard Dingwall
02/12/2010 12:02 PM by
Richard Dingwall

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

OmariO
02/12/2010 12:32 PM by
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
02/12/2010 01:35 PM by
Ayende Rahien

OmariO,

Think about the SQL that this will generate

Paul Batum
02/12/2010 03:40 PM by
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
02/12/2010 03:43 PM by
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
02/12/2010 04:38 PM by
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
02/12/2010 06:51 PM by
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

Comments have been closed on this topic.