tMSSqlSCD Optimization

[castiellll]

Hello,

I am using an tMSSqlSCD component that works fine but slow. I've checked the query used in MSSql and it looks like

UPDATE [fact].[operation] SET [integration_date] =  @P0 
 [amount] =  @P1 
 [amount_com] =  @P2 
 [amount_ttc] =  @P3 
 [closed_comments] =  @P4 
 [com_status] =  @P5 
 [com_type] =  @P6 
 [dt_closed] =  @P7 
 [dt_refund] =  @P8 
 [end_to_end_ref] =  @P9 
 [id_client_transfert] =  @P10 
 [id_money_exchange] =  @P11 
 [id_receiver] =  @P12 
 [id_sender] =  @P13 
 [is_real_money] =  @P14 
 [is_refunded] =  @P15 
 [label] =  @P16 
 [ref_commission] =  @P17 
 [tax_com] =  @P18 
 [type_operation] =  @P19 
 [unpaid_comments] =  @P20 
 [unpaid_money_exchange_id] =  @P21 
 [unpaid_money_exchange_origin_id] =  @P22 
 [unpaid_ref] =  @P23 
 [unpaid_type] =  @P24  
 WHERE  
 ([id_commission] =  @P25   OR (( @P26  IS NULL) AND ([id_commission] IS NULL )))  
 AND
 ([id_domain] =  @P27  		OR (( @P28  IS NULL) AND ([id_domain] IS NULL )))  
 AND
 ([id_operation] =  @P29  	OR (( @P30  IS NULL) AND ([id_operation] IS NULL )))  
 AND
 ([id_unpaid] =  @P31 	    OR (( @P32  IS NULL) AND  ([id_unpaid] IS NULL ))) 

The problem is that in the where clause, it uses "OR" which is not SARGable and may slow significantly the query regarding the quantity of data I have.

So, is there any way to force the query to be SARGable or to re-write the query that this component generate ??

Thanks in advance.

[Anonymous]

Hi,

    My recommendation will be to check with the help of DBAs to build better performance optimization plans for the generated query. DBAs will have tuning recommendation tools for Databases which will provide better execution plan.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[castiellll]

Hi,

Thank you for your reply but it doesn't show me how do I force Talend to rewrite. We know exactly how to optimize our query but we don't know how to make Talend do it (beside bulkExec or rewrite manually a routine).

[Anonymous · Accepted solution]

Hi,

    You will not be able to force Talend to use a specific way of writing code. If you want to follow any specific method of code execution, routine methodology is the most easy one as you will get more control at code level.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