Ayende @ Rahien

It's a girl

Challenge: Premature optimization, and all of that…

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.

Comments

Rafal
02/16/2010 10:19 AM by
Rafal

Looks like RSB handles message batches in a single transaction. Is it a bug or feature? BTW, serializable isolation level is almost guaranteed to cause problems in real world application.

Ayende Rahien
02/16/2010 11:28 AM by
Ayende Rahien

Rafal,

Yes, that IS a feature.

And serializable isolation is actually a good thing in some circumstances. For one, it make it really simple to think about concurrency, and if all our data is user scoped, we aren't going to deal with a lot of complexity either.

Rafal
02/16/2010 11:49 AM by
Rafal

Ah, I thought that sending an array of message is there only for performance reasons or developer convenience, didn't know about this little side effect.

Felix
02/16/2010 11:56 AM by
Felix

Uhmm,

If the session crunching the messages is the same for all the messages in this example, then the first level cache is already an optimization. Eagerly fetch more then one collection at a time may results in a cartesian product query, that is worst than executing the 3 query to load the collection when needed.

Jan Willem B
02/16/2010 12:07 PM by
Jan Willem B

Since one session is used, calls to session.Get <user(message.UserId) will only hit the database once.

The first time this is done, the books, queues and collections are eagerly loaded (if specified, you can't tell from the queries), but in a seperate select statements. I would not set fetchmode to join because this will lead to cartesian product. Further optimization could be to use a multicriteriaquery to send the three queries at once. But that would be an MS SQL specific optimization.

simone basso
02/16/2010 10:06 PM by
simone basso

may be grouping all those queries into the same transaction might cause some more deadlocks?

Ayende Rahien
02/18/2010 08:26 AM by
Ayende Rahien

Simone,

Can you envision a way in which two concurrent transactions (even for the same user) can deadlock with this code?

simone basso - smnbss
02/19/2010 12:06 PM by
simone basso - smnbss

The problem in fact is with the type of transaction you're using

A transaction with Serializable Isolation places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.

Grouping those 4 queries on the same transaction will eventually put some more stress to the database if used in conjunction with a serializable transaction .

here I wrote a little bit more :)

http://smnbss.spaces.live.com/blog/cns!A117AA5E007A0648!1894.entry?&c02vws=1

Ayende Rahien
02/20/2010 03:13 PM by
Ayende Rahien

Simone,

All the data that I am accessing is local to the current user

simone basso
02/20/2010 07:21 PM by
simone basso

Sure, in your specific case it might not be a problem, but if you have other services accessing the same data with other queries it might create some problems. BTW, what is the problem then with that query? :)

Comments have been closed on this topic.