Topic
  • 2 replies
  • Latest Post - ‏2013-11-13T01:24:31Z by boyerpl
TonyLlopis
TonyLlopis
59 Posts

Pinned topic Query performance

‏2013-11-11T16:13:46Z |

I have noticed that under some conditions the following query performs very poorly:

               <Prop names='cicsFileWoDataSet' dataTypes='integer'>
                    <Sql>
                        select count(*)
                        from $(SCHEMA).dmh_online_dstore ds, $(SCHEMA).dmh_online_region rg
                        where ds.region_id = rg.region_id
                        and rg.region_type_cd = 'C'
                        and ds.dataset_id not in
                            (select dsn.dataset_id from $(SCHEMA).dmh_dataset dsn)
                    </Sql>
                </Prop>

On the other hand, converting the NOT-IN clause into a LEFT-OUTER-JOIN ..  WHEN ... IS NULL gives good response time.   

All of the above after reorg/runstats.

 

  • jcdelmo
    jcdelmo
    352 Posts

    Re: Query performance

    ‏2013-11-12T16:23:08Z  

    RAA work item 42511 has been opened for this issue.

  • boyerpl
    boyerpl
    221 Posts

    Re: Query performance

    ‏2013-11-13T01:24:31Z  
    • jcdelmo
    • ‏2013-11-12T16:23:08Z

    RAA work item 42511 has been opened for this issue.

    I'll rewrite the IMS one was well.   Both will be changed in the next fixpack.