All Forums Database
Sam_989898 3 posts Joined 03/08
05 Mar 2008
Identify deleted records

I need help in optimizing the query below:Query is about identifying UK of deletes in a large table (70+ GB) using the pre-load-image and post-load-image. Here is the query we have written but this consume a ton of CPU.The two tables are joined on the UK in the query below:Need urgent help for performance improvement or any other alternatives, immediate responses would be greatly appreciated.LOCK TABLE pre_load_image FOR access LOCK TABLE post_load_image FOR access SELECT 'sap_clnt_id=' || COALESCE( A.sap_clnt_id , '' ) || '~co_cd=' || COALESCE( A.co_cd , '' ) || '~fin_doc_nbr=' || COALESCE( A.fin_doc_nbr , '' ) || '~fscl_yr_nbr=' || COALESCE( A.fscl_yr_nbr , '' ) || '~fin_doc_line_nbr=' || COALESCE( A.fin_doc_line_nbr , '' ) FROM pre_load_image A WHERE NOT EXISTS ( SELECT sap_clnt_id , co_cd , fin_doc_nbr , fscl_yr_nbr FROM post_load_image B WHERE A.sap_clnt_id = B.sap_clnt_id AND A.co_cd = B.co_cd AND a.fin_doc_nbr = b.fin_doc_nbr AND A.fscl_yr_nbr = b.fscl_yr_nbr AND A.fin_doc_line_nbr = b.fin_doc_line_nbr ) ; Thanks in advance!_Sree

foxbat 27 posts Joined 06/07
06 Mar 2008

The NOT EXISTS form of your Query is probably processed as a correlated subquery. Some of them are known to consume a lot of time and resource.Try rewriting your query using the NOT IN form. LOCKING ROW FOR ACCESSSELECT 'sap_clnt_id=' || COALESCE( A.sap_clnt_id , '' ) || '~co_cd=' || COALESCE( A.co_cd , '' ) || '~fin_doc_nbr=' || COALESCE( A.fin_doc_nbr , '' ) || '~fscl_yr_nbr=' || COALESCE( A.fscl_yr_nbr , '' ) || '~fin_doc_line_nbr=' || COALESCE( A.fin_doc_line_nbr , '' ) FROM pre_load_image A WHERE (sap_clnt_id, co_cd, fin_doc_nbr, fscl_yr_nbr,fin_doc_line_nbr) NOT IN (SELECT sap_clnt_id, co_cd, fin_doc_nbr, fscl_yr_nbr,fin_doc_line_nbr FROM post_load_image B); PS. Please test the queries on test tables with similar data. I think the results may vary between the NOT EXISTS and NOT IN forms if the columns used in the condition are NULLABLE.

You must sign in to leave a comment.