Hello,

I believe the data type difference is the main root cause. Think of it this way: when you compare two different data types, like a Long Integer and Text, the database performs an internal cast to make the comparison. This casting happens for every row being searched, which significantly slows down the query. My advice is to convert the TextToLongInteger (or align the data types) before performing the join and compare the differences.

Also see this: Data Conversion 

Hello @Ajit Kurane,

By changing the data type of the "NewAccountNumber" and "OldAccountNumber" attributes from Text to Long Integer, the performance issue should be fixed.

Thanks,

Ananth

Yes, this can definitely be a root cause of the performance issue.

You have two major “red flags” in that join:

1. Data type mismatch (Long Integer vs Text) --- When you join PersonalDetail.CreditAccountNumber (Long Integer) with MasterData.NewAccountNumber / OldAccountNumber (Text), the database needs to perform an implicit conversion on one side of the join. This usually prevents the use of indexes efficiently and can cause full scans on large tables.
   
   

2. OR in the join condition --- The condition

   PersonalDetail.CreditAccountNumber = MasterData.NewAccountNumberORPersonalDetail.CreditAccountNumber = MasterData.OldAccountNumber

   makes it very hard for the optimizer to use indexes well and typically degrades performance on big volumes.

A few approaches to improve this:

1. Normalize data types Store all account numbers with the same type on both entities (either all numeric or all text). This allows proper indexing and avoids implicit conversions.

2. Avoid OR in the join Instead of one aggregate with an OR join, you can:

   • Run two queries and then Union / merge the results:

     • Query 1: join on CreditAccountNumber = NewAccountNumber

     • Query 2: join on CreditAccountNumber = OldAccountNumber This often performs better than a single join with OR.

   • Or, if possible, create an index on the column(s) that hold the account number in MasterData and use a single, simple join.

3. Move heavy logic to SQL / View For very large datasets, consider:

   • Creating a database view that already encapsulates the best-performing join logic (with normalized types), and consume that view in OutSystems.

   • Or use an Advanced SQL with explicit casts and hints, tuned by your DBA.

4. Pre-aggregate in a staging table (for monthly reports) Since this is a monthly report run by a timer, another option is:

   • Pre-calculate and store the data in a staging/report table during off-peak hours.

   • Then your report query reads from this optimized table instead of hitting the raw transactional tables every time.

In summary: yes, the combination of data type mismatch + OR in the join is very likely hurting performance. Aligning the data types and simplifying the join condition (possibly splitting into two queries or using a view/staging table) should give you a significant improvement.

Since action is already triggered via timer, it meant it's really a long job (more than 20 minutes)
Advanced SQL widget might help, instead of fetching aggregate. Do take note of the output entities/structures to match the order of the SQL Select statement. 

If SQL is 'SELECT {PersonalDetail}.*, {MasterData}.*..... the output list must be PersonalDetail, MasterData is this order

SELECT {PersonalDetail}.*, {MasterData}.* FROM {PersonalDetail}

INNER JOIN {MasterData} 

    ON {PersonalDetail}.[CreditAccountNumber] = CAST({MasterData}.[NewAccountNumber] AS BIGINT)

UNION ALL

SELECT {PersonalDetail}.*, {MasterData}.* FROM {PersonalDetail}

INNER JOIN {MasterData} 

    ON {PersonalDetail}.[CreditAccountNumber] = CAST({MasterData}.[OldAccountNumber] AS BIGINT)