Query Optimization

RyanAB

How can avoid updating every row multiple times and optimize this large table in to one? Do you have a clue? it looks quite complex.

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

UPDATE Gin#1_lat.dbo.tbl_tide_sal AS G

SET RPC = (

               SELECT isnull(sum(coc), 0)

               FROM ##tempa AS gmd

               WHERE gmd.agent IN (

                               83

                               ,85

                               ,203

                               ,207

                               ,303

                               ,306

                               )

                       AND gmd.midnight = h.midnight

                       AND gmd.callservice = h.service

                       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

UPDATE uni#1_stats.dbo.tbl_mesa_tz

SET PTP = (

               SELECT isnull(sum(coc), 0)

               FROM ##tempa AS gmd

               WHERE gmd.agent IN (

                               83

                               ,303

                               )

                       AND gmd.midnight = h.midnight

                       AND gmd.callservice = h.service

                       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

UPDATE Gin#1_lat.dbo.tbl_tide_sal AS G tz

SET PAY = (

               SELECT isnull(sum(coc), 0)

               FROM ##tempa AS gmd

               WHERE gmd.agent IN (

                               85

                               ,306

                               )

                       AND gmd.midnight = h.midnight

                       AND gmd.callservice = h.service

                       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

UPDATE Gin#1_lat.dbo.tbl_tide_sal AS G

SET DEFER = (

               SELECT isnull(sum(coc), 0)

               FROM ##tempa AS gmd

               WHERE gmd.agent = 203

                       AND gmd.midnight = h.midnight

                       AND gmd.callservice = h.service

                       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

UPDATE Gin#1_lat.dbo.tbl_tide_sal AS G

SET ABANDON = (

               SELECT isnull(sum(coc), 0)

               FROM ##tempa AS gmd

               WHERE gmd.switch = 1

                       AND gmd.midnight = h.midnight

                       AND gmd.callservice = h.service

                       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

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET Connects = 0

WHERE Connects IS NULL

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET RPC = 0

WHERE RPC IS NULL

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET PTP = 0

WHERE PTP IS NULL

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET PAY = 0

WHERE PAY IS NULL

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET DEFER = 0

WHERE DEFER IS NULL

UPDATE Gin#1_lat.dbo.tbl_tide_sal

SET ABANDON = 0

WHERE ABANDON IS NULL