Re: Slow query - correlated or not?

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. 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