Mindscape Blog » Blog Archive » LightSpeed Passes Mats’ Challenge

LightSpeed Passes Mats’ Challenge

Mats Helander, creator of the NPersist O/R Mapper has issued a challenge to other O/R Mapper developers. To pass the challenge, an O/RM needs to be able to load a complete hierarchy of Customers, Orders and OrderLines as efficiently as possible by emitting exactly ~~a maximum of~~ three queries:

SELECT * FROM Customers
SELECT * FROM Orders
SELECT * FROM OrderLines

I’m happy to report that LightSpeed passes the test and does it with only one query. Here is the test output from running Mats’ provided test:

------ Test started: Assembly: OrmChallenge.Domain.dll ------
 
 
 
SELECT
  Customers.Id,
  Customers.CustomerName
FROM
  Customers;
 
SELECT
  Orders.Id,
  Orders.IsShipped,
  Orders.CustomerId
FROM
  Orders;
 
SELECT
  OrderLines.Id,
  OrderLines.Total,
  OrderLines.OrderId
FROM
  OrderLines
 
(25 ms)
 
 
1 passed, 0 failed, 0 skipped, took 1.28 seconds.

With LightSpeed, this is of course done using the EagerLoad attribute.

UPDATE: Here is the implementation of Mapper:

using System.Collections.Generic;
 
using Mindscape.LightSpeed|>;
using Mindscape.LightSpeed|>.Logging;
 
namespace OrmChallenge.Domain
{
  public class Mapper
  {
    static Mapper()
    {
      LightSpeedContext.ConnectionString = Database.ConnectionString;
      LightSpeedContext.Logger = new ConsoleLogger();
      LightSpeedContext.PluralizeTableNames = true;
    }
 
    internal static IList<Customer> LoadCustomersOrdersAndOrderLines()
    {
      return Repository.Find<Customer>();
    }
  }
}

Add your comments
Author icon

Posted by Andrew Peters on 26 June 2007

16 Responses to “LightSpeed Passes Mats’ Challenge”

[...] took the challengetoday and we passed. digg_bgcolor = ‘transparent’; digg_skin = [...]
Andrew Peters’ Blog » Blog Archive » LightSpeed Passes Mats’ Challenge Jun 26th, 2007
Andrew,
Copy-pasting over my reply to your comment in my blog:
Andrew,
Thanks for your contribution! :-)
The code looks very cool, but if I understand correctly your example will use only one sql query (using joins?) which is not allowed. I see in your post that you say “as efficiently as possible by emitting a maximum of three queries” (my bold). Usually, in an O/R test, that would indeed be the challenge (as for example with the TORPEDO test) but in this case, the extra hard part is that doing it with just one sql statement is /not/ allowed – you have to use those three statements, no fewer, no more.
Nonetheless, I’d say you have a pretty slick mapper going there, just judging from the syntax in the example!
Please feel free to post updated examples if you like. In my opinion I think it will be very hard (except possibly for a very custom mapper, see Nick’s post!) for a generic mapper to actually solve my challenge, and so I do think it is very interesting as well to see how /close/ different mappers can get, so please feel free to post examples of how close you can get as well (as Frans has done above)
/Mats
Mats Helander Jun 26th, 2007
I can also add that I do think it would be an interesting challenge, albeit another one, to see which mappers can solve it using just one sql statement as well!
For the record, NPersist (my mapper) wouldn’t pass that challenge!
And I do have to say that I’m pretty damn impressed that in your case, your successful implementation of that challenge would be a one-liner….:-O
/Mats
Mats Helander Jun 26th, 2007
Hi Mats,
Thanks for clarifying. LightSpeed will emit those three queries exactly as stated in my post with no joins (I copied the SQL straight out of the VS console window.) They must however be batched into a single database call that returns multiple readers.
Cheers,
Andrew.
Andrew Peters Jun 26th, 2007
I am in awe! :-O
As I note in my blog, you then indeed pass the challenge! I am certainly hugely impressed!
I invite you, if you want, to continue discussing a bit about, well, how the heck you managed to pull this off? :-))
/Mats
Mats Helander Jun 26th, 2007
“and does it with only one query”
This is what confused me. Perhaps you should change it to “one call to the database” (because that is what you meant, right?)
/Mats
Mats Helander Jun 26th, 2007
Hi Andrew,
Does that mean that you’ve specifically coded for the unfiltered case — where if the root collection is filled with a “select *”, you determine that the eager fetching is also a “select *” based upon the relationship type?
Not often that want to load a whole tree of the database into memory, but when I do, I guess it pays to be as efficient as possible.
Cool.
Kirk
Kirk Jackson Jun 26th, 2007
Amazing! Now I understand! :-) (I followed the “Eager” link)
The information that I wanted to put in a new type of DSL in my posts about Eager Loading “Hints” is neatly contained in your named aggregates! Completely brilliant and /exactly/ the type of good thinking about the problem that I was looking for!
So, you indeed pass the challenge, and your answer to the problem behind the challenge is named aggregates. My hat is well and truely off!
/Mats
Mats Helander Jun 26th, 2007
Hi Mats,
Thanks!
Named aggregates are not essential for this just the EagerLoad attribute. Named aggregates simply allow for varying eager/lazy behavior at the association level per use case.
Cheers,
Andrew.
Andrew Peters Jun 26th, 2007

