How to optimize a select within an update?

Question

How to optimize a select within an update?

Navigation

#1 by (0 votes)

0

I created a stored-procedure that updates the balances of the Handle table. At first I tried to use From Movimentacao within the update of the move, only that returned error. I fixed this problem by doing FROM (SELECT * FROM Movimentacao) as L2 . But this caused another problem: slowness if the table is too large, because it gets SELECT * FROM Movimentacao all the time. I tested with 10000 records and got very slow. I was breaking my head to build a query that was quick in the update with select , but it's a bit difficult.

Follow the stored-procedure code in MySql:

BEGIN

 set @a:=0;

 update Movimentacao d set d.ordem = (select @a:=@a+1) where id_conta = wconta
 order by data;

 UPDATE Movimentacao set saldo= (SELECT SUM(IF(operacao = 'Entrada', valor, -valor))
 FROM (SELECT * FROM Movimentacao) as L2
 WHERE L2.ordem <= Movimentacao.ordem and id_conta = wconta order by data,ordem)
 WHERE id_conta = wconta
 ORDER BY data,ordem;

END

mysql query stored-procedures select

asked by anonymous 31.01.2017 / 21:54

1 answer

loading date time without javascript ZipArchive call error :: close ()

score 0 · Answer 1

I work a lot with query optimization for MS Sql Server. In general I solve a lot with the use of temporary tables limiting my universe of data.

It seems that you are working with financial movement and that you need to update the balance with the sum of the previous operations that record.

It seems that MySQL accepts CASE and INNER JOIN in UPDATEs, so I suggest something like this:

BEGIN

 set @a:=0;

 update Movimentacao d set d.ordem = (select @a:=@a+1) where id_conta = wconta order by data;

CREATE TEMPORARY TABLE Temp_Movimentacao SELECT * FROM Movimentacao order by data;

  UPDATE Movimentacao AS M INNER JOIN 
      Temp_Movimentacao AS L2
      ON L2.ordem <= M.ordem AND id_conta = wconta
    SET M.saldo = 
        SUM(CASE L2.operacao = 'Entrada' THEN L2.valor ELSE L2.valor*-1 END)
    WHERE id_conta = wconta

END

I did not understand the use of ORDER in UPDATE since it has no logical impact in my view since you are creating an order based on dates, so I took it. I would need to understand your problem a little better.

I hope I have helped.