Optimize for speed a simple stored procedure

Question

Hello:

In SQL 2008 I've this easy-but-bad-write sp that works:

ALTER PROCEDURE [dbo].[paActualizaCapacidadesDeZonas]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IdArticulo AS INT
    DECLARE @ZonaAct AS INT
    DECLARE @Suma AS INT

    UPDATE CapacidadesZonas SET Ocupado=0

    DECLARE csrSumas CURSOR FOR
         SELECT AT.IdArticulo, T.NumZona, SUM(AT.Cantidad) 
         FROM ArticulosTickets AT 
              INNER JOIN Tickets T ON AT.IdTicket = T.IdTicket
         GROUP BY AT.IdArticulo, T.NumZona

    OPEN csrSumas

    FETCH NEXT FROM csrSumas INTO @IdArticulo, @ZonaAct, @Suma

    WHILE @@FETCH_STATUS = 0
    BEGIN

         UPDATE CapacidadesZonas SET Ocupado = @Suma
         WHERE NumZona = @ZonaAct AND IdArticulo = @IdArticulo

         FETCH NEXT FROM csrSumas INTO @IdArticulo, @ZonaAct, @Suma
    END

    CLOSE csrSumas
    DEALLOCATE csrSumas   
END

I know: I must avoid cursors, so I'm pretty sure that it can be done in a much proper way.

I've tried with a single Update query:

UPDATE CapacidadesZonas SET Ocupado = 
(SELECT SUM(AT.Cantidad) 
 FROM ArticulosTickets AT 
       INNER JOIN Tickets T ON AT.IdTicket = T.IdTicket
     GROUP BY AT.IdArticulo, T.NumZona)

But this is really wrong, because the select returns more than one row.

I'm feeling bad with this, because it is supposed must be easy for me, but I can't find the equivalent query.

Any suggestions?

Thanks in advance.

Answer 1

Try:

UPDATE cz
SET Ocupado = SUM(AT.Cantidad)
FROM CapacidadesZonas as cz
INNER JOIN ArticulosTickets AT ON cz.numZona = at.numZona and cz.IDArticulo = at.IDArticulo
INNER JOIN Tickets T ON AT.IdTicket = T.IdTicket
GROUP BY AT.IdArticulo, T.NumZona

Answer 2

There are many different solutions to this problem-- see this article for a few options. Here's one way: use a derived table.

UPDATE CapacidadesZonas SET Ocupado=0 WHERE Ocupado <> 0;

UPDATE CapacidadesZonas 
SET Ocupado = SUM(s.Cantidad)
FROM CapacidadesZonas C INNER JOIN 
(
SELECT T.NumZona, AT.IdArticulo, SUM(AT.Cantidad) as Ocupado
    FROM ArticulosTickets AT 
    INNER JOIN Tickets T ON AT.IdTicket = T.IdTicket
    GROUP BY AT.IdArticulo, T.NumZona
) s ON s.NumZona = C.NumZona AND s.IdArticulo = C.IdArticulo;

Caveats:

• are you expecting that the CapacidadesZonas table is available to a live application while the update is happening? If so you may have a locking or perf issue since SQL will may lock the whole table for the update of every row. If this is the case, consider doing your update in batches (e.g. of 1,000 rows each). UPDATE TOP makes batching easy.
• sometimes SQL picks a suboptimal plan for queries like this. it may be faster to load a temp table (like in astander's solution above, but using a temp table instead of a table var) than to try to do the update as a single query. If you do this, remember to make sure there's an index on (IDArticulo, NumZona) on the the temp table before you do your update.