Low Performance Query

Hello!

I have problems with the performance of this query. It takes 4 to 5 minutes to run with a single user. When another user logs in and tries to type information into the table this process takes longer. (from 30 minutes to 1 hour if users are 10 or more)

I know that the Nolock parameter is not a recommended option and that I have many subqueries that make performance worse but I would like to get a general idea of how it could improve performance. I'm not very good at T-SQL.

--------------------------------------------------------------------------------------------------------------

SELECT        H.BODEGA,
                    H.ARTICULO,
                          H.DESCRIPCION,
                          H.LOTE,
                          H.LOTE_PROV,
                          H.FECH_FAB,
                          H.FECH_VEN,
                          H.QTYDISPLOTE,
                          H.QTYRSVDLOTE,
                          H.QTYTOTAL,
                          H.STOCK_KG,
                           H.COD_UNIDAD,
                           H.U_NEGOCIO,
                           ((H.QTYDISPLOTE + H.QTYRSVDLOTE) * H.COSTO_REPO_DOL)as COSTO_REPO_DOL,
                           H.FECING_EMP,
                              DATEDIFF(day,H.FECING_EMP,GETDATE())AS DIAS_EMP,
                           H.FECING_BOD,
                           DATEDIFF(day,H.FECING_BOD,GETDATE()) AS DIAS_BOD,
                           H.fecacc_corectiva,      
                           H.responsable,
                           H.motivo_art,      
                           H.observacion_art,
                        
                          (0)AS stock_invent,
                        (SELECT s.nombre  from sagad_maestro_det s WITH(NOLOCK)
                 where s.codigo = 13  and s.tipo_det = h.tipo) as tipo,

                           h.caning_empresa ,
                           h.embarque,
                           h.crm,
                           ((H.QTYDISPLOTE + H.QTYRSVDLOTE) * H.COSTO_PROM_DOL) AS COSTO_CONTABLE,
                            case ISNULL(len(RTRIM(h.motivo_art)),0) when 0 then 'N' else 'E' end as existe

FROM (
select
       E.bodega,  
         E.articulo,  
         E.descripcion,  
         E.lote,  
         L.LOTE_DEL_PROVEEDOR AS lote_prov,  
         L.FECHA_ENTRADA AS fech_fab,  
         L.FECHA_ENTRADA AS fech_ven,  
         E.qtydisplote,  
         E.qtyrsvdlote,  
         E.qtytotal,  
         E.stock_kg,  
         E.cod_unidad,  
           (select substring(un.DESCRIPCION,1,30) from montana.unidad_negocio un with(nolock)
              where un.unidad_negocio = E.cod_unidad) as u_negocio,

         E.costo_repo_dol,  
        E.FECING_EMP,    
        E.FECING_BOD,  

              m.fecacc_corectiva as fecacc_corectiva,      
              m.responsable as responsable,      
              (select T.Nombre
               FROM  ANT_ATRASOMP T WITH(NOLOCK) WHERE T.TIPO= 'MOTALMST'  and T.idAtrasoMP = m.motivo_art)AS motivo_art,
              m.observacion_art as observacion_art,
             E.caning_empresa,
             m.tipo,
             (select max(lie.EMBARQUE) from montana.det_lin_embarque lie WITH(NOLOCK) where lie.lote = E.LOTE )as embarque,
             (select em.CRM from montana.EMBARQUE em WITH(NOLOCK) where em.EMBARQUE in(select max(lie.EMBARQUE) from montana.det_lin_embarque lie WITH(NOLOCK) where lie.lote = E.LOTE))as crm,
             E.COSTO_PROM_DOL


from SAG_STOCK_ALM E WITH(NOLOCK)
LEFT JOIN MONTANA.LOTE L WITH(NOLOCK) ON (E.LOTE = L.LOTE AND E.ARTICULO =L.ARTICULO)
left join articulo_mov_almacen m WITH(NOLOCK)on ( m.bodega = E.BODEGA and       m.articulo = E.ARTICULO and m.lote = E.LOTE and       m.lote_prov= L.LOTE_DEL_PROVEEDOR )
WHERE (E.qtytotal ) > 0) AS H

---------------------------------------------------------------------------------------------------------------------------------------------

I would very much appreciate your help or comments!
JnavarroMcIT ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Can you get an execution plan for this query and attach the .sqlplan file please.
JnavarroMcIT ConsultantAuthor Commented:
Here it is:

