User variable inside multinested query

Question

I have to optimize a rather long complex query with multiple queries inside. There is a subquery that is repeated many times as a 3rd gen SELECT:

(SELECT mc.cotmoneda2 FROM monedacotizaciones mc  WHERE date(mc.`FechaHora`)<= date( p.Fechacreacion) AND mc.tipo=0 order by mc.`FechaHora`desc limit 1))

heres a reduced version of the complete query:

SELECT p.ID
  ,p.Tipo
  , p.Numero
  , p.Nombre
  , e.Empresa
  ,(CASE p.NroMoneda WHEN 1 Then (SELECT sum(fi.ImportePrecio1/(SELECT mc.cotmoneda2 FROM monedacotizaciones mc  where date(mc.`FechaHora`)<= date( p.Fechacreacion) and mc.tipo=0 order by mc.`FechaHora`desc limit 1))
                                    FROM facturasitems fi inner join facturas f on (fi.idFactura= f.Recid)
                                    where (f.estado =0 or f.estado =1 or f.estado =3 ) and  f.idpedido = p.`recid`)

                       ELSE  (SELECT sum(fi.ImportePrecio2)
                             FROM facturasitems fi inner join facturas f on (fi.idFactura= f.Recid)
                             where (f.estado =0 or f.estado =1 or f.estado =3 ) and  f.idpedido = p.`recid`) end) as FacturadoUSA

  ,(SELECT sum(Ci.ImportePrecio1/(SELECT mc.cotmoneda2 FROM monedacotizaciones mc  where date(mc.`FechaHora`)<= date( p.Fechacreacion) and mc.tipo=0 order by mc.`FechaHora`desc limit 1))
     FROM Comprasitems ci inner join Compras C on (ci.idCompra= C.Recid)
     WHERE (c.estado =0 OR c.estado =1 ) AND C.idpedido = p.`recid`) as CostoRealUSA


  ,(SELECT sum(dgv.importe/(SELECT mc.cotmoneda2 FROM monedacotizaciones mc  where date(mc.`FechaHora`)<= date( p.Fechacreacion) and mc.tipo=0 order by mc.`FechaHora`desc limit 1))
    FROM detalles_gastosvarios23 dgv
    where dgv.idref = p.Recid) as GastosReales


FROM Pedidos p INNER JOIN  `contactos` ON (p.`idref`=`contactos`.`idcontacto`)
           INNER JOIN `empresas` e ON (contactos.`idempresa`= e.`idempresa`)
           INNER JOIN `talonarios` ON (`talonarios`.`recid`= p.`idtalonario`)
WHERE  (p.`fechacreacion` BETWEEN '<%fechainicio%>' AND '<%fechafin%>')
  AND talonarios.NroSucursal =1
GROUP BY p.Numero

What I want to do is create a user variable containing the subquery, so that it will be reevaluated for each record, but only once per record. The way it is now it works, but takes over 3 minutes!. I have tried many different options many times but looks like Im not getting the syntax right. The thing is the user variable subquery contains a reference to p.

Thanks, sorry for my poor english.

It is better to find a way to move this to a `FROM` clause or into a subquery. — Gordon Linoff, Mar 16 '16 at 19:18
Your English is better than a fair amount of people who have English as their first language :) — Jeff.Clark, Mar 16 '16 at 20:30
Gordon: it is a subquery, but as it is, its running many times for each record, when its only needed once per record. This is exactly what Im asking for, Im trying to avoid redundancy. How could this be moved to the FROM clause? Jeff: Thanks! :) — Ricardo Olivo Poletti, Mar 16 '16 at 23:55

score 0 · Answer 1 · answered Mar 16 '16 at 20:26

0

I would say one way to speed up the query is to make a temporary table. There are probably other ways, but this is one way. Part of what is making it slow is all of the conditions like, "f.estado = 0 OR f.estado = 1 OR f.estado = 3" etc... repeating over and over. If your temporary table only includes a list of records with those conditions met, it will go faster.

SELECT <Desired Columns>
INTO #<TableName>
FROM facturasitems fi inner join facturas f on (fi.idFactura= f.Recid)
WHERE (f.estado =0 or f.estado =1 or f.estado =3 ) and  f.idpedido = p.`recid`)

Then one of your queries may look like

SELECT sum(fi.ImportePrecio2)
FROM #<TableName>

Your query will no longer have to look at all of those conditions each time.

answered Mar 16 '16 at 20:26

Jeff.Clark

599
1
5
27

Ok, thank you Sir!. I was looking for the syntax to create a dynamic user variable that would be recalculated for each record, but any other ideas like this to optimize the query are superwelcome, in fact this might be the answer Im looking for!, but Im not sure how this would be implemented. Where would this temporary table subquery be in the *global* query?. – Ricardo Olivo Poletti Mar 16 '16 at 23:46
Ok, great! but theres a reference to table 'p' which is not in the subquery, but in the global query. You see what I mean?. Maybe I'm wrong, but I dont think this will work, that is, unless we change the FROM clause to be JOIN with the p table. Please let me know if I made sense or if I'm off. Thanks! – Ricardo Olivo Poletti Mar 17 '16 at 00:05
Took me a while but I just understood your point. Ill try that, thank you! – Ricardo Olivo Poletti Mar 17 '16 at 01:21

User variable inside multinested query

1 Answers1