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>();
    }
  }
}
Tagged as LightSpeed, Products

16 Responses to “LightSpeed Passes Mats’ Challenge”

  • […] took the challengetoday and we passed. digg_bgcolor = ‘transparent’; digg_skin = […]

  • 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

  • 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

  • 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.

  • 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

  • “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

  • 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

  • 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

  • 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.

  • 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.

  • […] Peters from MindScape just posted that their mapper LightSpeed passes the challenge. At first I thought he must have misread the […]

  • 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,

    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,

    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

  • Oh, and does LightSpeed have a product page, so I can add a link to it in the post about your triumphant victory? :-)

    /Mats

  • > 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.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top