Left joins are stupidly slow with LINQ -> OpenAccess?

[Kendall Bennett]

This query works perfectly in Entity Framework and generates SQL code that is about what you would expect, with left joins and more importantly, as one SQL query:

 

            return from p in uow.Products

                   join pd in uow.ProductDescriptions on p.ProductID equals pd.ProductID

                   join pl in uow.Locations on p.LocationID equals pl.LocationID into pls

                   from pl in pls.DefaultIfEmpty()

                   join qc in uow.ProductQCs on p.ProductID equals qc.ProductID into qcs

                   from qc in qcs.DefaultIfEmpty()

                   where pd.LanguageID == GT.LanguageID &&

                         (p.Barcode == barcode || p.Model == barcode) &&

                         (includeDiscontinued || p.Enabled == true || qc.New == true)

                   orderby p.Model ascending

                   select new DTO.ProductLocation {

                       ID = p.ProductID,

                       Model = p.Model,

                       Barcode = p.Barcode,

                       Name = pd.Name,

                       OpenStockQty = (p.Qty < 0 ? 0 : p.Qty),

                       LocationID = p.LocationID,

                       LocationCode = (pl == null) ? null : pl.Barcode,

                   };

 

When I run this query with OpenAccess, it works but it is REALLY slow. I finally figured out how to get the logging working so I could see the generated SQL in the new profiling and tracing tool (which was a pain because it writes the data to disk from a background thread every second at the fastest setting, so when you are debugging you have to sit and wait, and then step over some code, before it writes it to disk). When I looked at it under the SQL tool, I was shocked! Whereas Entity Framework generates SQL that represents a single query with the left joins in it, as soon as I throw a left join at OpenAccess it completely falls apart. Rather than doing a left join, it then breaks up the query and does an N+1 query. So it ends up doing the following:

 

1. Select ALL products joined with products descriptions and get the ID, language ID and a few other items from it
2. Select ALL product locations
3. Select ALL product QC’s
4. Then join them all together in memory to produce the final result

 

If I get rid of the left joins against the locations and QC tables, then it produces it all in one query, otherwise it reverts to the disgusting result above. There is no way I can use OpenAccess in our project if it cannot handle the simple case of a query such as the above with left joins without reverting to N+1 behaviour internally.

Please tell me there is some way to resolve this problem and there is a way to get this to work correctly?

[Damyan Bogoev]

Hello Kendall Bennett,

You could use the Fetch Optimization API in order to avoid the additional calls to the database. Helpful information about the fetch strategies in the product can be found here and here.
Hope that helps.

Best wishes,
Damyan Bogoev
the Telerik team

[Kendall Bennett]

Sorry but I should not have to use the fetch plan API just to get a left join to work correctly, This should be handled correctly by Open Access and I am really surprised that an ORM as mature as Open Access does not support left joins!

And anyway, I tried using the fetch plan API to tell it to include the locations and qc details with the products but it had no effect at all on this query. It still ended up doing an N+1 query for some reason, probably because I am not selecting a full entity from the database but rather transforming the data into a DTO object in the select clause. Until this is supported Open Access is an expensive door stop for me that I paid for. I am evaluating Lightspeed at the moment to see if it works better, but I would really like to use Open Access.

[Kendall Bennett]

Someone really needs to fix this ASAP, because lack of support for left joins means the product is mostly useless to me unless I move my queries with left joins into database views.

[Kendall Bennett]

Reading this post from back in March, it is clear the LINQ support in Entity Framework needs some more work, as it does not yet support left joins in the query syntax:

http://www.telerik.com/community/forums/orm/orm-express/linq-left-join.aspx

As it turns out, I was able to change the code to the following and eliminate the left joins, so that OpenAccess will create the left joins itself due to the use of the navigation properties in the query:

           return from p in uow.Products

                   join pd in uow.ProductDescriptions on p.ProductID equals pd.ProductID

                   where pd.LanguageID == GT.LanguageID &&

                         (p.Barcode == barcode || p.Model == barcode) &&

                         (includeDiscontinued || p.Enabled == true || p.QC.New == true)

                   orderby p.Model ascending

                   select new DTO.ProductLocation {

                       ID = p.ProductID,

                       Model = p.Model,

                       Barcode = p.Barcode,

                       Name = pd.Name,

                       OpenStockQty = (p.Qty < 0 ? 0 : p.Qty),

                       LocationID = p.LocationID,

                       LocationCode = p.Location.Barcode,

                   };

