Eager Loading with OpenAccess ORM

Thread is closed for posting
4 posts, 0 answers
  1. k f
    k f avatar
    109 posts
    Member since:
    Jun 2006

    Posted 18 Nov 2008 Link to this post

    Hello,

    I'm currently use LLBL Gen pro as my ORM.  I was poised to make the transition to Linq to Sql but found a serious flaw in the design/performance that has caused me to rule it out at this time.

    Having a telerik subscription I am curious in evaluating this product but I need to know up front if Open Access supports the ability to prefetch as many tables as needed with any limitations.

    An example of what I'm talking about.  Let's take the Northwind database.  Say I want to create a middle tier endpoint called GetEmployeeCustomers.

    My entity in my Middle tier will contain data from the Employee, Customer, Order and Order Details database.  I want all this info back at the same time.

    With Linq to Sql you can only prefetch 2 tables at at time so this query would resort to lazy loading after prefetching employee and customer tables together.  The result would probably be 150 to 300 database calls.  Whether as with LLBL Gen Pro, there would be 4.

    In looking through the online comments aboutu the Telerik ORM, I ran across the following.

    "Fetch Plan is used to optimal fetch the initial result of the query and to navigate to a data that is not yet in memory. This allows you to optimize navigation on a generic node tree and always fetch 3 levels together."

    Could somebody at Telerik please clarify that this statement means that Telerik ORM suffers the same problem as Linq to Sql in this regard?  Or conversely can you verify that your ORM supports unlimited Prefetches of tables for eager loading.

    Another question to add here, does your ORM give you a equivalent to the Adapter model with LLBL Gen pro.  For my needs, the main importance of this feature for my environment is it forces the developers to prefetch any tables they need data for and effectively disables lazy loading.  WHich is key in trying to prevent bad code from destroying performance if the persons writing the code don't pay attention to what they are doing?

    Thanks.




  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 18 Nov 2008 Link to this post

    Hello k f,

    OpenAccess can fetch from as many tables as you want in parallel. Even better, if you have collections in your model there is a way to execute only one additional query per collection independent how big your first result is.One big difference to others is that this mechanism is also working during lazy loading. That means if you navigate to a not loaded object the plan will optimize this fetch also.

    For n-tier we have two solutions, one is similar to the adapter, just serialize the data to the client and take care of change tracking, lazy loading by yourself, the other is our ObjectContainer doing all management work for you. You definitely should have a look.


    You should try it out.

    Kind regards,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  3. k f
    k f avatar
    109 posts
    Member since:
    Jun 2006

    Posted 18 Nov 2008 Link to this post

    Hello Jan,

    I'm not 100% if the questions were asked.  The question was how many level can you prefetch?

    To make this easier, in the 4 tables I mentioned above if I want to get everything from them.  How many queries to sql would I see with your product?  4 such as with LLBL Gen or 200+ as would be the case with Linq To SQL.

    Also for this
    "For n-tier we have two solutions, one is similar to the adapter, just serialize the data to the client and take care of change tracking, lazy loading by yourself, the other is our ObjectContainer doing all management work for you. You definitely should have a look."

    I create my own DTO objects in my N-tier apps so if I ever want to can a ORM for something else I can easily.  Also, I don't have to rely on 3rd party entities for serialization.  WIth that said, what I'm asking is there a template for building the entities with your ORM that will disable Lazy Loading and require the developer to prefetch the tables they need info or else throw an exception.

    Lazy Loading has it's place but can be extremely dangerous.  I've been witness to a single page that should have made 100 database calls to load per user end up making 40k plus because of bad practices and not understanding lazy loading.  So I work from the other way, I do not allow it in any environment unless it is determined there is a real benefit for it.

    So, in summary, 1) how many queries would I see from your ORM if I created an endpoint to northwind that needed to retrieve Employees, their customers, the customers orders and order details all at once?

    2)  Is there a template or whatever you call it that is equivalent to LLBL Gen's adapter model that prevents lazy loading and requires developers to specifically prefetch the tables for the data they need to return?

    Thanks.

  4. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 18 Nov 2008 Link to this post

    Hello k f,

    1. The amount of queries depends on the kind of references in your model. If there are references to the 3 additional tables we will join them into the first result, If there are collections (backward FKs in the db) we will have 3 additional queries. Means something between 1 and 4.
    2. No, with FetchPlan the idea is, that you look at the slow page, find out which data is loaded and just set a plan for this page or business case. Most of the pages are working well, only some are executing to many queries, you can also count the amount od sql statements per page and do your optimization with that input.

    All the best,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Back to Top