Query Optimization

There are a few things that I notice about your query.

1.        You need to try and work in a relational format so that the execution of the code can perform faster. So it’s a good practice that if you have subquery for the update to have it in the “From clause” since you will be working in the first layer of the logical model.  Therefore you will have access to the attributes “set columns” earlier.
2.        You need to try to work with the SQL standard in terms of the predicate logic. The join conditions needs to be constraint or you will have a Cartesian product this would result in the product of millions of records.
3.        A good practice is to use a simple logic to constraint your data. In this case an ‘or’ would do what you need in a faster way.
4.        If any of the tables involved are not used in the predicate logic or the attribute they should be removed since this will only create more work for the execution plan.
5.        Always keep in mind that key words will be highlighted in blue and this can slow the execution of the query to avoid this you need to enclose the keyword in brackets []

Remember the logical model is the fastest way to access the data. And it follows the hierarchy of From > Where > group by > Having > Select > Order by

With that been said I think this would be my revised code.

Updateh

       set h.pay = aodx.CoC

from

       dbo.tbl_cal_db AS h

       Join (SELECT

                                  isnull(sum(coc), 0) CoC

                                  ,midnight

                                  ,callhour

                    ,callservice

                    ,calltz

                    ,ac

               FROM ##tempa AS aod

               WHERE (aod.agent = 95 or aod.agent = 206)

                       AND aod.midnight = h.midnight

                       AND aod.callservice = h.[service]

                       AND aod.callhour = h.CallHour

                       AND aod.calltz = h.TZ

                       AND aod.ac = h.st

               GROUP BY midnight

                       ,callhour

                       ,callservice

                       ,calltz

                       ,ac

                     ) aodx on aodx.midnight = h.midnight

                       AND aodx.callservice = h.[service]

                       AND aodx.callhour = h.CallHour

                       AND aodx.calltz = h.TZ

                       AND aodx.ac = h.st