Joining "TT" records to regular DB records in BPMs

timshuwy · June 25, 2018, 9:01pm

I am opening up this topic, because it was raised at #Insights2018. Please join in on the discussion if you have examples that prove this wrong!!!
I just want to curb any urban legend that may be building out in the community based on misunderstood ideas.

There was a recommendation made during one of the sessions that JOINING a TT record to a regular database record was bad practice. Instead of joining, you should simply add conditions in your query for any of those links between TT and non-TT records. This recommendation was based on a study done by @josecgomez (I believe) which showed the difference between a poorly written and a well written Linq statement using tt records. Jose IS CORRECT, in that you can create really bad linqs if joined incorrectly

It raised a huge concern in my own mind because I regularly do this… for example, I would join the TTOrderDtl to Part to find if the part met certain criteria. This would mean that I would need to review and change links in 100s of BPMs.

After returning home, I started research, including contacting Development and other consultants, and received word that this is NOT a problem UNLESS you connect backwards…
GOOD joins:

TTOrderDtl -->Join -->Part
TTOrderDtl -->Join -->Part --> Join -->PartPlant

image.png642×545 32.6 KB

BAD joins:
Part -->Join --> TTOrderRel --> Join -->PartPlant
PartTran --> ttPart

image.png846×583 38.6 KB

The word I received (had have personally verified) is that as long as the TT table(s) is/are the FIRST table(s), then when the linq statement is created, then your query will be efficient…
Thoughts?

hkeric.wci · June 25, 2018, 9:18pm

I get the slow JOIN in Code if I do ttFirst - E10 BPM Slow due to Join? - #13 by hkeric.wci - ERP 10 - Epicor User Help Forum

timshuwy · June 25, 2018, 9:25pm

Guess I missed that discussion… but this is slightly different… talking about using the base widget to do a query. So far, in my tests, I don’t get a performance hit unless I make it backwards.

josecgomez · June 25, 2018, 9:26pm

Tim looking at the code behind this doesn’t appear to be the case… I’m curious as to why they said this… the code behind at least as of 10.2 does a whole join. however it does only bring back they keys you join.,… but if its something like PartTran that is still thousands upon thousands of records.
I’m curious now… maybe they changed that… let me check it out. Thanks for the heads up

josecgomez · June 25, 2018, 9:46pm

ok @timshuwy I did some testing with SQL Profiler and such, it does still bring back a an entry per each record of data but it only brings back 3 columns (that is the columns in your join)

This BPM

Runs the following Code in SQL

SELECT 
1 AS [C1], 
[Extent1].[Company] AS [Company], 
[Extent1].[ABCCode] AS [ABCCode]
FROM [Erp].[ABCCode] AS [Extent1]

The above Select would return 1,1,1,1,1,1,1,1 for each value in ABCCode… that’s not a big deal for ABCCode but for PartTran you’d get back 1,1,1,1, (times) the number of records in the parttran table…

IMO that is still not great. Unless I did something wrong? I’ll try with a few more links and see if I see a difference.

timshuwy · June 25, 2018, 10:36pm

Thanks… This is what I am looking for… Something I can feed back to Development, because I was told that we wrote it specifically so that it COULD work with a join. (In some places, the join is automatically created).
I don’t really care if i am “right or wrong” on this subject. It is more about getting it right in the future.

timshuwy · June 25, 2018, 10:37pm

WAIT… you MAY HAVE done something wrong… your TT table is table number 2… swap places so that TT is table number 1

josecgomez · June 25, 2018, 10:56pm

Hmm weird I drew them in a different order. I’ll try again. Thanks tim!

josecgomez · June 26, 2018, 1:22am

I re-wrote the BPM making sure to make ttTable the first and to drag the arrow from tt to the actual table

The LINQ (Code behind generated looks like this)

 private bool C001_QuerySizeCondition()
        {
            var query =
                from dbQuery in (
                    from rowABCCode in this.Db.ABCCode
                    where ((rowABCCode.Company == null
                        || rowABCCode.Company == ""
                        || rowABCCode.Company == (this.Session.CompanyID)))
                    select new
                        {
                            rowABCCode_Company = rowABCCode.Company,
                            rowABCCode_ABCCode1 = rowABCCode.ABCCode1,
                        }).AsEnumerable()
                join rowttABCCode in ds.ABCCode on new { Key0 = dbQuery.rowABCCode_Company.ToLowerInvariant(), Key1 = dbQuery.rowABCCode_ABCCode1.ToLowerInvariant() } equals new { Key0 = rowttABCCode.Company.ToLowerInvariant(), Key1 = rowttABCCode.ABCCode.ToLowerInvariant() }
                select 1;
            return query.Take(2).Count() >= (1);
        }

