Fetch Strategy and Stored Procedures

7 posts, 0 answers
  1. Morgan McCollough
    Morgan McCollough avatar
    34 posts
    Member since:
    May 2010

    Posted 02 Mar 2012 Link to this post

    I've been evaluating Telerik ORM and trying to decide whether we can use it instead of the Entity Framework in a Silverlight Business app I am in charge of. I have run into an issue involving lazy loading. I was somewhat disappointed to see that it could not be turned off. Having it turned on by default when using RIA and Silverlight doesn't seem like a very good idea, because you easily have the potential for huge object graphs to be automatically fetched from the database during the serialization operations.

    Anyway, I was trying to understand how to work around this with fetch strategies, but I'm not sure exactly how to approach this with stored procedures. Basically, we have a significant number of stored procedures that already return all information needed from various tables using multiple result sets. We also have a number of other instances where all necessary values are loaded in a single domain service by making several separate stored procedure calls.

    However, with Telerik ORM, I'm not sure how to prevent the context from automatically loading all related items even after the stored procedure has already brought back all the necessary information. Is there a way to apply a fetch plan to a stored procedure?

    For example, I have one SPROC that brings back information from two related tables in two different result sets. If I retrieve the reader and then translate the 2 result sets to the appropriate entities, I should have all the values I need. But, when I run a trace, I see that the Telerik ORM is automatically fetching all the related items again. There are very often more than 1000 results from this query (and many others in the app), and this kind of lazy loading results in more than 1000 calls to the DB. Our DBA especially gets very leary about this.

    Below is an example of how I'm calling the stored procedure using the GetReader method generated along with the Domain Method. Can you advise on how to do this differently or apply a fetch plan to this?

    public IQueryable<Costing> GetCostings()
            {
                IEnumerable<Costing> costings;
                using (var reader = DataContext.GetReaderForusp_search_default2(null, 1, 65, 1, "joeuser", true, 1, 1, 26))
                {
                    costings = DataContext.Translate<Costing>(reader).ToList();
                    reader.NextResult();
                    var refs = DataContext.Translate<Reference>(reader).ToList();
                    reader.NextResult();
                    var prods = DataContext.Translate<Product>(reader).ToList();
                }
                return costings.AsQueryable();
            }
  2. Morgan McCollough
    Morgan McCollough avatar
    34 posts
    Member since:
    May 2010

    Posted 02 Mar 2012 Link to this post

    The other issue I thought about is how to prevent Telerik ORM from loading ALL related items automatically. There are plenty of scenarios in the Silverlight app that we have where we want to load a certain subset of related items. This is where turning off lazy loading and doing explicit eager loading comes in very handy.
  3. DevCraft banner
  4. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 05 Mar 2012 Link to this post

    Hi Morgan,

    I am afraid that fetch strategies cannot be applied on stored procedures. You could improve the behavior in your application by using materialization to persistent types. In this case the current item from the result set will be materialized only on demand. Otherwise in case of materialization to non-persistent type all the items will be translated on calling the CLR method which executes to stored procedure.
    I am sorry for the inconvenience caused.

    Greetings,
    Damyan Bogoev
    the Telerik team
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
  5. Morgan McCollough
    Morgan McCollough avatar
    34 posts
    Member since:
    May 2010

    Posted 09 Mar 2012 Link to this post

    The types referenced in the code sample I posted are all persistent types. So, isn't materialization what I'm already doing?

    What seems to be happening is that only what I explicitly load as a result of the stored procedure comes into memory on the server, but once the result gets returned from the DomainService method and serialization to the Silverlight client begins, Telerik ORM seems to be trying to load all other related items. Is this possibly because the property references done as a result of the serialization process trigger the lazy load? In my situation this results in a pretty large object model being sent down to the client and the whole process taking about 3-4 times longer than it would if it only sent down the original results returned from the SPROC.

    If I'm right about the serialization process triggering lazy load, that seems like a major flaw to me for using this in the context of RIA services business apps.

    Any other ideas?
  6. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 13 Mar 2012 Link to this post

    Hello Morgan McCollough,

    You can disable transporting the associations’ information through the RIA Service by removing the AssociationAttribute from the referenced properties in your domain model. This will avoid loading the all the information for the related items.
    Hope that helps. 

    Greetings,
    Damyan Bogoev
    the Telerik team
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
  7. Morgan McCollough
    Morgan McCollough avatar
    34 posts
    Member since:
    May 2010

    Posted 14 Mar 2012 Link to this post

    Unfortunately, that's not an option. If I remove the association attributes, those associations are no longer reflected down to the Silverlight client. In other words, the entity code generation for the Silverlight stuff will not create properties for associated entities unless both the include and the association attributes are present in the metadata on the server.

    Our current code really heavily relies upon those associations existing on the client, so removing them would imply massive changes. If there is no other way around this, I'm afraid this is kind of a deal breaker. I would like the use Telerik ORM. The designer is definitely nicer, among other things.

    Is it on the roadmap to apply fetch strategies to stored procedures or turn off lazy loading? It would seem to me that without that, you are kind of excluding yourselves from use on enterprise business Silverlight apps unless most people are just using LINQ...
  8. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 16 Mar 2012 Link to this post

    Hello Morgan McCollough,

    Firstly I want to apologize for the inconvenience caused.

    I am afraid that we do not plan to implement support for fetch strategies with stored procedures.

    Actually you could use a Linq query to read the data from database instead of using the stored procedure. You could apply the necessary fetch strategies and used them in the Linq query. This will resolve the performance problems you are facing on your side.

    Hope that helps. If any other questions arise, do not hesitate to contact us back.


    Regards,
    Damyan Bogoev
    the Telerik team
    Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
Back to Top
DevCraft banner