COmplex update takes a 90 minutes to run in Oracle
In the following procedure, the update is taking 90 minutes.
The client table has 22 million records, the CPDS table has 40 records.
It has been written by someone else, I don't have access to Oracle so can't check my thinking, but I believe, if we select the data to be updated into pl/sql tables, and then using rowid just update the rows with the data stored in pl/sql tables (both the select and update using bulk binds), then the query will be faster.
Cheers
Abhi
*************
PROCEDURE POPULATE_CLIENT_CPDS_DATA( P_START_CLNTNUM IN NUMBER, P_END_CLNTNUM IN NUMBER, P_UPDATED OUT NUMBER) IS L_STRSQL VARCHAR2(2000) ; BEGIN L_STRSQL := 'UPDATE CLIENT C SET ( O2BFID, MSISDNVALIDATIONDATE, SOURCE ) = ( SELECT O2BFID, MSISDNVALIDATIONDATE, SOURCE FROM C USTOMER_CPDS_DATA WHERE ID = C.ID ) WHERE CLNTNUM BETWEEN ' ;
L_STRSQL := L_STRSQL || P_START_CLNTNUM || ' AND ' || P_END_CLNTNUM || ' AND C.DOMAIN = ''' || 'IFL.co.uk' || ''' AND EXISTS ( SELEC T 1 FROM CLIENT_CPDS_DATA WHERE ID = C.ID)';
EXECUTE IMMEDIATE L_STRSQL ; P_UPDATED := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN P_UPDATED := -1; END POPULATE_CLIENT_CPDS_DATA;
Software/Hardware used:
Oracle
The client table has 22 million records, the CPDS table has 40 records.
It has been written by someone else, I don't have access to Oracle so can't check my thinking, but I believe, if we select the data to be updated into pl/sql tables, and then using rowid just update the rows with the data stored in pl/sql tables (both the select and update using bulk binds), then the query will be faster.
Cheers
Abhi
*************
PROCEDURE POPULATE_CLIENT_CPDS_DATA( P_START_CLNTNUM IN NUMBER, P_END_CLNTNUM IN NUMBER, P_UPDATED OUT NUMBER) IS L_STRSQL VARCHAR2(2000) ; BEGIN L_STRSQL := 'UPDATE CLIENT C SET ( O2BFID, MSISDNVALIDATIONDATE, SOURCE ) = ( SELECT O2BFID, MSISDNVALIDATIONDATE, SOURCE FROM C USTOMER_CPDS_DATA WHERE ID = C.ID ) WHERE CLNTNUM BETWEEN ' ;
L_STRSQL := L_STRSQL || P_START_CLNTNUM || ' AND ' || P_END_CLNTNUM || ' AND C.DOMAIN = ''' || 'IFL.co.uk' || ''' AND EXISTS ( SELEC T 1 FROM CLIENT_CPDS_DATA WHERE ID = C.ID)';
EXECUTE IMMEDIATE L_STRSQL ; P_UPDATED := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN P_UPDATED := -1; END POPULATE_CLIENT_CPDS_DATA;
Software/Hardware used:
Oracle
ASKED:
February 1, 2011 4:19 PM
UPDATED:
February 1, 2011 5:30 PM
Answer Wiki:
Bulk binding is useful to speed up multiple individual statements by reducing the context switches between the PL and the SQL engines, but in this case you have a single UPDATE statement, so context switches are not a factor and thus I don't think bulk binding will help. In fact, I guess it would be slower.
You will probably be able to improve the response time by adding (if they are missing) some indexes.
For example, indexes on the following columns would help:
CUSTOMER_CPDS_DATA.ID
CLIENT.CLNTNUM,CLIENT.DOMAIN ( or maybe domain,clntnum )
CLIENT_CPDS_DATA.ID
"<i>I don't have access to Oracle so can't check my thinking</i>"
So, is this just a theoretical discussion ? :-)
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
