LINQ ORM Memory Problems (Joins)

[Kyle Pietschmann]

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

[Thomas]

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,
Thomas
the Telerik team