Improving performance of update with sub-queries with join

Question

I have a large database where I run an update every day to update the previous day activity. The update below works fine although it is really slow: when I have more than 1000 records to update it might take more than 40 seconds. Please notice that it is the same sub-query running 9x just to update the columns for 1 record. I'm aware that using joins I can improve performance although my SQL skills are limited and although I have tried I have not suceeded.

I wonder if anyone could help with the transformation!

This is the code:

The update receives 2 variables: @Arg_ID_cartera and @Arg_UID_pr.

update gestor_val_pos 
        set cantidad = (select top 1 gestor_transac.shabal from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera
                      order by gestor_transac.fecha_tran desc
                     )
        , coste_div_loc = (select top 1 gestor_transac.Coste_LOC_BAL from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera
                      order by gestor_transac.fecha_tran desc
                     )
         ,  coste_div_car = (select top 1 gestor_transac.Coste_CAR_BAL from gestor_transac
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera 
                      order by gestor_transac.fecha_tran desc
                     ) 
        ,  POS_cum_Inflows = (select top 1 gestor_transac.TRAN_Cum_inflows from gestor_transac 
                     where CONVERT(date,gestor_transac.fecha_tran) <= 
                     CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                     gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera
                     order by gestor_transac.fecha_tran desc
                     )    
        ,  POS_cum_Outflows = (select top 1 gestor_transac.TRAN_Cum_Outflows from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera
                      order by gestor_transac.fecha_tran desc
                     )    
        ,  Descripcion = (select top 1 gestor_transac.descrpcion from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera order by 
                      gestor_transac.fecha_tran desc
                     ) 
        , Custodio = (select top 1 gestor_transac.custodio from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera order by 
                      gestor_transac.fecha_tran desc
                     )
        , Fecha_compra = (select top 1 gestor_transac.fecha_tran from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera order by 
                      gestor_transac.fecha_tran desc
                     )
        , Fecha_vencimiento = (select top 1 gestor_transac.fecha_vencimiento from gestor_transac 
                      where CONVERT(date,gestor_transac.fecha_tran) <= 
                      CONVERT(date,gestor_val_pos.fecha_trade) and gestor_transac.uid_producto= 
                      gestor_val_pos.uid_producto and id_cartera= @Arg_ID_cartera order by 
                      gestor_transac.fecha_tran desc
                     )
where id_cartera= @Arg_ID_cartera and uid_producto= @Arg_UID_pr;

Answer 1

You can do this with a simple joined update:

UPDATE p
SET
          cantidad = t.shabal
        , coste_div_loc = t.Coste_LOC_BAL
        ,  coste_div_car = t.Coste_CAR_BAL
        ,  POS_cum_Inflows = t.TRAN_Cum_inflows
        ,  POS_cum_Outflows = t.TRAN_Cum_Outflows
        ,  Descripcion = t.descrpcion
        , Custodio = t.custodio
        , Fecha_compra = t.fecha_tran
        , Fecha_vencimiento = t.fecha_vencimiento

FROM gestor_val_pos AS p
LEFT JOIN (
    select top 1
        t.shabal,
        t.Coste_LOC_BAL,
        t.Coste_CAR_BAL,
        t.TRAN_Cum_inflows,
        t.TRAN_Cum_Outflows,
        t.descrpcion,
        t.custodio,
        t.fecha_tran
        t.fecha_vencimiento
    from gestor_transac AS t
    where CONVERT(date, t.fecha_tran) <= CONVERT(date, p.fecha_trade)
      and t.uid_producto = p.uid_producto
      and id_cartera = @Arg_ID_cartera
    order by t.fecha_tran desc
) g

where id_cartera = @Arg_ID_cartera
  and uid_producto = @Arg_UID_pr;

Further notes:

• CONVERT(date, t.fecha_tran) <= CONVERT(date, p.fecha_trade) will not hit an index properly. Instead use something like CONVERT(date, t.fecha_tran) <= p.fecha_trade or t.fecha_tran <= CONVERT(date, p.fecha_trade) depending on which index you want to hit.
• I have used LEFT JOIN, an INNER JOIN may what you need instead
• Rewriting this using row-numbering may be more or less efficient, depending on cardinalities (percentage of rows hit)
• Note the use of aliases t and p they make it much easier to read and write the SQL
• Always specify all tables of columns

Answer 2

I don't know type of columns table. You must change type of parameter.

You can declare parameter and set parameter from main table just by one excute query and update column with params.

Try this:

------------------------------------**Main Parameter**-------------------------        
-- you can create procedure with main parameter and exec procedure
DECLARE @Arg_ID_cartera bigint --*Set This Value*-- --(int, uniqueidentifier , ... i dont know type)
DECLARE @Arg_UID_pr uniqueidentifier--*Set This Value*----(int, bigint , ... i dont know type)

------------------------------------**gestor_val_pos Parameter**-------------------------     
DECLARE @fecha_trade bigint--(int, nvarchar(500) , ... i dont know type)
DECLARE @uid_producto uniqueidentifier--(int, nvarchar(500) , ... i dont know type)

------------------------------------**Update Parameter**-------------------------  
DECLARE @shaba nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @Coste_LOC_BAL nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @Coste_CAR_BAL nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @TRAN_Cum_inflows nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @TRAN_Cum_Outflows nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @descrpcion nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @custodio nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @fecha_tran nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
DECLARE @fecha_vencimiento nvarchar(500) --(int, uniqueidentifier , ... i dont know type)
   
------------------------------------**Set gestor_val_pos parameter **-------------------------   
SELECT @fecha_trade = g.fecha_trade, @uid_producto = g.uid_producto
FROM  gestor_val_pos  g
WHERE id_cartera = @Arg_ID_cartera and uid_producto= @Arg_UID_pr;

------------------------------------**@shaba , @Coste_LOC_BAL , @Coste_CAR_BAL, @TRAN_Cum_inflows, @TRAN_Cum_Outflows, @descrpcion, @custodio, @fecha_tran, @fecha_vencimiento from gestor_transac**-------------------------                     

SELECT TOP 1 @shaba = gestor_transac.shabal , 
    @Coste_LOC_BAL = gestor_transac.Coste_LOC_BAL,
    @Coste_CAR_BAL = gestor_transac.Coste_CAR_BA,
    @TRAN_Cum_inflows = gestor_transac.TRAN_Cum_inflows,
    @TRAN_Cum_Outflows = gestor_transac.TRAN_Cum_Outflows,
    @descrpcion = gestor_transac.descrpcion,
    @custodio = gestor_transac.custodio,
    @fecha_tran = gestor_transac.fecha_tran,
    @fecha_vencimiento = gestor_transac.fecha_vencimiento
FROM gestor_transac 
WHERE CONVERT(date,gestor_transac.fecha_tran) <= CONVERT(date,@fecha_trade) 
AND gestor_transac.uid_producto= @uid_producto 
AND id_cartera= @Arg_ID_cartera
ORDER BY gestor_transac.fecha_tran DESC
       
------------------------------------**UPDATE**-------------------------         

UPDATE g SET cantidad = @shaba, 
    coste_div_loc = @Coste_LOC_BAL,
    coste_div_car = Coste_CAR_BAL,
    POS_cum_Inflows = @TRAN_Cum_inflows,
    POS_cum_Outflows = @TRAN_Cum_Outflows,
    Descripcion = @descrpcion,
    Custodio = @custodio,
    Fecha_compra = @fecha_tran,
    Fecha_vencimiento = @fecha_vencimiento
FROM  gestor_val_pos  g
WHERE id_cartera = @Arg_ID_cartera AND uid_producto= @Arg_UID_pr;