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.
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
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.
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.
1 Like
WAIT… you MAY HAVE done something wrong… your TT table is table number 2… swap places so that TT is table number 1
Hmm weird I drew them in a different order. I’ll try again. Thanks tim!
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
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.
8 Likes
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)
Right it should have returned no results. But I will try it with part and see if it makes a difference
2 Likes
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
3 Likes
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?
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.
6 Likes
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#.
sure thing 
cost benefit at that point.
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.
5 Likes
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.
5 Likes
I wonder if Epicor will ever change their underlying generated code. I still see many folks join on ttTable using the WIdget.
Jose you are awesome. I have read this post before, but wow, still shocks me today- thank you!. 
2 Likes