Kirk,

Good spotting. If no criteria have been set and we are eagerly following a dependent association (fk not null), then we can omit the filtering normally required to return the right set of child objects.

So, for the more common case:

// contrived example:
 
Repository.Find<Customer>(Entity.Attribute("Id") > 0)

We end up with:

SELECT
  Customers.Id,
  Customers.CustomerName
FROM
  Customers
WHERE
  Customers.Id > 0;
 
SELECT
  Orders.Id,
  Orders.IsShipped,
  Orders.CustomerId
FROM
  Orders
WHERE
  Orders.CustomerId > 0;
 
SELECT
  OrderLines.Id,
  OrderLines.Total,
  OrderLines.OrderId
FROM
  OrderLines
WHERE
  EXISTS (
    SELECT
      Orders.*
    FROM
      Orders
    WHERE
      Orders.Id = OrderLines.OrderId AND
      EXISTS (
        SELECT
          Customers.*
        FROM
          Customers
        WHERE
          Customers.Id = Orders.CustomerId AND
          Customers.Id > 0
      )
  )

Still single batch, 3 selects however.

Cheers,

Andrew.

Andrew Peters Jun 26th, 2007

[...] Peters from MindScape just posted that their mapper LightSpeed passes the challenge. At first I thought he must have misread the [...]
Mats Helander Weblog » Blog Archive » LightSpeed from MindScape Passes The O/R Mapping Challenge Jun 26th, 2007
Hi Andrew,
“Named aggregates are not essential for this”,
The part that would be essential (I think) is to be able to tell the mapper when it will be alright to resolve both sides of the relationships it touches when filling the objects. I think that stating “My query is againist a named aggregate” in the query, and letting the mapper provide the secondary sql statements from that information, provides exactly the information I was looking for.
In your new sql example to Kirk,
Why do you use a subselect in the third statement instead of just a join (in the where clause! we’re not talking about joining in tables into the result, getting Cartesian products)? Is it because it requires less transformation (you can basically just wrap the original query up into the subquery) ?
/Mats
Mats Helander Jun 26th, 2007
Mats,
Good question! The reason for the subselect is because it better represents my intent: Give me *only* OrderLines that have Orders from a Customer whose Id > 0.
See: http://andrewpeters.net/2006/09/28/quote-of-the-day-from-oracles-ask-tom/ for a bit more info.
As discussed in that post, I’m relying on the DB optimizer to figure things out. If it turns out that a join based strategy is consistently faster then I will need to review the implementation. With respect to it requiring less-transformation, an early iteration of the query builder used a join strategy and I seem to recall it was easier to implement.
Cheers,
Andrew.
Andrew Peters Jun 26th, 2007
Andrew,
Interesting!
And while it looked at a glance like using subselects could be easier, I discussed it a bit with Roger Johansson and it quickly became apparent that it probably is much harder, yes.
/Mats
Mats Helander Jun 26th, 2007
Oh, and does LightSpeed have a product page, so I can add a link to it in the post about your triumphant victory? :-)
/Mats
Mats Helander Jun 26th, 2007
> And while it looked at a glance like using subselects could be easier, I discussed it a bit with
> Roger Johansson and it quickly became apparent that it probably is much harder, yes.
Resulting in much premature baldness :-)
We are currently in beta and will be releasing in the next couple of weeks. For now you could link to: http://www.mindscape.co.nz/blog/?p=23 and we also have an EAP site: http://eap.mindscape.net.nz/
Cheers,
Andrew.
Andrew Peters Jun 26th, 2007