I have this simplified query where I have a nested select to get the last date of the material
SELECT
M.SAP_MATERIAL
CP.CODIGO AS CODIGO_PUENTE,
(
SELECT TOP 1 FECHA_ENVIO FROM
CABECERA_ENTRADA AS CAB
LEFT JOIN DETALLE_ENTRADA AS DET ON
DET.NUMERO_GUIA = CAB.NUMERO_GUIA
AND DET.TIPO_GUIA = CAB.TIPO_GUIA
AND DET.ID_BODEGA = CAB.ID_BODEGA
AND DET.BOD_SALIDA = CAB.BOD_SALIDA
WHERE M.SAP_MATERIAL = DET.SAP_MATERIAL
ORDER BY FECHA_ENVIO DESC
) AS FECHA
FROM
MATERIAL AS M
My question is if this can be optimized to be more efficient, perhaps in a left join
Being able to suggest an optimization if knowing the structure of the tables and their indexes is almost impossible, but the subquery pattern at the column level is usually quite inefficient, taking this subquery to the level of a
left join
could improve a lot, but that will depend on how you have configured the indices. In your query, I understand, that you want to get theFECHA_ENVIO
most current for eachSAP_MATERIAL
, this, we can basically solve it like this:The success of the optimization will depend on this query, which we can incorporate as a subquery, to achieve what you are looking for:
Thinking about optimizing code having little data for the scenario is complicated to say the least. But you can try various scenarios in addition to the one already suggested by @Patricio Moracho, using a derived table is sure to be much more efficient than a subquery.
Now, since you introduce the Sql Server tag, maybe you can improve it by using, for example, Last_Value.
It is evident that the scenario will not be exactly what you propose, because I have not even defined primary keys for any table, but for the purpose of what I am going to show you it may be enough, since it is you who has to evaluate, and in what conditions one query may be more efficient than another.
Another option, using the Apply operator
In all the options proposed, since you need the last date, having an index by Send_Date would be much more efficient.