Hello
I have created a stored procedure
DELIMITER $$ CREATE OR REPLACE DEFINER=`dbadmin`@`%` PROCEDURE `FAR_STAGE_BP_IMPACT`() BEGIN UPDATE FAR_BREAKS_STAGE cod INNER JOIN FAR_IBOR_BP_IMPACT_DATA tna ON (cod.FUND_SYMBOL = tna.ACCT_CD_FMRID) SET cod.IBOR_TOTAL_NET_ASSETS_USD = tna.MKT_VAL_USD, cod.BREAK_BP_IMPACT = CASE WHEN IFNULL(cod.IBOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN 0 WHEN cod.DEBIT_CREDIT = 'Debit' THEN CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4)) ELSE CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4)) END WHERE tna.ACCT_CD_FMRID IS NOT NULL and cod.REC_TYPE = 'IBOR'; UPDATE FAR_BREAKS_STAGE cod INNER JOIN FAR_ABOR_BP_IMPACT_DATA tna ON (cod.FUND_NUMBER = tna.ACCOUNT_NUMBER_IO) SET cod.ABOR_TOTAL_NET_ASSETS_USD = tna.TOTAL_NET_ASSETS_USD, cod.BREAK_BP_IMPACT = CASE WHEN IFNULL(cod.ABOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN 0 WHEN cod.DEBIT_CREDIT = 'Debit' THEN CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4)) ELSE CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4)) END WHERE tna.ACCOUNT_NUMBER_IO IS NOT NULL and cod.REC_TYPE = 'ABOR'; UPDATE FAR_BREAKS_STAGE bs SET bs.FUND_BP_IMPACT = CASE WHEN bs.REC_TYPE= 'IBOR' THEN fn_get_sum(bs.FUND_SYMBOL, 'IBOR') ELSE fn_get_sum(bs.FUND_SYMBOL, 'ABOR') END; END$$ DELIMITER ;
This stored procedure is working in unexpected manner. Sometimes only updating ABOR data and sometimes only IBOR and not updating column FUND_BP_IMPACT column at all. timeout is set as 600ms. Is there any way we can increase the timeout and can anyone suggest that do I need to split this one procedure into three to work correctly?
Discussion posts and replies are publicly visible
It is impossible to say anything without way more information.
According to https://docs.appian.com/suite/help/24.2/fnc_system_a_executestoredprocedureforquery.html you can extend the timeout but I would advise against it. It would be better to look at the performance of the stored procedure and tune that accordingly.
One thing which jumps out to me on your stored procedure is that on the last query you are updating the whole of the table FAR_BREAKS_STAGE as you have no were clause. Which without looking knowing what you are doing seems completely unnecessary.
I would also check your joins to see if you have relevant indexes if necessary. You could try running EXPLAIN or ANALYZE FORMAT=JSON OR ANALYZE before your query to see how the query is performing and this often gives you a good indication of what changes you need to make.
However as Stefan rightly said it is very hard to properly say without more information
Swati Sharda
I suspect there could be multiple reasons for that
1. Row Locking - If multiple updates are trying to modify the same rows, there could be a row lock that prevents one of the updates from proceeding.
2. Each update statement has a Where clause, if one of the condition in the where clause does not match any rows, the update will be skipped for those rows. It could cascade down to further updates too
3. If there are triggers on the table that are executing as a result of the UPDATE statements, they might cause unexpected behavior such as skipping rows or modifying the table in a way that affects subsequent updates.
UPDATE
I would advise you to try having three similar test tables and test data for BREAKS, ABOR and IBOR. Modify the stored procedure to update these test tables. And then you can try testing and debugging to point out exactly what is happenin with the procedure.
when i am running these three update statements in stored procedure, it is updating only IBOR_TOTAL_NET_ASSETS_USD and ABOR_TOTAL_NET_ASSETS_USD but not updating Break_BP_Impact and FUND_BP_IMPACT column, they are all 0.0000 or null. But when I run these three update statements separately one by one in sequence in SQL manually, it is working correctly and updating all columns. Why it is not working in the stored procedure????
Hi Soma ,
there are no triggers and where clause is working well when i am running them manually each update statement. these are not working correctly in stored procedure.