Joining "TT" records to regular DB records in BPMs

Experts' Corner
10

Ran it in 10.2.100.5 with the Same Results
First Test:

image
image.png722×661 34.2 KB

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

image
image.png1128×741 62.5 KB

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

image
image.png913×661 48.1 KB

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)

image
image.png811×700 45.9 KB 

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
image

Now the test with PartTran using the ttTable

image
image.png691×661 38 KB

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

image
image.png713×64 4.76 KB

That’s 149.5 times slower :frowning: 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