-----------------------------------------------------------------------------------

DECLARE      @return_value int

EXEC      @return_value = [dbo].[usp_carga_stock_sag]
            @usuario = N'LAGAPITO'

SELECT      'Return Value' = @return_value

-----------------------------------------------------------------

Thank you!
plan1.sqlplan
PortletPaulEE Topic AdvisorCommented:
Not sure if that exection plan is for the complete query, I can't study it in detail until much later today, but in the meantime:

My guess is that the "correlated sub-queries" that you use are the cause of performance issues

Try "breaking up" the base query (& don't worry about column order at all during this process)

-- start with just a single table, is this fast? if not, get an execution plan
-- maybe E.qtytotal needs an index?
SELECT
      E.bodega
    , E.articulo
    , E.descripcion
    , E.lote
    , E.qtydisplote
    , E.qtyrsvdlote
    , E.qtytotal
    , E.stock_kg
    , E.cod_unidad
    , E.costo_repo_dol
    , E.FECING_EMP
    , E.FECING_BOD
    , E.caning_empresa
    , E.COSTO_PROM_DOL
FROM SAG_STOCK_ALM E WITH (NOLOCK)
WHERE (E.qtytotal) > 0
;

Open in new window

-- now add one more table, is this fast? if not, get an execution plan
-- maybe indexing is needed on E.LOTE and L.LOTE?
SELECT
      E.bodega
    , E.articulo
    , E.descripcion
    , E.lote
    , E.qtydisplote
    , E.qtyrsvdlote
    , E.qtytotal
    , E.stock_kg
    , E.cod_unidad
    , E.costo_repo_dol
    , E.FECING_EMP
    , E.FECING_BOD
    , E.caning_empresa
    , E.COSTO_PROM_DOL

    , L.LOTE_DEL_PROVEEDOR AS lote_prov
    , L.FECHA_ENTRADA      AS fech_fab
    , L.FECHA_ENTRADA      AS fech_ven
FROM SAG_STOCK_ALM E WITH (NOLOCK)
LEFT JOIN MONTANA.LOTE L WITH (NOLOCK) ON E.LOTE = L.LOTE
      AND E.ARTICULO = L.ARTICULO
WHERE (E.qtytotal) > 0
;

Open in new window

-- now add one more table, is this fast? if not, get an execution plan
-- maybe indexing is needed on:
-- m.bodega   E.BODEGA, m.articulo  E.ARTICULO, m.lote   E.LOTE, m.lote_prov   L.LOTE_DEL_PROVEEDOR
SELECT
      E.bodega
    , E.articulo
    , E.descripcion
    , E.lote
    , E.qtydisplote
    , E.qtyrsvdlote
    , E.qtytotal
    , E.stock_kg
    , E.cod_unidad
    , E.costo_repo_dol
    , E.FECING_EMP
    , E.FECING_BOD
    , E.caning_empresa
    , E.COSTO_PROM_DOL

    , L.LOTE_DEL_PROVEEDOR AS lote_prov
    , L.FECHA_ENTRADA      AS fech_fab
    , L.FECHA_ENTRADA      AS fech_ven

    , m.fecacc_corectiva AS fecacc_corectiva
    , m.responsable      AS responsable
    , m.fecacc_corectiva AS fecacc_corectiva
    , m.responsable      AS responsable
    , m.observacion_art  AS observacion_art
    , m.tipo

FROM SAG_STOCK_ALM E WITH (NOLOCK)
LEFT JOIN MONTANA.LOTE L WITH (NOLOCK) ON E.LOTE = L.LOTE
      AND E.ARTICULO = L.ARTICULO
LEFT JOIN articulo_mov_almacen m WITH (NOLOCK) ON m.bodega = E.BODEGA
      AND m.articulo = E.ARTICULO
      AND m.lote = E.LOTE
      AND m.lote_prov = L.LOTE_DEL_PROVEEDOR
WHERE (E.qtytotal) > 0
;

Open in new window


Now the "correlated sub-queries"

--then add this, is it fast or slow
    , (SELECT
            SUBSTRING(un.DESCRIPCION, 1, 30)
          FROM montana.unidad_negocio un WITH (NOLOCK)
          WHERE un.unidad_negocio = E.cod_unidad)
          AS u_negocio

Open in new window

-- then add this, is it fast or slow
    , (SELECT
            T.Nombre
          FROM ANT_ATRASOMP T WITH (NOLOCK)
          WHERE T.TIPO = 'MOTALMST'
          AND T.idAtrasoMP = m.motivo_art)
          AS motivo_art

Open in new window

-- then add this, is it fast or slow
    , (SELECT
            MAX(lie.EMBARQUE)
          FROM montana.det_lin_embarque lie WITH (NOLOCK)
          WHERE lie.lote = E.LOTE)
          AS embarque

Open in new window

-- then add this, is it fast or slow
    , (SELECT
            em.CRM
          FROM montana.EMBARQUE em WITH (NOLOCK)
          WHERE em.EMBARQUE IN (SELECT
            MAX(lie.EMBARQUE)
          FROM montana.det_lin_embarque lie WITH (NOLOCK)
          WHERE lie.lote = E.LOTE))
          AS crm

Open in new window


It looks like all of these sub-queries could return more than one row, and if ANY of these do that, your overall query will stop with an error.

You need to re-consider how you do these sub-queries in my view
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JnavarroMcIT ConsultantAuthor Commented:
I realized I had the wrong plan. I apologize for the inconvenience.

This is the right plan.
plan2.sqlplan
PortletPaulEE Topic AdvisorCommented:
Thanks, it didn't look right, BUT:

1. I cannot look in detail at that plan until much later
2. I have suggested a series of smaller queries, and to be honest you almost certainly need to follow this advice anyway.

by the way: Have you looked at what indexes exist on these tables?
and: If there are missing indexes can you create new indexes?
The execution plan lists some missing indexes, fixing these will improve performance.
            <MissingIndexes>
              <MissingIndexGroup Impact="24.1102">
                <MissingIndex Database="[EXACFOOD]" Schema="[MONTANA]" Table="[TRANSACCION_INV]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[NATURALEZA]" ColumnId="14" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[FECHA_HORA_TRANSAC]" ColumnId="3" />
                    <Column Name="[ARTICULO]" ColumnId="7" />
                    <Column Name="[LOTE]" ColumnId="10" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="24.4154">
                <MissingIndex Database="[EXACFOOD]" Schema="[MONTANA]" Table="[TRANSACCION_INV]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[NATURALEZA]" ColumnId="14" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[FECHA_HORA_TRANSAC]" ColumnId="3" />
                    <Column Name="[ARTICULO]" ColumnId="7" />
                    <Column Name="[BODEGA]" ColumnId="8" />
                    <Column Name="[LOTE]" ColumnId="10" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>

Open in new window


The remaining areas of complexity arise from the "correlated sub-queries" and these are often the cause of slowness. These too are affected by indexes, and there are alternatives (such as using cross or outer apply) but we really need to know IF and WHICH of those sub-queries is causing poor performance.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm second Paul about the need to rewrite your query to avoid the subqueries. Here's what I came after rewriting your query (hopefully I didn't forget nothing):
;WITH CTE_Embarque
AS
	(SELECT lote, max(EMBARQUE) MaxEmbarque 
	FROM montana.det_lin_embarque
	GROUP BY lote)
