Help with slow Linq query

Epicor ERP 10
1

We have the following Linq query in a method directive and user’s started reporting slow performance and we found this query takes 15 seconds. Any suggestions/pointers as to why it’s so slow?

var rows2 = (from jobHead in ttJobHead
            join jobHeadDb in Db.JobHead
            on new { jobHead.JobNum }
            equals new { jobHeadDb.JobNum }
            join jobProd in Db.JobProd
            on new {jobHead.JobNum }
            equals new {jobProd.JobNum}
            join partRow in Db.Part
            on new { jobProd.PartNum }
            equals new { partRow.PartNum }
            select new { ttJobFirm=jobHead.JobFirm, JobFirm=jobHeadDb.JobFirm, jobProd.PartNum, jobHead.RowMod, partRow.CheckBox04, partRow.CheckBox06, partRow.Date02, partRow.ShortChar07, partRow.ShortChar08 }).ToList();
2

NEVER join a tt table to a Db context. :slight_smile:

4 Likes
3

Seriously!!! Don’t do it!!! You are bringing ALL your jobhead records 100% of them into memory

1 Like
4

In other words if you ever want to use the Epicor Widgets and not Custom Code and you are a large company and you need to join on ttPart you are screwed… Can we just say Epicor doesn’t support large companies, only small mom an pop shops :slight_smile: maybe they will find a way to fix the widgets atleast :slight_smile: not so much custom code.

1 Like
5

You can make them work with the widgets.
Make a variable for every item in that you need to join to your tt.
Use the setter to assign your variable to the tt value.
In the query builder add the variables as a criteria to the table.

It’s a pain, but better than returning every part record.

1 Like
6

Thanks @hkeric.wci and @josegomez, we are really new to C# Epicor development and Linq. As I think most developers would agree - we don’t like magic(just do this and it is fast, don’t do this it’s slow) we really want to understand why so we can reason about solutions and troubleshoot problems.

I think what is being implied is this performance issue with joining temp tables to DB context is Epicor specific, that is educating ourselves more on C# and Linq won’t help us to understand why… Could anyone explain why this is so slow or point us to a book that we could read to better educate ourselves.

7

Hi @embedded
in the link I reference above there is more explanation but it is not necessarily an Epicor issue.

The issue is that a ttTable in a BPM is a POCO (Plain Old Class Object) while the Db.JobHead is a context table.
A context table is understood by SQL while a POCO is not, so when you say join
POCO to Context, C# / EntityFramework the best it can do is say, well SQL won’t understand my POCO it only understands the table, so let’s select everything in that table in SQL and then when we get the results we (in C#) can then do the join virtually in memmory.

This works well enough in general, until you do it with a large table (ala JobHead) so what happens then is that the JobHead table (all of it with every single record) is queried and brought into C# in memory and then the join between it and the tttable (POCO) is done thus causing performance issues

6 Likes