I have a validation table which gets updated through a insert statement. The fields in the select clause has a computed column and the t_validate table gets updated once matching policies and other conditions are satisfied.
Now This query in previuos runs would complete in 5 mins. But now it is taking more than 2 hours and still query does not complete. For information, this step is taking longers hours as additional data has been added to table b (> 1 mil records).
Please help to optimize this query. Thanks....
insert into t_validate (policy,form,date,premium)
Select a.policy, a.form, a.date, sum(a.premium/b.premprcnt) as premium
from b inner join a on
b.policy = a.policy and
b.form = a.form and
b.date = a.date and
b.location_date = a.location_date and
b.location_pin = a.location_pin and
b.state = a.state
group by a.policy, a.form, a.date