SELECT E.bodega,   
    E.articulo,   
    E.descripcion,   
    E.lote,   
    L.LOTE_DEL_PROVEEDOR AS lote_prov,   
    L.FECHA_ENTRADA AS fech_fab,   
    L.FECHA_ENTRADA AS fech_ven,   
    E.qtydisplote,   
    E.qtyrsvdlote,   
    E.qtytotal,   
    E.stock_kg,   
    E.cod_unidad,   
	substring(un.DESCRIPCION,1,30) as u_negocio,
	((E.QTYDISPLOTE + E.QTYRSVDLOTE) * E.COSTO_REPO_DOL) as COSTO_REPO_DOL,
	E.FECING_EMP,    
	DATEDIFF(day,E.FECING_EMP,GETDATE())AS DIAS_EMP,
	E.FECING_BOD,
	DATEDIFF(day,E.FECING_BOD,GETDATE()) AS DIAS_BOD,
	m.fecacc_corectiva as fecacc_corectiva,      
    m.responsable as responsable,      
    T.Nombre AS motivo_art,
    m.observacion_art as observacion_art,
	0 AS stock_invent,
	s.nombre as tipo,
    E.caning_empresa,
    lie.MaxEmbarque as embarque,
    em.CRM as crm,
	((E.QTYDISPLOTE + E.QTYRSVDLOTE) * E.COSTO_PROM_DOL) AS COSTO_CONTABLE,
	case ISNULL(len(RTRIM(T.Nombre)),0) 
		when 0 then 'N' 
		else 'E' end 
	as existe
FROM SAG_STOCK_ALM E WITH(NOLOCK) 
	INNER JOIN montana.unidad_negocio un with(nolock) ON un.unidad_negocio = E.cod_unidad
	INNER JOIN ANT_ATRASOMP T WITH(NOLOCK) ON T.TIPO= 'MOTALMST'  and T.idAtrasoMP = m.motivo_art
	INNER JOIN cte_embarque lie ON lie.lote = E.LOTE
	INNER JOIN montana.EMBARQUE em WITH(NOLOCK) on em.EMBARQUE = lie.MaxEmbarque
	LEFT JOIN MONTANA.LOTE L WITH(NOLOCK) ON E.LOTE = L.LOTE AND E.ARTICULO =L.ARTICULO
	LEFT JOIN articulo_mov_almacen m WITH(NOLOCK) ON m.bodega = E.BODEGA and m.articulo = E.ARTICULO and m.lote = E.LOTE and m.lote_prov= L.LOTE_DEL_PROVEEDOR
	INNER JOIN sagad_maestro_det s WITH(NOLOCK) ON s.codigo = 13  and s.tipo_det = m.tipo
WHERE E.qtytotal > 0

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JnavarroMcIT ConsultantAuthor Commented:
Dear Vitor,

I have this mistake. I have checked the left join fields and the columns exist in the tables.

Msg 4104, Level 16, State 1, Line 40
The multi-part identifier "m.motivo_art" could not be bound.


I'm getting a better understanding of how to change the subquery's.
I hope you can help me
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think it's the order of the JOINs so I've moved that line to the bottom of the JOINs.
Please check now:
;WITH CTE_Embarque
AS
	(SELECT lote, max(EMBARQUE) MaxEmbarque 
	FROM montana.det_lin_embarque
	GROUP BY lote)
SELECT E.bodega,   
    E.articulo,   
    E.descripcion,   
    E.lote,   
    L.LOTE_DEL_PROVEEDOR AS lote_prov,   
    L.FECHA_ENTRADA AS fech_fab,   
    L.FECHA_ENTRADA AS fech_ven,   
    E.qtydisplote,   
    E.qtyrsvdlote,   
    E.qtytotal,   
    E.stock_kg,   
    E.cod_unidad,   
	substring(un.DESCRIPCION,1,30) as u_negocio,
	((E.QTYDISPLOTE + E.QTYRSVDLOTE) * E.COSTO_REPO_DOL) as COSTO_REPO_DOL,
	E.FECING_EMP,    
	DATEDIFF(day,E.FECING_EMP,GETDATE())AS DIAS_EMP,
	E.FECING_BOD,
	DATEDIFF(day,E.FECING_BOD,GETDATE()) AS DIAS_BOD,
	m.fecacc_corectiva as fecacc_corectiva,      
    m.responsable as responsable,      
    T.Nombre AS motivo_art,
    m.observacion_art as observacion_art,
	0 AS stock_invent,
	s.nombre as tipo,
    E.caning_empresa,
    lie.MaxEmbarque as embarque,
    em.CRM as crm,
	((E.QTYDISPLOTE + E.QTYRSVDLOTE) * E.COSTO_PROM_DOL) AS COSTO_CONTABLE,
	case ISNULL(len(RTRIM(T.Nombre)),0) 
		when 0 then 'N' 
		else 'E' end 
	as existe
FROM SAG_STOCK_ALM E WITH(NOLOCK) 
	INNER JOIN montana.unidad_negocio un with(nolock) ON un.unidad_negocio = E.cod_unidad
	INNER JOIN cte_embarque lie ON lie.lote = E.LOTE
	INNER JOIN montana.EMBARQUE em WITH(NOLOCK) on em.EMBARQUE = lie.MaxEmbarque
	LEFT JOIN MONTANA.LOTE L WITH(NOLOCK) ON E.LOTE = L.LOTE AND E.ARTICULO =L.ARTICULO
	LEFT JOIN articulo_mov_almacen m WITH(NOLOCK) ON m.bodega = E.BODEGA and m.articulo = E.ARTICULO and m.lote = E.LOTE and m.lote_prov= L.LOTE_DEL_PROVEEDOR
	INNER JOIN ANT_ATRASOMP T WITH(NOLOCK) ON T.TIPO= 'MOTALMST' and T.idAtrasoMP = m.motivo_art
	INNER JOIN sagad_maestro_det s WITH(NOLOCK) ON s.codigo = 13  and s.tipo_det = m.tipo