Which using SQL Profiler generated the following SQL on the server side

SELECT 
1 AS [C1], 
[Extent1].[Company] AS [Company], 
[Extent1].[ABCCode] AS [ABCCode]
FROM [Erp].[ABCCode] AS [Extent1]
WHERE (((N'' = [Extent1].[Company]) OR (([Extent1].[Company] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)))

Note that the above will still return 1 record per item in the ABCCode table (1,1,1,) if this was PartTran with a million lines you’d get a million rows returned.

This was a test in 10.1.600.16 I will re-run this test in 10.2 shortly just in case that they addressed these in 10.2

josecgomez · June 26, 2018, 1:52am

Ran it in 10.2.100.5 with the Same Results
First Test:

Ran SQL Profiler Yielded the following SQL Statement

SELECT 
    1 AS [C1], 
    [Extent1].[Company] AS [Company], 
    [Extent1].[ABCCode] AS [ABCCode]
    FROM [Erp].[ABCCode] AS [Extent1]
    WHERE ([Extent1].[Company] IN (N'',@p__linq__0))

Note that the above SQL yields 1 record per each entry in ABCCode table for the given Company

As a test I decided to create a variable which holds the current ttABCValue

Then use that variable as criterial in the BAQ condition (without the ttTable)

This yielded in Profiler the following SQL

SELECT 
    1 AS [C1]
    FROM [Erp].[ABCCode] AS [Extent1]
    WHERE ([Extent1].[ABCCode] = @p__linq__0) AND ([Extent1].[Company] IN (N'''',@p__linq__1))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'A',@p__linq__1=N'EPIC06'

Its a bit hard to read but if you notice it declares 2 variables in SQL the @p__linq__0 and the @p__linq__1
@p__linq__0 is given the value of ‘A’ (my current ABCCode I was testing with) and
@p__linq__1 is given the value of ‘EPIC06’ my current company.

So it is running the Select statement where ABCCode=‘A’ and Company=‘EPIC06’ so you’ll get exactly 1 results which matches the given query.

Since you have the attention of development I’d ask that they try these scenarios with something like Part Tran and see the difference inf performance. I am testing with ABCCode so its hard to tell.

For giggles I tested one last time with PartTran (instead of ABCCode)
This is the Test with Part Tran using the variable (not the ttTable)

SELECT 
    1 AS [C1]
    FROM [Erp].[PartTran] AS [Extent1]
    WHERE ([Extent1].[PartNum] = @p__linq__0) AND ([Extent1].[Company] IN (N'''',@p__linq__1))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'A',@p__linq__1=N'EPIC06'

Once again it passes in the whereClause on the PartNum and Company and returns just 1 record. Execution time was 25 miliseconds

Now the test with PartTran using the ttTable

Onece again it yeilds the following SQL statement

SELECT 
    1 AS [C1], 
    [Extent1].[Company] AS [Company], 
    [Extent1].[PartNum] AS [PartNum]
    FROM [Erp].[PartTran] AS [Extent1]
    WHERE [Extent1].[Company] IN (N'''',@p__linq__0)',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'EPIC06'

This table has 1.9 million records…
Excution time for this one was…3738 milliseconds

That’s 149.5 times slower and it would increase exponentially based on the size of the table.

Hopefully Development can replicate, let me know if anyone sees anything differently. I think I followed all the suggestions from @timshuwy. I would love nothing more than an easier work around for this, or if we can get development to help. If the ttTable is involved the code behind should do a foreach and then do a the join to the rest of the tables…

foreach(var x in tt<table>)
{
 // Do Additional joins here with a whereClause for the ttTable
}

I was hoping there was a work around, Hopefully we can come up with something and again if I missed something let me know.

timshuwy · June 26, 2018, 2:41pm

Thank you for this analysis… I have no ability (lack of knowledge of Sql) to do this type of test myself… but you doing this will help us in the end…
One thing I wonder about (and would love to see) is if you did the same BPM based on Part table, and a query that joins ttPart ->> PartTran using JOIN (Company=company, job=job) vs using a WHERE clause… and see if there is a difference. In your test, since you didn’t have a true join between ABC and PartTran, there was nothing to filter on (unless I missed something)

josecgomez · June 26, 2018, 2:42pm

Right it should have returned no results. But I will try it with part and see if it makes a difference

josecgomez · June 26, 2018, 3:06pm

Hi @timshuwy I ran it using Part.Update

With the where Clause

Excecution Time: 23 MS
SQL:

SELECT 
    1 AS [C1]
    FROM [Erp].[PartTran] AS [Extent1]
    WHERE ([Extent1].[Company] = @p__linq__0) AND ([Extent1].[PartNum] = @p__linq__1) AND ([Extent1].[Company] IN (N'''',@p__linq__2))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)',@p__linq__0=N'EPIC06',@p__linq__1=N'001MP',@p__linq__2=N'EPIC06'

Returns Exactly the number of rows in PartTran for the given PartNumber ‘001MP’ in the Company ‘EPIC06’

With the ttJoin

Execution Time: 2140 MS

SQL:

SELECT 
    1 AS [C1], 
    [Extent1].[Company] AS [Company], 
    [Extent1].[PartNum] AS [PartNum]
    FROM [Erp].[PartTran] AS [Extent1]
    WHERE [Extent1].[Company] IN (N'''',@p__linq__0)',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'EPIC06'

Returning All rows from Part Tran 1.9 million records
93 times slower

I did them both back to back at the same time, you can see the difference in the profiler

timshuwy · June 26, 2018, 3:16pm

Wow… super thanks to you! I will push this info back… not sure where it will go from here.
One thing I did, (and if you could try) was instead of setting a variable, i simply put both the ttPart and PartTran into the query but WITHOUT a join, and then added the criteria (like below). Wondering if this makes a difference?

josecgomez · June 26, 2018, 3:25pm

Just tried it, that did not make a difference it removed all the criteria from the PartTran query
SQL:

SELECT 
    1 AS [C1], 
    [Extent1].[PartNum] AS [PartNum], 
    [Extent1].[Company] AS [Company]
    FROM [Erp].[PartTran] AS [Extent1]

Out of curiosity I looked at the generated code behind for this one…

private bool C001_QuerySizeCondition()
        {
            var query =
                from dbQuery in (
                    from rowPartTran in this.Db.PartTran
                    select new
                        {
                            rowPartTran_Company = rowPartTran.Company,
                            rowPartTran_PartNum = rowPartTran.PartNum,
                        }).AsEnumerable()
                join rowttPart in ds.Part on 1 equals 1
                where (string.Equals(dbQuery.rowPartTran_Company, rowttPart.Company, StringComparison.OrdinalIgnoreCase)
                    && string.Equals(dbQuery.rowPartTran_PartNum, rowttPart.PartNum, StringComparison.OrdinalIgnoreCase)
                    && (dbQuery.rowPartTran_Company == null
                    || dbQuery.rowPartTran_Company == ""
                    || string.Equals(dbQuery.rowPartTran_Company, this.Session.CompanyID, StringComparison.OrdinalIgnoreCase)))
                select 1;
            return query.Take(2).Count() >= (1);
        }

Looks like it automatically creates a join where 1=1 effectively doing a CROSS JOIN and running a SELECT * on both tables.
It looks like the only solution for the time being is to use the variable

Granted and one thing I want to make clear this will only be a significant problem if the tables are large (PartTran, Labor, TranGLC etc) even 1.9 million records returned in 3 seconds. However 3 seconds is a long time to wait if you have several BPMs.
Also the more tables we join the more we increase the Delay. I imagine a BPM that joins ttPart to PartTran and then to Labor… etc…
So although this isn’t something I’d consider a HUGE issue, I think we need to be aware of it and try to avoid it if we can.

timshuwy · June 26, 2018, 3:52pm

On “challenge” with the method where you need to define a variable first, and then do the query… SOMETIMES there are BPMs where there are multiple tt records. having the TT record inside the query is easier than building the entire query in c#.

josecgomez · June 26, 2018, 3:53pm

sure thing cost benefit at that point.

timshuwy · June 26, 2018, 8:17pm

Just letting you know that i have been able to create a BPM that does a query two different ways… within the BPM, I grab start/end times, and calculate the elapsed time for the query to run. I found similar results. I have now passed the BPM samples onto other in Tech Support and Development…
Whether this gets “Fixed” or just causes us to write better BPMs, we are still better off for this research.

josecgomez · June 26, 2018, 8:21pm

Thanks @timshuwy it would require a fundamental re-design of the BAQ Widget, where by if the ttTable is involved they need to wrap that in a loop first and then join the rest of the tables. It wouldn’t be terribly hard to write just would require some cleverness.
I think in the very least they should put a warning if you join to a tt in (ANY) instance.

Randy · June 26, 2018, 9:04pm

I need to rewrite some BPMs… Thanks @timshuwy and @josecgomez