Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Unanswered: Better way to write query for improving performance?

    Hi All,

    I have written a query which is working fine but taking long time to execute, it is actually matching a lot of data. If there is some other way of writing my query, then please suggest.

    Scenario :

    I have 3 tables - Country, Stores and Mapping. Country table is having column state and indicator. User can select any state and either include it or exclude it, which will update the indicator to Include or Exclude. The stores table is having column store number and column indicator. Mapping table is having mapping between the state and the stores.

    When user includes or excludes any of the state, it updates the Country table indicator corresponding to the state, which is not a problem.
    Now, I need to update the Store table where first I have to check the stores one by one that they belong to which State from Mapping table and if the found state is the same selected by the user, then update store indicator in the Store table.
    Because number of records are huge, so it is affecting the performance.

    For updating the Store table, I am writing the following query:
    UPDATE STORE SET INDICATOR = :INDICATOR WHERE STATE IN (SELECT STORE_NUMBER FROM MAPPING WHERE STATE IN (: STATE)).

    The ": STATE" value I am passing through my java program and it will go like ('IL', 'AL').

    I need help to make this query better if possible.

    Thanks a lot for any help and suggestions.
    Last edited by ashu000; 06-04-11 at 07:20.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Remove(or ignore) Mapping table.
    Then add state column to Store table.
    If you want to see the indicator, join Country table and see indicator in Country table.

  3. #3
    Join Date
    Apr 2011
    Posts
    28
    Hi Tonkuma,

    Thanks for your reply.

    Actually there are other dependencies and it is not allowed to change the table. So with the same structure I have to go.

    I think what I am doing is the only way.

    Thanks a lot once again for your replies.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ashu000 View Post
    UPDATE STORE SET INDICATOR = :INDICATOR WHERE STATE IN (SELECT STORE_NUMBER FROM MAPPING WHERE STATE IN (: STATE))
    Maybe try a correlated subquery:
    Code:
    UPDATE STORE s SET INDICATOR = :INDICATOR WHERE EXISTS
    (SELECT 1 FROM MAPPING WHERE s.STATE=STORE_NUMBER and STATE IN (: STATE))
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •