Ayuda con optimización de un Store Procedure.

Question

Bueno, necesito ayuda con la optimización de un store (no tengo mucha experiencia en esto)

La cosa es que este sp lo utilizo para copiar toda la tabla A a la tabla B y luego añadir campos como subconsultas de otras tablas (x,y,z,etc).

En total son la tabla principal A tiene cerca de 30 campos + las sub consultas 40, En total esta ultima tabla generada cuenta son unos 70 campos.

El motivo de esta tabla es que la organización quiere un reporte en excel, yo en el modelo de datos enlazo por bigquery y muestro todos los datos de esta tabla (no son muchos registros, solo 500  día pero se cambia el filtro del modelo de datos para mostrar solo la data del mes en curso).

Solo necesito una alternativa de optimizar el sp ya que demora 1 hora con 20 minutos en ejecutar, trate de explicar bien el problema para los que no pueden leer el código ya que es largo (para mi lo es).

Muchas Gracias de antemano!

DECLARE @RETROCESO Int 
IF DAY(getdate())='1' OR DAY(getdate())='2' 
BEGIN 
	SET @RETROCESO=-1
	PRINT @RETROCESO
END 
ELSE 

BEGIN 


	SET @RETROCESO=-1
END 

	WHILE @RETROCESO <0
	BEGIN 
	DECLARE @FECHA DATEtime =GETDATE ()+@RETROCESO
	print @fecha




BEGIN
--DECLARE @FECHA DATEtime =GETDATE()-1
--BORRAR EN CASO SE AUTOMATICE VARIAS VECES AL DIA--delete from H_BASE_PRODUCTIVIDAD where FEC_HOJA_CASTEADA =@FECHA


--h_cre_credito creditos en curso data clientes y saldo capital
		  IF OBJECT_ID('TEMPDB..#credito_cliente_actual') IS NOT NULL
		DROP TABLE #credito_cliente_actual

		select sum(IMP_SAL_CAPITAL) as saldo_capital_actual,count(cod_credito) as n_credito_actual,de.COD_PERSONAL 
		into #credito_cliente_actual
		from H_CRE_CREDITO hcc
		left join DIM_EJECUTIVO de on de.COD_EJECUTIVO = hcc.COD_EJECUTIVO
		where COD_PRD not in ('663',
		'660',
		'661',
		'662')
		and TIP_SITUACION <> '07'
		group by de.COD_PERSONAL

--cuadre cartera data clientes y saldo capital
		  IF OBJECT_ID('TEMPDB..#credito_cliente_mes_pasado') IS NOT NULL
		DROP TABLE #credito_cliente_mes_pasado

		select de.[COD_PERSONAL],sum(IMP_SAL_CAPITAL) AS SALDO_CAPITAL_Anterior,
		count(distinct(cod_cli)) AS CLIENTES_anterior  
		into #credito_cliente_mes_pasado
		from CUADRE_CARTERA  cc
		left join DIM_EJECUTIVO de on de.DES_EJECUTIVO =cc.[EJECUTIVO DE NEGOCIOS]
		where  
		 [año_proce] = year(dateadd(month,-1,getdate())) and MES_PROCE = month(dateadd(month,-1,getdate())) and
		(producto <> 'PROFAE' and  producto <> 'PROREACTIVA30' and producto <> 'PROREACTIVA10' and producto <> 'PROREACTIVA300' )
		and F_CALIDAD_CARTERA_XRM <> '40. CASTIGADOS'
		group by de.[COD_PERSONAL]



--creditos mora dia 0 de cre credito
--cuadre cartera data clientes y saldo capital
		IF OBJECT_ID('TEMPDB..#credito_mora_dia_0') IS NOT NULL
		DROP TABLE #credito_mora_dia_0

		select 
		 sum(IMP_SAL_CAPITAL) as saldo_capital_actual_0,de.COD_PERSONAL
		 into #credito_mora_dia_0
		 from H_CRE_CREDITO hcc left join DIM_EJECUTIVO de on de.COD_EJECUTIVO = hcc.COD_EJECUTIVO
		 WHERE F_CALIDAD_CARTERA = '10. NORMAL (0)'  and
		 COD_PRD not in ('663',
		'660',
		'661',
		'662')
		and TIP_SITUACION <> '07'
		group by de.COD_PERSONAL


--dia 0 cuadre cartera
		  IF OBJECT_ID('TEMPDB..#credito_mora_dia_0_mes_anterior') IS NOT NULL
		DROP TABLE #credito_mora_dia_0_mes_anterior

		select de.[COD_PERSONAL],sum(IMP_SAL_CAPITAL) AS saldo_capita_al_dia 
		into #credito_mora_dia_0_mes_anterior
		from CUADRE_CARTERA  cc
		left join DIM_EJECUTIVO de on de.DES_EJECUTIVO =cc.[EJECUTIVO DE NEGOCIOS]
		where  
		 [año_proce] = year(dateadd(month,-1,getdate())) and MES_PROCE = month(dateadd(month,-1,getdate())) and
		(producto <> 'PROFAE' and  producto <> 'PROREACTIVA30' and producto <> 'PROREACTIVA10' and producto <> 'PROREACTIVA300' )
		and F_CALIDAD_CARTERA_XRM = '10. NORMAL (0)'
		and F_CALIDAD_CARTERA_XRM <> '40. CASTIGADOS'
		group by de.[COD_PERSONAL]


--tabla telefonos
		  IF OBJECT_ID('TEMPDB..#tel_val') IS NOT NULL
		DROP TABLE #tel_val

		SELECT COD_PERSONAL,SUM(CONVERT(DECIMAL,VALIDADOS))/COUNT(CONVERT(DECIMAL,VALIDADOS)) as valido
		into #tel_val
		FROM (
		select  DE.COD_PERSONAL,
		case when validado = 'SI' THEN 1
		WHEN VALIDADO = 'SIN REVISAR' THEN 0 END AS VALIDADOS

		from REL_TLF_CLIENTE_REPORTE tel left join DIM_EJECUTIVO de on tel.des_ejecutivo = de.des_ejecutivo)A
		GROUP BY COD_PERSONAL


--TABLA %REGISTRO_DIRECCION
		  IF OBJECT_ID('TEMPDB..#DIR_CLI') IS NOT NULL
		DROP TABLE #DIR_CLI
		-- DECLARE @FECHA DATEtime =GETDATE()
		SELECT COD_PERSONAL , SUM (
		CONVERT(DECIMAL,CASE WHEN NUM_COORD_X_REF_DOM IS NOT NULL OR NUM_COORD_X_REF_NEG IS NOT NULL THEN 1 ELSE 0 END))
		/ COUNT (
		CONVERT(DECIMAL,CASE WHEN NUM_COORD_X_REF_DOM IS NOT NULL OR NUM_COORD_X_REF_NEG IS NOT NULL THEN 1 ELSE 0 END))
		AS TOTAL
		into #DIR_CLI
		FROM DIR_REFERENCIADA DR LEFT JOIN  DIM_EJECUTIVO de on DR.des_ejecutivo = de.des_ejecutivo
		WHERE CONVERT(DATE,CONVERT(DATE,FEC_PROCESO,105)) = CONVERT(DATE,getdate(),105)
		GROUP BY COD_PERSONAL

--TABLA MEGAS
 		IF OBJECT_ID('TEMPDB..#TEMP_MEGAS') IS NOT NULL	
		DROP TABLE #TEMP_MEGAS
		
		select deviceid,(sum(convert(decimal,bytesdownloaded))+sum(convert(decimal,bytesuploaded)))/1000000 as Megas  
		INTO #TEMP_MEGAS
		from [192.168.87.31\SVRMSSQL].MDM.[dbo].[TRAFFIC_CELULAR]
		where CONVERT(CHAR(10),convert(date,timestamp),112) = CONVERT(CHAR(10),@FECHA-1,112) 
		group by deviceid


	
--TABLA STORAGE
 		IF OBJECT_ID('TEMPDB..#TEMP_MEMORY') IS NOT NULL	
		DROP TABLE #TEMP_MEMORY
	select DISTINCT(DEVICEID),(CONVERT(DECIMAL,AVAILABLEMEMORY)/CONVERT	(DECIMAL,TOTALMEMORY)) AS MEMORY  
	INTO #TEMP_MEMORY
	FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[memory]
	where CONVERT(CHAR(10),FECHA,112) = CONVERT(CHAR(10),@FECHA,112)  
	ORDER BY MEMORY

	







 --TABLA DESEMBOLSOS TEMPORALES
 		IF OBJECT_ID('TEMPDB..#TEMP_DESEMBOLSO') IS NOT NULL	
		DROP TABLE #TEMP_DESEMBOLSO
	--	DECLARE @FECHA DATEtime =GETDATE ()-1
		select count(MONTO_DESEMBOLSO) as  NRO_DESEMB ,sum(MONTO_DESEMBOLSO) MONTO_DESEM ,Fecha,D.DES_EJECUTIVO,W.COD_PERSONAL
		INTO #TEMP_DESEMBOLSO
		FROM FACT_DESEMBOLSO A
		LEFT JOIN DIM_TIEMPO B ON A.ID_TIEMPO=B.IdTiempo
		LEFT JOIN DIM_CREDITO C ON C.ID_CREDITO=A.ID_CREDITO 
		LEFT JOIN DIM_EJECUTIVO D ON D.ID_EJECUTIVO=A.ID_EJECUTIVO
		LEFT JOIn DIM_EJECUTIVO w ON A.ID_EJECUTIVO=w.ID_EJECUTIVO	
		where convert(date,Fecha) = convert(date,@FECHA)
		group by w.COD_PERSONAL,d.DES_EJECUTIVO,Fecha

--TABLA BATERIAS
		--DECLARE @FECHA DATEtime =GETDATE ()
 		IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
		DROP TABLE #TEMP_BATERIA
		 SELECT deviceid,fecha,value as BATERIA
		 INTO #TEMP_BATERIA
		 FROM (
		 select ROW_NUMBER() OVER(PARTITION BY deviceid ORDER BY fecha desc) AS ORDEN ,* 
		 from (select deviceid,convert(datetime,timestamp) as fecha,value from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] 
		 where CONVERT(CHAR(10),convert(date,timestamp),112)  =  CONVERT(CHAR(10),@FECHA,112) 
		 ) A ) B WHERE   b.ORDEN='1' 
		
--CONSULTA LICENCIAS ORACLE (SQL ESTA MAL)

		 if OBJECT_ID('TEMPDB..#LICENCIAS_HR_TOTAL') IS NOT NULL
		 DROP TABLE #LICENCIAS_HR_TOTAL
		 SELECT
		 R.COD_PERSONAL,R.FEC_INICIO,R.FEC_FIN
		 INTO #LICENCIAS_HR_TOTAL
		 FROM PROD..CACTUS_INTEGRAL.PLA_SUBS_REG_DESC R
		 WHERE R.COD_EMPRESA = '0001' AND  @FECHA BETWEEN FEC_INICIO AND 
		 ISNULL(FEC_FIN, CONVERT(datetime,(DATEADD(MONTH,1,concat(year(@FECHA),'-', month(@FECHA),'-', '01')) - 1)))
		 ORDER BY 1, 3

--CONSULTA FERIADOS
--select * from #FERIADO

		if OBJECT_ID('TEMPDB..#FERIADO') IS NOT NULL
		DROP TABLE #FERIADO

--DECLARE @FECHA DATEtime =GETDATE ()-5
		SELECT fecha,'FERIADO' as FERIADO INTO #FERIADO 
		FROM DIM_FERIADO
		WHERE convert(date,@FECHA) = convert(date,Fecha)


--CONSULTA VACACIONES ORACLE (SQL ESTA MAL)
		if OBJECT_ID('TEMPDB..#VACACIONES_HR_TOTAL') IS NOT NULL
		DROP TABLE #VACACIONES_HR_TOTAL

--DECLARE @FECHA DATE =GETDATE ()

		SELECT COD_PERSONAL,FEC_INICIO,FEC_FINAL
		INTO #VACACIONES_HR_TOTAL
		FROM PROD..CACTUS_INTEGRAL.PLA_VACACIONES_MES 
		WHERE COD_EMPRESA = '0001' AND  @FECHA BETWEEN FEC_INICIO AND 
		ISNULL(FEC_FINAL, CONVERT(datetime,(DATEADD(MONTH,1,concat(year(@FECHA),'-', month(@FECHA),'-', '01')) - 1)))


--CONSULTA SOLICITUDES
		if OBJECT_ID('TEMPDB..#SOLICITUDES_HR_TOTAL') IS NOT NULL
		DROP TABLE #SOLICITUDES_HR_TOTAL
		SELECT (select cod_personal from DIM_EJECUTIVO cen where cen.cod_ejecutivo = soli.cod_ejecutivo ) as COD_PERSONAL, COUNT(COD_PRO_CRE) as SOLICITUDES
		INTO #SOLICITUDES_HR_TOTAL
		FROM PROD..CACTUS_SAFI.CRE_PRO_CRE soli
		WHERE COD_EMPRESA = '0001'  AND FEC_REGISTRO = CONVERT(DATE,@FECHA)
		GROUP BY COD_EJECUTIVO


	--	DECLARE @FECHA DATEtime =GETDATE ()-1
--CONSULTA RECUPERACIONES
		if OBJECT_ID('TEMPDB..#MONTO_RECUPERADO') IS NOT NULL
		DROP TABLE #MONTO_RECUPERADO

		select SUM(IMP_RECUP_TOTAL) AS RECUPERADO ,COD_PERSONAL,DIA_PROCESO 
		INTO #MONTO_RECUPERADO
		from PAGO_VS_GESTION_DIA 
		where RANGO_MORA IN ( 
		'(2-3)', 
		'(4-8)',
		'(9-15)',
		'(16-30)',
		'(31-44)',
		'(45-60)'
		)
		AND CONVERT (CHAR(10),@FECHA,112) = DIA_PROCESO
		GROUP BY COD_PERSONAL,DIA_PROCESO



--ELIMINA LA DATA PARA REPROCESAR
		 delete from HR_TOTAL where convert(date,FEC_HOJA_ruta) =convert(date,@FECHA)


 --DECLARE @FECHA DATEtime =GETDATE ()-1
		INSERT INTO HR_TOTAL
