[Python] Oracle DB to Python3 Performance improvement

We are trying to replace the Oracle Packages to Python3 Inline Queries, Implemented calling the Oracle Queries using CX_Oracle and Python Oracle DB pip packages, Tried all the attempts to improve the python execution performance. But we its not improving, Have tried multi processing its not working its taking 30mins to compelte the process. We have deployed in AKS and running the python.

# Get smoothing ratio components #984 Recods in Dataframe
smooth_ratio_comp_df = self.database.get_data_in_pandas(
self.table_cur,
self.smoothing_queries["get_smooth_ratio_comp"].format(
pin_calc_inst_id=self.pin_calc_inst_id,
pin_price_type_id=self.pin_price_type_id,
cs_flag_derv =CalcCommonConstants.cs_flag_derv ,
cs_flag_yes =CalcCommonConstants.cs_flag_yes
),
self.interface_model,
self.log_helper
)

for index, sm_comp in smooth_ratio_comp_df.iterrows():
self.process_smoothing_component(sm_comp)

def process_smoothing_component(self, sm_comp):
"""Process a single smoothing component"""

# Initialize SQL building variables
v_smth_sql = 'SELECT '

# Get UI calc component derive data
ui_comp_derive_df = self.database.get_data_in_pandas(
self.table_cur,
self.smoothing_queries["get_ui_calc_comp_derive"].format(
parent_comp_model_id=sm_comp['PARENT_COMP_MODEL_ID'],
version=sm_comp['VERSION']
),
self.interface_model,
self.log_helper
)

# Process each UI component derive
for ui_index, sm_ratio in ui_comp_derive_df.iterrows():
if sm_ratio['IS_COMP'] == CalcCommonConstants.cs_flag_yes:
# Initialize reallocation flag
lv_realloc_flg = CalcCommonConstants.cs_flag_no

# Check reallocation flag
# lv_realloc_flg = self.check_realloc_trans_comp(sm_ratio['RHS_ID'])
lv_realloc_flg = self.f_chk_realloc_trans_comp(sm_ratio['RHS_ID'],self.pin_calc_as_of_date)

# Get smoothing ratio flag
lv_sm_ratio_flg, lv_calc_comp_typ = self.get_smooth_ratio_flg(sm_ratio['RHS_ID'])

# Get blend product NDC11s
ls_blnd_prd_ndc11 = self.prc_get_blnd_prd(sm_comp['NDC9'], self.pin_calc_as_of_date, self.pin_current_period)


# Build SQL based on conditions
if lv_sm_ratio_flg == CalcCommonConstants.cs_flag_yes:
# Smoothing ratio query
v_smth_sql += self.build_smoothing_ratio_sql(sm_ratio['RHS_ID'], sm_comp['NDC9'])

elif sm_ratio['AMT_FLG'] == CalcCommonConstants.cs_flag_yes and lv_realloc_flg == CalcCommonConstants.cs_flag_no:
# Amount flag with no reallocation
v_smth_sql += self.build_amount_sql_no_realloc(sm_ratio['RHS_ID'], sm_comp['NDC9'], ls_blnd_prd_ndc11)

elif sm_ratio['AMT_FLG'] == CalcCommonConstants.cs_flag_yes and lv_realloc_flg == CalcCommonConstants.cs_flag_yes:
# Amount flag with reallocation
v_smth_sql += self.build_amount_sql_realloc(sm_ratio['RHS_ID'], sm_comp['NDC9'], ls_blnd_prd_ndc11)

elif sm_ratio['QTY_FLG'] == CalcCommonConstants.cs_flag_yes and lv_realloc_flg == CalcCommonConstants.cs_flag_no:
# Quantity flag with no reallocation
v_smth_sql += self.build_quantity_sql_no_realloc(sm_ratio['RHS_ID'], sm_comp['NDC9'], ls_blnd_prd_ndc11)

elif sm_ratio['QTY_FLG'] == CalcCommonConstants.cs_flag_yes and lv_realloc_flg == CalcCommonConstants.cs_flag_yes:
# Quantity flag with reallocation
v_smth_sql += self.build_quantity_sql_realloc(sm_ratio['RHS_ID'], sm_comp['NDC9'], ls_blnd_prd_ndc11)

else:
# Default case
v_smth_sql += ' (select 0 from dual) '
else:
# Not a component, use RHS name directly
v_smth_sql += sm_ratio['RHS_NAME']

# Execute the calculation if we have a valid query
ln_smth_ratio = 0
if v_smth_sql != 'SELECT ':
v_smth_sql += ' from dual'
ln_smth_ratio = self.execute_smoothing_calculation(v_smth_sql)

# Insert the smoothing result
self.insert_smoothing_result(sm_comp, ln_smth_ratio)

Continue reading...