WHERE E.qtytotal > 0

Open in new window

JnavarroMcIT ConsultantAuthor Commented:
Dear Vitor,

The new query takes 3 min and has a different result. (377 rows)

The old query takes 20 to 25 seconds average (947335 rows) but I would like to optimize it to the maximum taking into account the recommendations you have given me. I'm working on replacing the values and getting the same result.

Old Query:

  1. SELECT E.BODEGA,
  2.                                 E.ARTICULO,
  3.                                 AR.COD_UNIDAD,
  4.                                 SUBSTRING(AR.DESCRIPCION,1,254) as DESCRIPCION,
  5.                                 E.LOTE,
  6.                                 sum(E.CANT_DISPONIBLE) AS QTYDISPLOTE,
  7.                                 sum(E.CANT_RESERVADA ) AS QTYRSVDLOTE,
  8.                                 sum(E.CANT_RESERVADA ) + sum(E.CANT_DISPONIBLE) as qtytotal,
  9.                                 SUM(ISNULL(((ISNULL(E.CANT_DISPONIBLE,0) + ISNULL(E.CANT_RESERVADA,0)) * ISNULL(AR.FACTOR_EMPAQUE,0)),0)) AS STOCK_KG,
  10.                                 MAX(ISNULL(AR.COSTO_REPO_DOL,0)) AS COSTO_REPO_DOL,
  11.                                 MAX(ISNULL(AR.COSTO_PROM_DOL,0)) as COSTO_PROM_DOL,
  12.                                 (select MIN(IV.FECHA_HORA_TRANSAC) from MONTANA.TRANSACCION_INV IV WITH(NOLOCK)  
  13.                                 where IV.articulo = E.ARTICULO  and IV.lote = E.LOTE and IV.naturaleza = 'E') AS FECING_EMP,    
  14.                       (select MAX(IV.FECHA_HORA_TRANSAC) from MONTANA.TRANSACCION_INV IV WITH(NOLOCK)  
  15.                       where IV.articulo = E.ARTICULO  and IV.lote = E.LOTE and IV.BODEGA = E.BODEGA AND IV.naturaleza = 'E') AS FECING_BOD,  
  16.                                 (select sum(cantidad) from MONTANA.TRANSACCION_INV iv WITH(NOLOCK)  
  17.                                 where IV.articulo = E.ARTICULO  and IV.lote = E.LOTE and IV.naturaleza = 'E'  and AJUSTE_CONFIG = '~OO~') as caning_empresa,
  18.                                 getdate()
  19.                        FROM MONTANA.EXISTENCIA_LOTE E with (nolock)  
  20.                        INNER JOIN MONTANA.ARTICULO AR with (nolock)  ON (E.ARTICULO = AR.ARTICULO )
  21.                        GROUP BY
  22.                                    E.BODEGA,
  23.                                    E.ARTICULO,
  24.                                    AR.DESCRIPCION,
  25.                                    E.LOTE,
  26.                                    AR.COD_UNIDAD
OldPlan.sqlplan
NewPlan.sqlplan
Vitor MontalvãoMSSQL Senior EngineerCommented:
I've only used INNER JOIN and that's limit the number of returned rows.
Unfortunately I don't have access to your server so I can't test the query. Just gave you an example how to change it. You may need to make some changes to work as you want.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, your OLD QUERY isn't the same as the one you posted when you opened this question.
Why's that?
JnavarroMcIT ConsultantAuthor Commented:
I thought it was an equal Query and I should get similar results. I'm going to work on getting an equal result and I'll post it here! :)

Thank you very much for your time and patience. I'm not very good at this subject but I want to learn.
Vitor MontalvãoMSSQL Senior EngineerCommented:
And review the indexes for your table EXISTENCIA_LOTE as I think it has none.
JnavarroMcIT ConsultantAuthor Commented:
Hello!

I try this! I create 2 views: (I'm still not sure how to create temporary tables)
 
create view usp_carga_stock_sag_view
as
select lote,
sum(CANT_DISPONIBLE) AS QTYDISPLOTE,
					   sum(CANT_RESERVADA ) AS QTYRSVDLOTE,
					   sum(CANT_RESERVADA ) + sum(CANT_DISPONIBLE) as qtytotal
from MONTANA.EXISTENCIA_LOTE  
group by MONTANA.EXISTENCIA_LOTE.LOTE  
go

Open in new window


create view usp_carga_stock_sag_viewv2
as
select articulo,FACTOR_EMPAQUE,
MAX(ISNULL(COSTO_REPO_DOL,0)) AS COSTO_REPO_DOL,
					  MAX(ISNULL(COSTO_PROM_DOL,0)) AS COSTO_PROM_DOL
					   
from MONTANA.ARTICULO  
group by MONTANA.ARTICULO.ARTICULO  ,
MONTANA.ARTICULO.FACTOR_EMPAQUE 
go

Open in new window


I've been able to redesign the query this way:

		SELECT E.BODEGA,
		
					   E.ARTICULO,
					   AR.COD_UNIDAD,
					   SUBSTRING(AR.DESCRIPCION,1,254) as DESCRIPCION,
					   E.LOTE,
					   V.QTYDISPLOTE AS QTYDISPLOTE,
					   V.QTYRSVDLOTE AS QTYRSVDLOTE,
					   V.qtytotal as qtytotal,
					   SUM(ISNULL(((ISNULL(E.CANT_DISPONIBLE,0) + ISNULL(E.CANT_RESERVADA,0)) * ISNULL(AR.FACTOR_EMPAQUE,0)),0)) AS STOCK_KG,
					   V2.COSTO_REPO_DOL AS COSTO_REPO_DOL,
					   V2.COSTO_PROM_DOL as COSTO_PROM_DOL,
					   (SELECT MAX(IV.FECHA_HORA_TRANSAC) WHERE IV.naturaleza = 'E') as FECING_BOD,
					   (SELECT MIN(IV.FECHA_HORA_TRANSAC) WHERE IV.naturaleza = 'E') AS FECING_EMP,  
                       (select sum(cantidad) where IV.naturaleza = 'E'  and AJUSTE_CONFIG = '~OO~') as caning_empresa,
					   getdate()
				FROM MONTANA.EXISTENCIA_LOTE E   
				INNER JOIN MONTANA.ARTICULO AR  ON (E.ARTICULO = AR.ARTICULO )
				INNER JOIN usp_carga_stock_sag_viewv2 V2 ON (V2.ARTICULO = AR.ARTICULO )
				INNER JOIN usp_carga_stock_sag_view V ON (V.LOTE = E.LOTE )
				LEFT JOIN MONTANA.TRANSACCION_INV IV ON IV.articulo = E.ARTICULO  and IV.lote = E.LOTE and IV.BODEGA = E.BODEGA and IV.naturaleza = 'E' 
					 
			
				GROUP BY 
						E.BODEGA,
						E.ARTICULO,
						AR.DESCRIPCION,
						E.LOTE,
						AR.COD_UNIDAD,
						iv.NATURALEZA,
						V.QTYDISPLOTE,
						V.QTYRSVDLOTE,
						v.qtytotal,
						V2.COSTO_PROM_DOL,
						V2.COSTO_REPO_DOL,
						IV.AJUSTE_CONFIG

Open in new window



but I have a problem with this line of code: (AJUSTE_CONFIG = '~OO~' makes the performance of the query worse.)

(select sum(cantidad) where IV.naturaleza = 'E'  and AJUSTE_CONFIG = '~OO~' ) as caning_empresa,

Open in new window


in the join I can only declare it this way because if I add the AJUSTE_CONFIG = '~OOO~' parameter the data is not the same.

 
LEFT JOIN MONTANA.TRANSACCION_INV IV ON IV.articulo = E.ARTICULO  and IV.lote = E.LOTE and IV.BODEGA = E.BODEGA and IV.naturaleza = 'E'

Open in new window



I hope you can continue to help me or correct me, please.
planv3.sqlplan
Vitor MontalvãoMSSQL Senior EngineerCommented:
You keep changing your query and I'm helping on other questions as well, so every time you change the query I need to loose time reviewing it and I don't want to let others down because I'm loosing more time with this question than expected.
Your query now returns more than 1 Million rows and it's complaining also on the lack of indexes in the ARTICULO table.
Review the query again as I think isn't returning what you want.
JnavarroMcIT ConsultantAuthor Commented:
Thank you for your patience. I'm just trying to learn.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.