--DECLARE @FECHA DATEtime =GETDATE ()-1

		select

		EDN,
		DES_CARGO_PLANILLA AS DES_CARGO_PLANILLA,
		bp.COD_PERSONAL AS COD_PERSONAL,
		TRY_CONVERT(datetime,bp.fec_hoja_ruta) AS FEC_HOJA_RUTA,
		ENCARGATURA AS ENCARGATURA,
		bp.REGION AS REGION,
		convert(varchar,bp.AGENCIA,200) AS AGENCIA,
		[PROSPECCIÓN] AS PROSPECCION,
		[FIDELIZACIÓN] AS FIDELIZACION,
		EVALUACIÓN AS EVALUACION,
		RECUPERACIÓN AS RECUPERACION,
		[REC. COMISIÓN] AS REC_COMISION,
		[GESTIONES ADMINISTRATIVAS] AS GESTION_ADM,
		[VISITA JUDICIAL] AS VISITA_JUDICIAL,
		Preventiva AS PREVENTIVA,
		[Acompañamiento] AS ACOMPANAMIENTO,
		[Post-Desembolso] AS POST_DESEMBOLSO,
		 convert(datetime, bp.FEC_INGRESO, 105) as FEC_INGRESO,
		CARGO AS CARGO,
		CANTIDAD_LLAMADAS AS CANTIDAD_LLAMADAS,
		META AS META,
		NDiaSemana AS NDiaSemana,
		META_REAL AS META_REAL,
		LICENCIA AS LICENCIA,
		VACACIONES AS VACACIONES,
		DESCANSOS AS DESCANSOS,
		[PROSPECCIÓN VALIDA] AS PROSP_VALIDA,
		[FIDELIZACIÓN VALIDA] AS FID_VALIDA,
		[EVALUACIÓN VALIDA] AS EVAL_VALIDA,
		[RECUPERACIÓN VALIDA] AS REC_VALIDA,
		[REC. COMISIÓN VALIDA] AS REC_COMISION_VALIDA,
		CUMPLIMIENTO AS CUMPLIMIENTO,
		[VISITA JUDICIAL VALIDA] AS VISITA_JUDICIAL_VALIDA,
		[POST DESEMBOLSO VALIDO] AS POST_DESEMBOLSO_VALIDO,
		[ACOMPANAMIENTO VALIDO] AS ACOMPANAMIENTO_VALIDO,
		[PREVENTIVA VALIDA] AS PREVENTIVA_VALIDA,

