Re: Slow query - correlated or not?

Nils.Myklebust@idg.no (Nils Myklebust) wrote: >William Hayman <williamh@sequent.com> wrote: >:Hi >: >:We have an update that is taking a very long time to run. >: >:I have turned into a select statement for testing. >: >:I have included the set explain output. >: >:My questions are: Is this a correlated subquery? >Yes >: ie is >:the full table scan of ps_jrnl_ln occurring for each >:row being updated? >No. The subquery is however executed each time and will slow you down >considderably. >: Does this explain why ps_jrnl_ln >:does not appear in the subquery section of sqexplain.out? >But it does??? >:Any ideas on improving performance would be much appreciated. >The obvious without rewriting the statement would be an index on >'psoft'.PS_JRNL_LN.BUSINESS_UNIT. It would of course only help if it >gives a reasonable selectivity. I'm willing to guess based upon the info provided that you are running PeopleSoft Financials. PeopleSoft is notorious for using correlated subqueries as we have found ourselves. I agree with Nils about the index. We have found that MOST of our performance issues could be solved by looking at our indexing strategy. Good luck with your problems. >An exists corelated subquery is allways slow. (Not exists is even >slower if you should ever need that.) If you can rewrite it using in >instead and have no reference to the PS_JRNL_LN table in the subquery >it would be substantially faster. To achieve this it might be possible >to do a select of the primary key of PS_JRNL_LN with a join to >PS_COMBO_DATA_TBL something like this (I have left out the user names >which are only necessary if you are unfortunate enough to have to use >a mode ansi database): >select PS_JRNL_LN.primarykey > from PS_JRNL_LN, PS_COMBO_DATA_TBL > where PS_JRNL_LN.BUSINESS_UNIT='UOA' > and PS_JRNL_LN.ACCOUNT = PS_COMBO_DATA_TBL.ACCOUNT > and PS_JRNL_LN.DEPTID = PS_COMBO_DATA_TBL.DEPTID > and PS_JRNL_LN.JOURNAL_DATE > BETWEEN PS_COMBO_DATA_TBL.EFFDT_FROM > and PS_COMBO_DATA_TBL.EFFDT_TO > and PS_COMBO_DATA_TBL.SETID='UOFAK' > and PS_COMBO_DATA_TBL.PROCESS_GROUP='INCSTMT' > and PS_COMBO_DATA_TBL.VALID_CODE='V' >into temp tt_1 >Then you can update thus: >update PS_JRNL_LN set ... >where PS_JRNL_LN.primarykey in ((select primarykey from tt_1)) >which should be fast. >It's of course important to check the above statements for errors. I >have just copied and typed here and there may be any number of flaws. >This only works if the primary key of the PS_JRNL_LN table is a single >column. If it is not you may be able to add a serial column to it to >enable such updates as this. >Also don't fall in the trap of using unique in the select statement. >It's not needed and will only slow down the select. >: >:Thanks >:William >: >: >: >:QUERY: >:------ >:select count(*) from >:'psoft'.PS_JRNL_LN WHERE BUSINESS_UNIT='UOA' >: AND EXISTS (SELECT >: 'X' FROM 'psoft'.PS_COMBO_DATA_TBL WHERE SETID='UOFAK' AND >:PROCESS_GROUP='INCSTMT' >: AND VALID_CODE='V' AND 'psoft'.PS_JRNL_LN.JOURNAL_DATE BETWEEN >:EFFDT_FROM AND >: EFFDT_TO AND 'psoft'.PS_JRNL_LN.ACCOUNT = ACCOUNT AND >:'psoft'.PS_JRNL_LN.DEPTID = DEPTID) >: >:Estimated Cost: 1614158848 >:Estimated # of Rows Returned: 1 >: >:1) psoft.ps_jrnl_ln: SEQUENTIAL SCAN >: >: Filters: (psoft.ps_jrnl_ln.business_unit = 'UOA' AND EXISTS >:<subquery> ) >: >: Subquery: >: --------- >: Estimated Cost: 1200 >: Estimated # of Rows Returned: 1 >: >: 1) psoft.ps_combo_data_tbl: INDEX PATH >: >: Filters: (psoft.ps_combo_data_tbl.valid_code = 'V' AND >:(psoft.ps_combo_data_tbl.effdt_from <= psoft.ps_jrnl_ln.journal_date AND >:(psoft.ps_combo_data_tbl.effdt_to >= psoft.ps_jrnl_ln.journal_date AND >:(psoft.ps_combo_data_tbl.account = psoft.ps_jrnl_ln.account AND >:psoft.ps_combo_data_tbl.deptid = psoft.ps_jrnl_ln.deptid ) ) ) ) >: >: (1) Index Keys: setid process_group combination valid_code >:(Serial, fragments: ALL) >: Lower Index Filter: (psoft.ps_combo_data_tbl.setid = 'UOFAK' >:AND psoft.ps_combo_data_tbl.process_group = 'INCSTMT' ) >Nils.Myklebust@idg.no >NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway >My opinions are those of my company >The Informix FAQ is at http://www.iiug.org Barry Leb National Linen Service 1420 Peachtree Street MS #314 Atlanta, GA 30309 (404) 853-6119 (404) 853-6485 fax e-mail: barryleb@mindspring.com