Query Optimization

UPDATE Gin#1_lat.dbo.tbl_tide_sal
SET Connects = (
              SELECT isnull(sum(doc), 0)
              FROM ##tempa AS gmd
              WHERE gmd.agent IS NOT NULL
                      AND gmd.callservice = h.service
                      AND gmd.midnight = h.midnight
                      AND gmd.callhour = h.CallHour
                      AND gmd.calltz = h.TZ
                      AND gmd.ac = h.st
              GROUP BY midnight
                      ,callhour
                      ,callservice
                      ,calltz
                      ,ac
              )
FROM ##tempa
       , Gin#1_lat.dbo.tbl_tide_sal AS G

This query does not even to compile. The inner query refers to an alias h which is not defined. So this does not seem to be the actual code.

But as other have pointed out ##tempa does not belong in the outer FROM clause. This would be a better query:

 UPDATE Gin#1_lat.dbo.tbl_tide_sal
 SET Connects = (
               SELECT isnull(sum(doc), 0)
               FROM ##tempa AS gmd
               WHERE gmd.agent IS NOT NULL
                       AND gmd.callservice = h.service
                       AND gmd.midnight = h.midnight
                       AND gmd.callhour = h.CallHour
                       AND gmd.calltz = h.TZ
                       AND gmd.ac = h.st
               )
 FROM Gin#1_lat.dbo.tbl_tide_sal AS h

I also removed the GROUP BY which serves no purpose here.

---

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se