-- FERIADO
		(SELECT 'FERIADOS' as FERIADO FROM #FERIADO fer WHERE BP.fec_hoja_ruta =fer.Fecha) as FERIADOS_FILTRO,

-- VACACIONES
		(SELECT 'VACACIONES' as VACACIONES FROM #VACACIONES_HR_TOTAL fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL AND BP.fec_hoja_ruta  BETWEEN fer.FEC_INICIO AND FER.FEC_FINAL ) as VACACIONES_FILTRO,

--LICENCIAS
		(SELECT 'LICENCIAS' as LICENCIAS FROM #LICENCIAS_HR_TOTAL fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL AND BP.fec_hoja_ruta  BETWEEN fer.FEC_INICIO AND FER.FEC_FIN ) as LICENCIAS_FILTRO,

--MONTO RECUPERADO
		(SELECT RECUPERADO FROM #MONTO_RECUPERADO fer WHERE BP.COD_PERSONAL = FER.COD_PERSONAL  ) as RECUPERACIONES,

--QUINTILES
		(select Q_PROD from temporal..quintil_resultados_nov qr WHERE QR.COD_PERSONAL = BP.COD_PERSONAL ) AS Q_PROD_MES,
		(select Q_DET from temporal..quintil_resultados_nov qr WHERE QR.COD_PERSONAL = BP.COD_PERSONAL ) AS Q_DET_MES,
--(SELECT FEC_INGRESO FROM H_PLA_PERSONAL HPP WHERE HPP.COD_PERSONAL = BP.COD_PERSONAL) as FECHA_INGRESO,
--(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_VACACIONES_MES where GETDATE() BETWEEN FEC_INICIO AND FEC_FINAL) then 'SI' end) AS VACACIONES,
--(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_PERS_DESCANSOS where GETDATE() BETWEEN FEC_INICIO AND FEC_FIN) then 'SI' end) AS DESCANSO

--CUMPLIMIENTO CARTERA>60
		(SELECT    CAST(CAST(VISITA AS FLOAT)/CAST(CANTIDAD AS FLOAT) AS FLOAT) AS PORCENTAJE

		FROM (
		SELECT COD_PERSONAL , COUNT (COD_CREDITO) AS CANTIDAD, SUM (CANTIDAD) AS VISITA FROM (
		SELECT  COD_PERSONAL, COD_CREDITO ,CASE WHEN VISITADO ='SI' THEN 1 ELSE 0 END AS CANTIDAD
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(31-44)','(45-60)') ) A GROUP BY COD_PERSONAL) 
		B WHERE b.cod_personal = bp.cod_personal) AS CUMPLIMIENTO_CARTERA_60,






--select * from HR_TOTAL where fec_hoja_ruta = '2020-12-12' order by edn

--select distinct (cod_personal) from HR_TOTAL where fec_hoja_ruta = '2020-12-12'

--MORA >1 MONTO
		(SELECT    SUM40 
		FROM (
		--DECLARE @FECHA DATEtime =GETDATE ()-1
		SELECT COD_PERSONAL , SUM(IMP_CAP_DEUDOR)AS SUM40 FROM (
		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(2-3)','(4-8)','(9-15)','(16-30)','(31-44)','(45-60)') 
		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS MM_1,

--MORA >30 MONTO
		(SELECT    SUM30 
		FROM (
		--DECLARE @FECHA DATEtime =GETDATE ()-1
		SELECT COD_PERSONAL ,SUM(IMP_CAP_DEUDOR) AS SUM30 FROM (
		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(31-44)','(45-60)') 
		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS MM_30,

--MORA >1 PORCENTAJE

		(SELECT    PROCENTAJE40
		FROM (
		--DECLARE @FECHA DATEtime =GETDATE ()-1
		SELECT COD_PERSONAL ,((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS PROCENTAJE40 FROM (
		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(2-3)','(4-8)','(9-15)','(16-30)','(31-44)','(45-60)') 
		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS PM_1,


--MORA >30 PORCENTAJE

		(SELECT    PORCENTAJE30 
		FROM (
		--DECLARE @FECHA DATEtime =GETDATE ()-1
		SELECT COD_PERSONAL ,((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS PORCENTAJE30 FROM (
		SELECT  COD_PERSONAL,IMP_CAP_DEUDOR ,SALDO_TOTAL
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(31-44)','(45-60)') 
		) A GROUP BY COD_PERSONAL) B WHERE b.cod_personal = bp.cod_personal) AS PM_30,


--CALIDAD CARTERA MORA
--DATA
--DECLARE @FECHA DATEtime =GETDATE ()-1
--select COD_PERSONAL,CALIDAD_CARTERA,COUNT(CALIDAD_CARTERA) AS NRO,SUM(SALDO_CAPITAL) AS SALDO_CAP,SUM(SALDO_TOTAL) as SALDO_TOT,SUM(SALDO_TOTAL)-SUM(SALDO_CAPITAL) as SALDO_DEU,
--((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS MORA1

--from PAGO_VS_GESTION_DIA
--WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112)
--GROUP BY CALIDAD_CARTERA, COD_PERSONAL

--SOLICITUDES

		(SELECT SOLICITUDES FROM #SOLICITUDES_HR_TOTAL SHT WHERE SHT.COD_PERSONAL = BP.COD_PERSONAL ) AS SOLICITUDES,


--ESTO ES PARA CREAR LA TABLA TEMPORAL Y JALAR LAS BATERIAS PROMEDIADAS
--DECLARE @FECHA DATEtime =GETDATE ()
--SELECT distinct(COD_PERSONAL),value,MAX(convert(datetime,timestamp)) AS FECHA
--FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] vs
--left join temporal..CELULARES_IMEI ci on  vs.deviceid = ci.DES_IMEI
--where cod_personal is not null  AND CONVERT(CHAR(10),timestamp,121)  between CONVERT(CHAR(10),@FECHA-10,121) and CONVERT(CHAR(10),@FECHA,121)
--group by CI.COD_PERSONAL,convert(datetime,timestamp),value


		--STORAGE
				(select MEMORY from #TEMP_MEMORY X 
				left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
				where CI.COD_PERSONAL = BP.COD_PERSONAL) AS STORAGE,



--TEMPORAL BATERIAS
--	IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
--		DROP TABLE #TEMP_BATERIA

--select deviceid,AVG(value) AS VALUE
--INTO #TEMP_BATERIA
--from (
--select deviceid,fecha,max(value) as value from(
--select deviceid,convert(date,timestamp) as fecha,value 
--from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS]
--where CONVERT(CHAR(10),convert(date,timestamp),112)  between  CONVERT(CHAR(10),getdate()-3,112) and CONVERT(CHAR(10),getdate()-1,112) 
--) a group by a.deviceid, a.fecha, a.value
--)b group by deviceid
--order by deviceid





		--METO TODO DESEMBOLSOS (# Y $)

--CONSULTA DESEMBOLSOS DE LA TEMPORAL
		(select NRO_DESEMB from #TEMP_DESEMBOLSO X where X.COD_PERSONAL = BP.COD_PERSONAL) as NRO_DESEMB,
		--CONSULTA DESEMBOLSOS MONTO DE LA TEMPORAL
		(select MONTO_DESEM from #TEMP_DESEMBOLSO X where X.COD_PERSONAL = BP.COD_PERSONAL) as MONTO_DESEMB,

--BATERIAS
			(select BATERIA from #TEMP_BATERIA X 
			left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
			where CI.COD_PERSONAL = BP.COD_PERSONAL) as BATERIA,






--from H_BASE_PRODUCTIVIDAD bp  WHERE COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  

--ESTADO DEL EDN
bp.ESTADO AS ESTADO,


		--NOTA REGULATORIO

		(SELECT NOTA FROM T_CREDITO..HR_NOTA_REGULATORIO HNR
		WHERE HNR.COD_PERSONAL = BP.COD_PERSONAL) AS NOTA_REGULATORIO,

		--NOTA NORMATIVO
		(SELECT NOTA FROM T_CREDITO..HR_NOTA_NORMATIVO HNR
		WHERE HNR.COD_PERSONAL = BP.COD_PERSONAL) AS NOTA_NORMATIVO,





	--MEGAS
				(select Megas from #TEMP_MEGAS X 
				left join temporal..CELULARES_IMEI ci on  X.deviceid = ci.DES_IMEI
				where CI.COD_PERSONAL = BP.COD_PERSONAL) AS MEGAS,

				    --dir_cli
(select total from #DIR_CLI abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS porc_referenciadas,


 --tel_valido
(select valido from #tel_val abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS telf_validos,
--Gestor de recuperaciones
GS.CAN_CLI_ASIG, GS.REG_CLI_HR, GS.NO_REG_CLI_HR, GS.NUM_PAGOS, GS.IMP_PAGOS, 
GS.REG_CLI_HR_MES, GS.NO_REG_CLI_HR_MES, GS.NUM_PAGOS_MES, GS.IMP_PAGOS_MES,
GS.TOT_CLI_TRANSADO, GS.TRS_VIGENTE, GS.TRS_VENCIDO, GS.TRS_INCUMPLIDO, GS.NUM_TRANSADOS, GS.NUM_TRANSADOS_MES,
--Abogado interno
AB.CAN_CLI_ASIG, AB.REG_CLI_APP_JUD, AB.NO_REG_CLI_APP_JUD, AB.NUM_PAGOS, AB.IMP_PAGOS, 
AB.REG_CLI_APP_JUD_MES, AB.NO_REG_CLI_APP_JUD_MES, AB.NUM_PAGOS_MES, AB.IMP_PAGOS_MES,
AB.TOT_CLI_TRANSADO, AB.TRS_VIGENTE, AB.TRS_VENCIDO, AB.TRS_INCUMPLIDO, AB.NUM_TRANSADOS, AB.NUM_TRANSADOS_MES,


  --saldo_capital_actual - en curso
(select saldo_capital_actual from #credito_cliente_actual abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_EN_CURSO,

				    --nro_clientes actual - en curso
(select n_credito_actual from #credito_cliente_actual abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS NRO_CLIENTE_EN_CURSO,

				    --saldo_capital_mes_anterior 
(select SALDO_CAPITAL_Anterior from #credito_cliente_mes_pasado abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MES_ANTERIOR,

				    --nro_clientes actual - MES ANTERIOR
(select CLIENTES_anterior from #credito_cliente_mes_pasado abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS NRO_CLIENTE_MES_ANTERIOR,

--select * from #credito_mora_dia_0
--select * from #credito_mora_dia_0_mes_anterior

				    --saldo_capital_dia 0 mes anterior
(select saldo_capita_al_dia from #credito_mora_dia_0_mes_anterior abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MORA_DIA_0_MES_ANTERIOR,

				    --saldo_capital_dia 0 - en curso
(select saldo_capital_actual_0 from #credito_mora_dia_0 abc
where abc.COD_PERSONAL = BP.COD_PERSONAL) AS SALDO_CAPITAL_MORA_DIA_0_MES_ACTUAL,

					--EXPEDIENTES
(select EXPEDIENTE from H_EXPEDIENTE HE
where HE.COD_PERSONAL = BP.COD_PERSONAL) AS EXPEDIENTE,


--CUMPLIMIENTO CARTERA>1
		(SELECT    CAST(CAST(VISITA AS FLOAT)/CAST(CANTIDAD AS FLOAT) AS FLOAT) AS PORCENTAJE

		FROM (
		SELECT COD_PERSONAL , COUNT (COD_CREDITO) AS CANTIDAD, SUM (CANTIDAD) AS VISITA FROM (
		SELECT  COD_PERSONAL, COD_CREDITO ,CASE WHEN VISITADO ='SI' THEN 1 ELSE 0 END AS CANTIDAD
		 FROM PAGO_VS_GESTION_DIA WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112) 
		and RANGO_MORA in ('(1)', '(2-3)','(4-8)','(9-15)','(16-30)') ) A GROUP BY COD_PERSONAL) 
		B WHERE b.cod_personal = bp.cod_personal) AS CUMPLIMIENTO_CARTERA_1,

 --dias de vacaciones disponible

 '' as  dias_vac_disponible
--(select disponibles from RENOVACION_HIST aaa
--where aaa.COD_PERSONAL = BP.COD_PERSONAL) AS dias_vac_disponible


from H_BASE_PRODUCTIVIDAD bp
LEFT JOIN TMP_REP_GESTOR_RECUP GS ON BP.COD_PERSONAL=GS.COD_PERSONAL AND CAST(BP.FEC_HOJA_RUTA AS DATE)=GS.FEC_PROCESO
LEFT JOIN TMP_REP_ABOG_INTERNO AB ON BP.COD_PERSONAL=AB.COD_PERSONAL AND CAST(BP.FEC_HOJA_RUTA AS DATE)=AB.FEC_PROCESO
WHERE bp.COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  




--VALIDAR DESEMBOLSOS
--select sum(monto_desem) from #TEMP_DESEMBOLSO2 where fecha = '2020-12-29'
--select sum(monto_desemb) from HR_TOTAL where CONVERT(date,fec_hoja_ruta) = '2020-12-29'


END
SET @RETROCESO=@RETROCESO+1
PRINT @FECHA
END 

Answer 1

Hola AlexanderC1992:

Es demasiado "infumable" el procedimiento para poder ayudarte, pero hay muchas cosas que puedes cambiar para mejorar.

Elimina el código innecesario, solo mancha el procedimiento y te puede llevar a errores.

Un bucle, no es un buen camino, Tienes que replantear el código para no utilizar ese bucle.

Utiliza ctes correlativos y no tablas temporales, si el volumen de datos no es brutal. Ya que las consultas que he visto, lo que están es retornando funciones de agrupación, el número de filas será pequeño. Pero si usas tablas temporales porque tienen gran número de filas, indexa.

Creo que estás utilizando un montón de subconsultas como columnas de la query, y eso no es óptimo. Dudo mucho que el optimizador de consultas, con la query que has puesto, pueda sacar algo de provecho de ellas.

Si los datos que obtienes de otras bases de datos, e incluso de otros servidores devuelven pocas filas, son mucho más rápidas las variables de tabla que las tablas temporales. La creación de una tabla temporal, lleva de 2 a 3 decimas (en mi opinión), y en función del uso de tempdb. Pero la variable de tabla es instantánea. 

En resumen, yo haría lo siguiente. Para empezar.

Crearía todos las variables que necesitase.

Llenaría estas variables con los datos de los servidores vinculados, etc.

Haría un cte correlativo para obtener el resto de conjuntos y en la salida lo mezclaría con las variables de tabla.

Como apoyo, si necesitas generar ese bucle (no está claro del código), para los días del mes, puedes hacer un conjunto que te devuelva ese rango.

;With c as (
	Select T.n
		From (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
					 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
					 (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
					(31)
			 )T(n)
		)select * from c 

Con eso y haciendo un cross join a lo que fuera, ya tienes 31 filas, que por supuesto puedes restringir al día en el que estás. (supongo que es la lógica del bucle).

Ctes correlativos

https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

Variables de tabla

https://javifer2.wordpress.com/2019/11/16/variables-de-tabla/

Por último, utiliza una herramienta para dar formato al código. Puedes personalizarlo y te será mucho más fácil de seguir.

Ej, con tú procedure y pulsando un simple botón.

Declare @Retroceso Int;

If Day(GetDate()) = '1' Or Day(GetDate()) = '2'
Begin
	Set @Retroceso = -1;
	Print @Retroceso;
End;
Else
Begin
	Set @Retroceso = -1;
End;

While @Retroceso < 0
Begin
	Declare @Fecha DateTime = GetDate() + @Retroceso;
	Print @Fecha;
Begin
	--DECLARE @FECHA DATEtime =GETDATE()-1
	--BORRAR EN CASO SE AUTOMATICE VARIAS VECES AL DIA--delete from H_BASE_PRODUCTIVIDAD where FEC_HOJA_CASTEADA =@FECHA
	--h_cre_credito creditos en curso data clientes y saldo capital
	If Object_Id('TEMPDB..#credito_cliente_actual') Is Not Null
	Begin
		Drop Table #credito_cliente_actual
	End;
	Select Sum(imp_sal_capital) As saldo_capital_actual
		 , Count(cod_credito) As n_credito_actual
		 , de.cod_personal
	Into #credito_cliente_actual
		   From h_cre_credito As hcc
				Left Join dim_ejecutivo As de On de.cod_ejecutivo = hcc.cod_ejecutivo
		   Where cod_prd Not In('663', '660', '661', '662') And tip_situacion <> '07'
		   Group By de.cod_personal;

	--cuadre cartera data clientes y saldo capital
	If Object_Id('TEMPDB..#credito_cliente_mes_pasado') Is Not Null
	Begin
		Drop Table #credito_cliente_mes_pasado
	End;
	Select de.cod_personal
		 , Sum(imp_sal_capital) As saldo_capital_anterior
		 , Count(Distinct cod_cli) As clientes_anterior
	Into #credito_cliente_mes_pasado
		   From cuadre_cartera As cc
				Left Join dim_ejecutivo As de On de.des_ejecutivo = cc.[ejecutivo de negocios]
		   Where año_proce = Year(DateAdd(month, -1, GetDate())) And mes_proce = Month(DateAdd(month, -1, GetDate())) And producto <> 'PROFAE' And producto <> 'PROREACTIVA30' And producto <> 'PROREACTIVA10' And producto <> 'PROREACTIVA300' And f_calidad_cartera_xrm <> '40. CASTIGADOS'
		   Group By de.cod_personal;

	--creditos mora dia 0 de cre credito
	--cuadre cartera data clientes y saldo capital
	If Object_Id('TEMPDB..#credito_mora_dia_0') Is Not Null
	Begin
		Drop Table #credito_mora_dia_0
	End;
	Select Sum(imp_sal_capital) As saldo_capital_actual_0
		 , de.cod_personal
	Into #credito_mora_dia_0
		   From h_cre_credito As hcc
				Left Join dim_ejecutivo As de On de.cod_ejecutivo = hcc.cod_ejecutivo
		   Where f_calidad_cartera = '10. NORMAL (0)' And cod_prd Not In('663', '660', '661', '662') And tip_situacion <> '07'
		   Group By de.cod_personal;

	--dia 0 cuadre cartera
	If Object_Id('TEMPDB..#credito_mora_dia_0_mes_anterior') Is Not Null
	Begin
		Drop Table #credito_mora_dia_0_mes_anterior
	End;
	Select de.cod_personal
		 , Sum(imp_sal_capital) As saldo_capita_al_dia
	Into #credito_mora_dia_0_mes_anterior
		   From cuadre_cartera As cc
				Left Join dim_ejecutivo As de On de.des_ejecutivo = cc.[ejecutivo de negocios]
		   Where año_proce = Year(DateAdd(month, -1, GetDate())) And mes_proce = Month(DateAdd(month, -1, GetDate())) And producto <> 'PROFAE' And producto <> 'PROREACTIVA30' And producto <> 'PROREACTIVA10' And producto <> 'PROREACTIVA300' And f_calidad_cartera_xrm = '10. NORMAL (0)' And f_calidad_cartera_xrm <> '40. CASTIGADOS'
		   Group By de.cod_personal;

	--tabla telefonos
	If Object_Id('TEMPDB..#tel_val') Is Not Null
	Begin
		Drop Table #tel_val
	End;
	Select cod_personal
		 , Sum(Convert(Decimal, validados)) / Count(Convert(Decimal, validados)) As valido
	Into #tel_val
		   From (
				  Select de.cod_personal
					   , Case
							 When validado = 'SI' Then 1
							 When validado = 'SIN REVISAR' Then 0
						 End As validados
						 From rel_tlf_cliente_reporte As tel
							  Left Join dim_ejecutivo As de On tel.des_ejecutivo = de.des_ejecutivo
				) As a
		   Group By cod_personal;

	--TABLA %REGISTRO_DIRECCION
	If Object_Id('TEMPDB..#DIR_CLI') Is Not Null
	Begin
		Drop Table #dir_cli
	End;
	-- DECLARE @FECHA DATEtime =GETDATE()
	Select cod_personal
		 , Sum(Convert(Decimal,
					   Case
						   When num_coord_x_ref_dom Is Not Null Or num_coord_x_ref_neg Is Not Null Then 1
					   Else 0
					   End)) / Count(Convert(Decimal,
											 Case
												 When num_coord_x_ref_dom Is Not Null Or num_coord_x_ref_neg Is Not Null Then 1
											 Else 0
											 End)) As total
	Into #dir_cli
		   From dir_referenciada As dr
				Left Join dim_ejecutivo As de On dr.des_ejecutivo = de.des_ejecutivo
		   Where Convert(Date, Convert(Date, fec_proceso, 105)) = Convert(Date, GetDate(), 105)
		   Group By cod_personal;

	--TABLA MEGAS
	If Object_Id('TEMPDB..#TEMP_MEGAS') Is Not Null
	Begin
		Drop Table #temp_megas
	End;
	Select deviceid
		 , (Sum(Convert(Decimal, bytesdownloaded)) + Sum(Convert(Decimal, bytesuploaded))) / 1000000 As megas
	Into #temp_megas
		   From [192.168.87.31\svrmssql].mdm.dbo.traffic_celular
		   Where Convert(Char(10), Convert(Date, timestamp), 112) = Convert(Char(10), @Fecha - 1, 112)
		   Group By deviceid;

	--TABLA STORAGE
	If Object_Id('TEMPDB..#TEMP_MEMORY') Is Not Null
	Begin
		Drop Table #temp_memory
	End;
	Select Distinct 
		   deviceid
		 , Convert(Decimal, availablememory) / Convert(Decimal, totalmemory) As memory
	Into #temp_memory
		   From [192.168.87.31\svrmssql].mdm.dbo.memory
		   Where Convert(Char(10), fecha, 112) = Convert(Char(10), @Fecha, 112)
		   Order By memory;

	--TABLA DESEMBOLSOS TEMPORALES
	If Object_Id('TEMPDB..#TEMP_DESEMBOLSO') Is Not Null
	Begin
		Drop Table #temp_desembolso
	End;
	--	DECLARE @FECHA DATEtime =GETDATE ()-1
	Select Count(monto_desembolso) As nro_desemb
		 , Sum(monto_desembolso) As monto_desem
		 , fecha
		 , d.des_ejecutivo
		 , w.cod_personal
	Into #temp_desembolso
		   From fact_desembolso As a
				Left Join dim_tiempo As b On a.id_tiempo = b.idtiempo
				Left Join dim_credito As c On c.id_credito = a.id_credito
				Left Join dim_ejecutivo As d On d.id_ejecutivo = a.id_ejecutivo
				Left Join dim_ejecutivo As w On a.id_ejecutivo = w.id_ejecutivo
		   Where Convert(Date, fecha) = Convert(Date, @Fecha)
		   Group By w.cod_personal
				  , d.des_ejecutivo
				  , fecha;

	--TABLA BATERIAS
	--DECLARE @FECHA DATEtime =GETDATE ()
	If Object_Id('TEMPDB..#TEMP_BATERIA') Is Not Null
	Begin
		Drop Table #temp_bateria
	End;
	Select deviceid
		 , fecha
		 , value As bateria
	Into #temp_bateria
		   From (
				  Select Row_Number() Over(Partition By deviceid
						 Order By fecha Desc) As orden
					   , *
						 From (
								Select deviceid
									 , Convert(DateTime, timestamp) As fecha
									 , value
									   From [192.168.87.31\svrmssql].mdm.dbo.battery_status
									   Where Convert(Char(10), Convert(Date, timestamp), 112) = Convert(Char(10), @Fecha, 112)
							  ) As a
				) As b
		   Where b.orden = '1';

	--CONSULTA LICENCIAS ORACLE (SQL ESTA MAL)

	If Object_Id('TEMPDB..#LICENCIAS_HR_TOTAL') Is Not Null
	Begin
		Drop Table #licencias_hr_total
	End;
	Select r.cod_personal
		 , r.fec_inicio
		 , r.fec_fin
	Into #licencias_hr_total
		   From prod..cactus_integral.pla_subs_reg_desc As r
		   Where r.cod_empresa = '0001' And @Fecha Between fec_inicio And ISNULL(fec_fin, Convert(DateTime, DateAdd(MONTH, 1, concat(Year(@Fecha), '-', Month(@Fecha), '-', '01')) - 1))
		   Order By 1
				  , 3;

	--CONSULTA FERIADOS
	--select * from #FERIADO

	If Object_Id('TEMPDB..#FERIADO') Is Not Null
	Begin
		Drop Table #feriado
	End;

	--DECLARE @FECHA DATEtime =GETDATE ()-5
	Select fecha
		 , 'FERIADO' As feriado
	Into #feriado
		   From dim_feriado
		   Where Convert(Date, @Fecha) = Convert(Date, fecha);

	--CONSULTA VACACIONES ORACLE (SQL ESTA MAL)
	If Object_Id('TEMPDB..#VACACIONES_HR_TOTAL') Is Not Null
	Begin
		Drop Table #vacaciones_hr_total
	End;

	--DECLARE @FECHA DATE =GETDATE ()

	Select cod_personal
		 , fec_inicio
		 , fec_final
	Into #vacaciones_hr_total
		   From prod..cactus_integral.pla_vacaciones_mes
		   Where cod_empresa = '0001' And @Fecha Between fec_inicio And ISNULL(fec_final, Convert(DateTime, DateAdd(MONTH, 1, concat(Year(@Fecha), '-', Month(@Fecha), '-', '01')) - 1));

	--CONSULTA SOLICITUDES
	If Object_Id('TEMPDB..#SOLICITUDES_HR_TOTAL') Is Not Null
	Begin
		Drop Table #solicitudes_hr_total
	End;
	Select (
			 Select cod_personal
					From dim_ejecutivo As cen
					Where cen.cod_ejecutivo = soli.cod_ejecutivo
		   ) As cod_personal
		 , Count(cod_pro_cre) As solicitudes
	Into #solicitudes_hr_total
		   From prod..cactus_safi.cre_pro_cre As soli
		   Where cod_empresa = '0001' And fec_registro = Convert(Date, @Fecha)
		   Group By cod_ejecutivo;

	--	DECLARE @FECHA DATEtime =GETDATE ()-1
	--CONSULTA RECUPERACIONES
	If Object_Id('TEMPDB..#MONTO_RECUPERADO') Is Not Null
	Begin
		Drop Table #monto_recuperado
	End;
	Select Sum(imp_recup_total) As recuperado
		 , cod_personal
		 , dia_proceso
	Into #monto_recuperado
		   From pago_vs_gestion_dia
		   Where rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)') And Convert(Char(10), @Fecha, 112) = dia_proceso
		   Group By cod_personal
				  , dia_proceso;

	--ELIMINA LA DATA PARA REPROCESAR
	Delete From hr_total
	Where Convert(Date, fec_hoja_ruta) = Convert(Date, @Fecha);

	--DECLARE @FECHA DATEtime =GETDATE ()-1
	Insert Into hr_total
	--DECLARE @FECHA DATEtime =GETDATE ()-1

	Select edn
		 , des_cargo_planilla As des_cargo_planilla
		 , bp.cod_personal As cod_personal
		 , Try_Convert( DateTime, bp.fec_hoja_ruta) As fec_hoja_ruta
		 , encargatura As encargatura
		 , bp.region As region
		 , Convert(VarChar, bp.agencia, 200) As agencia
		 , prospección As prospeccion
		 , fidelización As fidelizacion
		 , evaluación As evaluacion
		 , recuperación As recuperacion
		 , [rec. comisión] As rec_comision
		 , [gestiones administrativas] As gestion_adm
		 , [visita judicial] As visita_judicial
		 , preventiva As preventiva
		 , acompañamiento As acompanamiento
		 , [post-desembolso] As post_desembolso
		 , Convert(DateTime, bp.fec_ingreso, 105) As fec_ingreso
		 , cargo As cargo
		 , cantidad_llamadas As cantidad_llamadas
		 , meta As meta
		 , ndiasemana As ndiasemana
		 , meta_real As meta_real
		 , licencia As licencia
		 , vacaciones As vacaciones
		 , descansos As descansos
		 , [prospección valida] As prosp_valida
		 , [fidelización valida] As fid_valida
		 , [evaluación valida] As eval_valida
		 , [recuperación valida] As rec_valida
		 , [rec. comisión valida] As rec_comision_valida
		 , cumplimiento As cumplimiento
		 , [visita judicial valida] As visita_judicial_valida
		 , [post desembolso valido] As post_desembolso_valido
		 , [acompanamiento valido] As acompanamiento_valido
		 , [preventiva valida] As preventiva_valida

		   -- FERIADO
		 , (
			 Select 'FERIADOS' As feriado
					From #feriado As fer
					Where bp.fec_hoja_ruta = fer.fecha
		   ) As feriados_filtro

		   -- VACACIONES
		 , (
			 Select 'VACACIONES' As vacaciones
					From #vacaciones_hr_total As fer
					Where bp.cod_personal = fer.cod_personal And bp.fec_hoja_ruta Between fer.fec_inicio And fer.fec_final
		   ) As vacaciones_filtro

		   --LICENCIAS
		 , (
			 Select 'LICENCIAS' As licencias
					From #licencias_hr_total As fer
					Where bp.cod_personal = fer.cod_personal And bp.fec_hoja_ruta Between fer.fec_inicio And fer.fec_fin
		   ) As licencias_filtro

		   --MONTO RECUPERADO
		 , (
			 Select recuperado
					From #monto_recuperado As fer
					Where bp.cod_personal = fer.cod_personal
		   ) As recuperaciones

		   --QUINTILES
		 , (
			 Select q_prod
					From temporal..quintil_resultados_nov As qr
					Where qr.cod_personal = bp.cod_personal
		   ) As q_prod_mes
		 , (
			 Select q_det
					From temporal..quintil_resultados_nov As qr
					Where qr.cod_personal = bp.cod_personal
		   ) As q_det_mes
		   --(SELECT FEC_INGRESO FROM H_PLA_PERSONAL HPP WHERE HPP.COD_PERSONAL = BP.COD_PERSONAL) as FECHA_INGRESO,
		   --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_VACACIONES_MES where GETDATE() BETWEEN FEC_INICIO AND FEC_FINAL) then 'SI' end) AS VACACIONES,
		   --(SELECT  CASE WHEN bp.cod_personal in (select distinct(COD_PERSONAL) from H_PLA_PERS_DESCANSOS where GETDATE() BETWEEN FEC_INICIO AND FEC_FIN) then 'SI' end) AS DESCANSO
		   --CUMPLIMIENTO CARTERA>60
		 , (
			 Select Cast(Cast(visita As Float) / Cast(cantidad As Float) As Float) As porcentaje
					From (
						   Select cod_personal
								, Count(cod_credito) As cantidad
								, Sum(cantidad) As visita
								  From (
										 Select cod_personal
											  , cod_credito
											  , Case
													When visitado = 'SI' Then 1
												Else 0
												End As cantidad
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As cumplimiento_cartera_60

	--select * from HR_TOTAL where fec_hoja_ruta = '2020-12-12' order by edn
	--select distinct (cod_personal) from HR_TOTAL where fec_hoja_ruta = '2020-12-12'
	--MORA >1 MONTO
		 , (
			 Select sum40
					From (
						   --DECLARE @FECHA DATEtime =GETDATE ()-1
						   Select cod_personal
								, Sum(imp_cap_deudor) As sum40
								  From (
										 Select cod_personal
											  , imp_cap_deudor
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As mm_1

	--MORA >30 MONTO
		 , (
			 Select sum30
					From (
						   --DECLARE @FECHA DATEtime =GETDATE ()-1
						   Select cod_personal
								, Sum(imp_cap_deudor) As sum30
								  From (
										 Select cod_personal
											  , imp_cap_deudor
											  , saldo_total
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As mm_30

	--MORA >1 PORCENTAJE
		 , (
			 Select procentaje40
					From (
						   --DECLARE @FECHA DATEtime =GETDATE ()-1
						   Select cod_personal
								, (Sum(imp_cap_deudor) / Sum(saldo_total)) * 100 As procentaje40
								  From (
										 Select cod_personal
											  , imp_cap_deudor
											  , saldo_total
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(2-3)', '(4-8)', '(9-15)', '(16-30)', '(31-44)', '(45-60)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As pm_1

	--MORA >30 PORCENTAJE
		 , (
			 Select porcentaje30
					From (
						   --DECLARE @FECHA DATEtime =GETDATE ()-1
						   Select cod_personal
								, (Sum(imp_cap_deudor) / Sum(saldo_total)) * 100 As porcentaje30
								  From (
										 Select cod_personal
											  , imp_cap_deudor
											  , saldo_total
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(31-44)', '(45-60)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As pm_30

	--CALIDAD CARTERA MORA
	--DATA
	--DECLARE @FECHA DATEtime =GETDATE ()-1
	--select COD_PERSONAL,CALIDAD_CARTERA,COUNT(CALIDAD_CARTERA) AS NRO,SUM(SALDO_CAPITAL) AS SALDO_CAP,SUM(SALDO_TOTAL) as SALDO_TOT,SUM(SALDO_TOTAL)-SUM(SALDO_CAPITAL) as SALDO_DEU,
	--((SUM(IMP_CAP_DEUDOR)/SUM(SALDO_TOTAL))*100) AS MORA1
	--from PAGO_VS_GESTION_DIA
	--WHERE DIA_PROCESO = CONVERT(nvarchar,@FECHA, 112)
	--GROUP BY CALIDAD_CARTERA, COD_PERSONAL
	--SOLICITUDES
		 , (
			 Select solicitudes
					From #solicitudes_hr_total As sht
					Where sht.cod_personal = bp.cod_personal
		   ) As solicitudes

	--ESTO ES PARA CREAR LA TABLA TEMPORAL Y JALAR LAS BATERIAS PROMEDIADAS
	--DECLARE @FECHA DATEtime =GETDATE ()
	--SELECT distinct(COD_PERSONAL),value,MAX(convert(datetime,timestamp)) AS FECHA
	--FROM [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS] vs
	--left join temporal..CELULARES_IMEI ci on  vs.deviceid = ci.DES_IMEI
	--where cod_personal is not null  AND CONVERT(CHAR(10),timestamp,121)  between CONVERT(CHAR(10),@FECHA-10,121) and CONVERT(CHAR(10),@FECHA,121)
	--group by CI.COD_PERSONAL,convert(datetime,timestamp),value
	--STORAGE
		 , (
			 Select memory
					From #temp_memory As x
						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
					Where ci.cod_personal = bp.cod_personal
		   ) As storage

		   --TEMPORAL BATERIAS
		   --	IF OBJECT_ID('TEMPDB..#TEMP_BATERIA') IS NOT NULL	
		   --		DROP TABLE #TEMP_BATERIA
		   --select deviceid,AVG(value) AS VALUE
		   --INTO #TEMP_BATERIA
		   --from (
		   --select deviceid,fecha,max(value) as value from(
		   --select deviceid,convert(date,timestamp) as fecha,value 
		   --from [192.168.87.31\SVRMSSQL].MDM.[dbo].[bATTERY_STATUS]
		   --where CONVERT(CHAR(10),convert(date,timestamp),112)  between  CONVERT(CHAR(10),getdate()-3,112) and CONVERT(CHAR(10),getdate()-1,112) 
		   --) a group by a.deviceid, a.fecha, a.value
		   --)b group by deviceid
		   --order by deviceid
		   --METO TODO DESEMBOLSOS (# Y $)
		   --CONSULTA DESEMBOLSOS DE LA TEMPORAL
		 , (
			 Select nro_desemb
					From #temp_desembolso As x
					Where x.cod_personal = bp.cod_personal
		   ) As nro_desemb
		   --CONSULTA DESEMBOLSOS MONTO DE LA TEMPORAL
		 , (
			 Select monto_desem
					From #temp_desembolso As x
					Where x.cod_personal = bp.cod_personal
		   ) As monto_desemb

		   --BATERIAS
		 , (
			 Select bateria
					From #temp_bateria As x
						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
					Where ci.cod_personal = bp.cod_personal
		   ) As bateria

		   --from H_BASE_PRODUCTIVIDAD bp  WHERE COD_PERSONAL IS NOT NULL AND convert(date,bp.fec_hoja_ruta) =convert(date,@FECHA)  
		   --ESTADO DEL EDN
		 , bp.estado As estado

		   --NOTA REGULATORIO
		 , (
			 Select nota
					From t_credito..hr_nota_regulatorio As hnr
					Where hnr.cod_personal = bp.cod_personal
		   ) As nota_regulatorio

		   --NOTA NORMATIVO
		 , (
			 Select nota
					From t_credito..hr_nota_normativo As hnr
					Where hnr.cod_personal = bp.cod_personal
		   ) As nota_normativo

		   --MEGAS
		 , (
			 Select megas
					From #temp_megas As x
						 Left Join temporal..celulares_imei As ci On x.deviceid = ci.des_imei
					Where ci.cod_personal = bp.cod_personal
		   ) As megas

		   --dir_cli
		 , (
			 Select total
					From #dir_cli As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As porc_referenciadas

		   --tel_valido
		 , (
			 Select valido
					From #tel_val As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As telf_validos
		   --Gestor de recuperaciones
		 , gs.can_cli_asig
		 , gs.reg_cli_hr
		 , gs.no_reg_cli_hr
		 , gs.num_pagos
		 , gs.imp_pagos
		 , gs.reg_cli_hr_mes
		 , gs.no_reg_cli_hr_mes
		 , gs.num_pagos_mes
		 , gs.imp_pagos_mes
		 , gs.tot_cli_transado
		 , gs.trs_vigente
		 , gs.trs_vencido
		 , gs.trs_incumplido
		 , gs.num_transados
		 , gs.num_transados_mes
		   --Abogado interno
		 , ab.can_cli_asig
		 , ab.reg_cli_app_jud
		 , ab.no_reg_cli_app_jud
		 , ab.num_pagos
		 , ab.imp_pagos
		 , ab.reg_cli_app_jud_mes
		 , ab.no_reg_cli_app_jud_mes
		 , ab.num_pagos_mes
		 , ab.imp_pagos_mes
		 , ab.tot_cli_transado
		 , ab.trs_vigente
		 , ab.trs_vencido
		 , ab.trs_incumplido
		 , ab.num_transados
		 , ab.num_transados_mes

		   --saldo_capital_actual - en curso
		 , (
			 Select saldo_capital_actual
					From #credito_cliente_actual As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As saldo_capital_en_curso

		   --nro_clientes actual - en curso
		 , (
			 Select n_credito_actual
					From #credito_cliente_actual As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As nro_cliente_en_curso

		   --saldo_capital_mes_anterior 
		 , (
			 Select saldo_capital_anterior
					From #credito_cliente_mes_pasado As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As saldo_capital_mes_anterior

		   --nro_clientes actual - MES ANTERIOR
		 , (
			 Select clientes_anterior
					From #credito_cliente_mes_pasado As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As nro_cliente_mes_anterior

		   --select * from #credito_mora_dia_0
		   --select * from #credito_mora_dia_0_mes_anterior
		   --saldo_capital_dia 0 mes anterior
		 , (
			 Select saldo_capita_al_dia
					From #credito_mora_dia_0_mes_anterior As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As saldo_capital_mora_dia_0_mes_anterior

		   --saldo_capital_dia 0 - en curso
		 , (
			 Select saldo_capital_actual_0
					From #credito_mora_dia_0 As abc
					Where abc.cod_personal = bp.cod_personal
		   ) As saldo_capital_mora_dia_0_mes_actual

		   --EXPEDIENTES
		 , (
			 Select expediente
					From h_expediente As he
					Where he.cod_personal = bp.cod_personal
		   ) As expediente

		   --CUMPLIMIENTO CARTERA>1
		 , (
			 Select Cast(Cast(visita As Float) / Cast(cantidad As Float) As Float) As porcentaje
					From (
						   Select cod_personal
								, Count(cod_credito) As cantidad
								, Sum(cantidad) As visita
								  From (
										 Select cod_personal
											  , cod_credito
											  , Case
													When visitado = 'SI' Then 1
												Else 0
												End As cantidad
												From pago_vs_gestion_dia
												Where dia_proceso = Convert(NVarchar, @Fecha, 112) And rango_mora In('(1)', '(2-3)', '(4-8)', '(9-15)', '(16-30)')
									   ) As a
								  Group By cod_personal
						 ) As b
					Where b.cod_personal = bp.cod_personal
		   ) As cumplimiento_cartera_1

		   --dias de vacaciones disponible
		 , '' As dias_vac_disponible
	--(select disponibles from RENOVACION_HIST aaa
	--where aaa.COD_PERSONAL = BP.COD_PERSONAL) AS dias_vac_disponible

		   From h_base_productividad As bp
				Left Join tmp_rep_gestor_recup As gs On bp.cod_personal = gs.cod_personal And Cast(bp.fec_hoja_ruta As Date) = gs.fec_proceso
				Left Join tmp_rep_abog_interno As ab On bp.cod_personal = ab.cod_personal And Cast(bp.fec_hoja_ruta As Date) = ab.fec_proceso
		   Where bp.cod_personal Is Not Null And Convert(Date, bp.fec_hoja_ruta) = Convert(Date, @Fecha);

	--VALIDAR DESEMBOLSOS
	--select sum(monto_desem) from #TEMP_DESEMBOLSO2 where fecha = '2020-12-29'
	--select sum(monto_desemb) from HR_TOTAL where CONVERT(date,fec_hoja_ruta) = '2020-12-29'

End;
	Set @Retroceso = @Retroceso + 1;
	Print @Fecha;
End;

Answer 2

Otra cosa, para poder optimizar hay que entrar más en detalle en cada una de las consultas. Divide y vencerás.

Por ejemplo esos not in. Aquí tienes una posible mejora.

https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/

Ojo que tienes que analizar el plan de ejecución de ellos, porque no sabemos ni tú motor ni el plan que se ha generado para cada consulta.

También hay muchos distinct y group by e incluso ambos, que no se podrían realizar un poco más óptimos.

Suerte te queda trabajo por delante.