Joining "TT" records to regular DB records in BPMs

Experts' Corner
15

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. :frowning:
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