Now I have another issue is that my original query with Entity Framework was also including a sub-select to query tally up allocated back orders for the products selected using a sum query, as follows. However this blows up with an exception saying this is not supported on the database side yet. Does anyone have any other suggestions on how to get something like this working?

            return from p in uow.Products

                   join pd in uow.ProductDescriptions on p.ProductID equals pd.ProductID

                   where pd.LanguageID == GT.LanguageID &&

                         (p.Barcode == barcode || p.Model == barcode) &&

                         (includeDiscontinued || p.Enabled == true || p.QC.New == true)

                   orderby p.Model ascending

                   select new DTO.ProductLocation {

                       ID = p.ProductID,

                       Model = p.Model,

                       Barcode = p.Barcode,

                       Name = pd.Name,

                       OpenStockQty = (p.Qty < 0 ? 0 : p.Qty),

                       AllocatedBOQty = p.BackOrders.Where(bo => bo.DateShipped == null).Sum(bo => bo.QtyInStock),

                       LocationID = p.LocationID,

                       LocationCode = p.Location.Barcode,

                   };

[Thomas]

Hello Kendall Bennett,

at the moment the only workaround this issue is to perform a select into an anonymous type avoiding the subquery, and performing the subquery from a second select (into that DT type). This will make the overall query slow (1+N), but it will work. 

We are working with high priority on those issues. We will notify you about the progress.

Best wishes,
Thomas
the Telerik team

[Kendall Bennett]

I look forward to some updates to better improve handling of joins in OpenAccess.

[Thomas]

Hello Kendall Bennett,

we are still working on this, stay tuned. 

Best wishes,
Thomas
the Telerik team

[Tys]

We are experiencing exactly these same problem. A relatively simple query with a simple join ends makes numerous extra database calls to get the final results.
Can we get a status update on this?

I have also tried to play with FetchPlans, but this simply doesn't do anything.

[Kendall Bennett]

For what it is worth, I never found a solution to these problems and ended up migrating to a Micro ORM solution instead. I tried using table views to simplify things, but alas MySQL has terrible support for table views and wants to select everything the view defines into the view as a memory (or worse disk!) table, and then perform any filtering on the view against the temp table. So if you define a view that covers your entire database table but performs complex joins and sums for you, you always select the entire blob and it is really, REALLY slow on large tables.

One day I would love to go back to using an ORM solution, but not until these kinds of problems are addressed. Part of the problem as I understand it is that underneath Open Access is built on the older non-LINQ based OQL engine, so it has to translate the LINQ queries into OQL, which is then executed (by turning the OQL into SQL). And some stuff just does not translate well to from LINQ->OQL->SQL. A better solution would be LINQ->SQL directly, but OpenAccess does not support that yet (at least not last time I looked over 12 months ago).

[Kendall Bennett]

Sometimes I get the impression that people using ORM solutions for real applications are probably doing everything with stored procedures and just using the ORM as an expensive mapping engine to map from stored procedures to entities, and then perform updates on those entities.

[Thomas]

Hi Tys,

please send your simple query to support (with .rlinq model) so that we can figure out what is really not so good. One issue _I_ have with LINQ is that it is easy to formulate queries that are much harder to the database than thought.

All the best,
Thomas
the Telerik team

[Pete Baxter]

I have the following LINQ that takes about 25 seconds in OpenAccess and 0.3 seconds in SQL Ent Manager. It was the first smple join I tried, maning it isn't hard to find really terrible performance in the OpenAccess implementation of LINQ to SQL.

var s = from sects in tmdb.TSections
join tmpl in tmdb.Templates on sects.TemplateID equals tmpl.ID
where (tmpl.PracticeID == 1)
select sects;
tmsections = s.ToList();

One table has 9 fields (1 ntext field) with about 3400 records, and the second has 6 fields (one ntext field) with about 98000 records.
There is no foreign key or association between the tables, though the key field in the second table (sects) is indexed.

Two expectations:
A single table join with no Where clause should have near speed equivalence with the SQL Ent Manager, ADO.NET. At present, OpenAccess appears to be about 10 times slower.
A single table join WITH a Where clause is very, very common (ie how many times do you want all customer orders when you join the customer table to the orders table?) and is currently a little over 25 times slower. 

I can help you with test cases, but you can literally trip over them. We are porting an application with about 60 tables and almost all our queries run slow, as in "we probably will have to stop using OpenAccess slow". So, you take the first step and be honest about what is going on, and I will help you. But, frankly, I don't think you need any customer samples -- you just need to spend an hour running your own sofwtare and creating some unit tests. 

Peter  

[Thomas]

Hello Peter,

rest assured that we have such cases in our test suite. The only reason why EF could be that dramatically faster is that the join is not executed on the server. However, all our tests do not show such a behavior.
Therefore please check that you are using the newest version of OpenAccess. If you do and the issue persists, please do not hesitate to contact support and send us your .rlinq too.

All the best,
Thomas
the Telerik team