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.

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.

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.

Maybe try a correlated subquery: