LINQ ORM Memory Problems (Joins)

Thread is closed for posting
2 posts, 0 answers
  1. Kyle Pietschmann
    Kyle Pietschmann avatar
    3 posts
    Member since:
    Jan 2010

    Posted 31 May 2011 Link to this post

    I just wanted to share a solution to a serious problem we had.  After bringing our code to production, the performance began deteriorating as the production tables began to grow.  There was a problem with our code that didn't look like a problem, involving joins.

    Take this query for example:
    var joinQuery = from a in custList
                                        join b in scope.Extent<Ge.Wf.FieldServices.InspectionData.Data.CustomersExtension>()
                                        on a.CustomerNumber equals b.CustomerNumber
                                        join c in scope.Extent<Ge.Wf.FieldServices.InspectionData.Data.InspectionScheduleCustomer>()
                                        on a.CustomerNumber equals c.CustomerNumber
                                        where (c.InspectionId == guidInspectionId && b.CustomerNumber == customerNumber && b.AllianceId== allianceId  )
                                        select new

    At first, we thought maybe the double join is what is doing it, and Telerik ORM has a weakness with multiple joins.  Turns out, the problem is actually quite logical.  We are joining this list in memory (custList) to a database table.  How can the ORM perform this join on the SQL server and only provide the results asked for by the where clause and the join criteria?  The answer is that it cannot.  It is forced to bring these entire SQL tables into memory and then join.  These tables weren't very big in QA, but on production they are big and growing.  The server began using multiple Gigabytes of memory to perform these joins and was crashing.

    Solution is to perform the SQL queries first, load the small result set into memory, and then join the resulting lists.  This way, it can retrieve and work with only the records it needs.  This DRASTICALLY increased performance and now it uses almost no memory:

    var cel= from p1 in scope.Extent<Ge.Wf.FieldServices.InspectionData.Data.CustomersExtension>()
                                            where p1.CustomerNumber == customerNumber && p1.AllianceId== allianceId
                                            select p1;

                          var isc= from p2 in scope.Extent<Ge.Wf.FieldServices.InspectionData.Data.InspectionScheduleCustomer>()
                                    where p2.InspectionId == guidInspectionId
                                 select p2;
                        //Joining data from Customer,CustomerExtension and InspectionScheduleCustomer table 
                        //based on AllianceId
                        var joinQuery = from a in custList
                                        join b in cel
                                        on a.CustomerNumber equals b.CustomerNumber
                                        join c in isc
                                        on a.CustomerNumber equals c.CustomerNumber
  2. Thomas
    Thomas avatar
    590 posts

    Posted 20 Jun 2011 Link to this post

    Hi Kyle Pietschmann,

    thanks for sharing your experience. Problem for us is, that application triggered in-memory LINQ implementations cannot be detected via our runtime. However, the upcoming Q2 2011 release will contain much improved diagnostic facilities, and analyzing the runtime behavior manually becomes much more easy.

    All the best,
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
Back to Top