I have a query that spend 30 seconds in sql server, and I need make the consult 7 times changing the value in one variable everytime, so 7 consults spend 3 minutes 40 seconds.

The 7 times I do it with a foreach in C# and add the result to a List<> but is imposible spend less of 3minutes, so I need the same results just doing 1 consult in SQL with the 7 variables at the same time.

This is the code:

select 
 pro,
 invproducto.v_nombre_pro,
 sum(pieza) as pieza,
 Sum(kilos)  as kilos,
 COSPZA=  min(iSnULL(invproducto.n_costolabbdg_pro,0) ), 
 IsNull(invproducto.c_validarpeso_pro,'')  as c_validarpeso_pro,
 invproducto.c_unidadcosteo_uni,
 c_codigo_uni =  invproducto.c_codigo_uni,invproducto.c_unidadvtainv_uni
From invproducto (NOLOCK),
 (
 SELECT 
   'M' AS ORD,
   fecha,mov,
   TIPO,REPAR,
   '' AS EMP,
   PRO,
   '' AS SERIE,
   '' AS CODFACREM,
   '' AS FACREM,
   
   
   PIEZA= (cASE when IsNull(c_ajustexuni_mov,'')  ='S' and IsNull(c_validarpeso_pro,'') ='S' and C_CODIGO_UNI = '02' 
         Then n_cantidadxuni_mov
       when IsNull(c_ajustexuni_mov,'')  ='S' and IsNull(c_validarpeso_pro,'')<>'S' and C_CODIGO_UNI =  c_unidadcosteo_uni 
         Then n_cantidad_mov
       when IsNull(c_ajustexuni_mov,'')  ='S' and IsNull(c_validarpeso_pro,'')<>'S' and C_CODIGO_UNI <> c_unidadcosteo_uni 
         Then 0
       wHEN IsNull(c_validarpeso_pro,'') <>'S' 
         THEN n_cantidad_mov
       wHEN IsNull(c_validarpeso_pro,'') ='S' and C_CODIGO_UNI='02' 
         THEN n_cantidadxuni_mov
       ELSE 
        (SELECT parte1.n_cantidadxuni_mov * (S2.N_CANTIDAD_EQU / S2.N_CANTIDADEQUIVALENTE_EQU)
         FROM SOIEQUIVALENCIA S2 (NOLOCK)
         wHERE  S2.C_CODIGO_EQU=
                (SELECT mAX(S1.C_CODIGO_EQU) FROM SOIEQUIVALENCIA S1 (NOLOCK)
                wHERE parte1.C_CODIGO_UNI = S1.C_CODIGO_UNI
                aND  S1.C_CODIGOCONV_UNI  = '02'
                AND  S1.C_CODIGO_PRO  = parte1.c_codigo_pro
                )
        ) 
     END),
   
   KILOS = (cASE when IsNull(c_ajustexuni_mov,'')  ='S'  and C_CODIGO_UNI= '03'  and c_unidadcosteo_uni <> '03' 
         Then n_cantidadxuni_mov 
       wHEN (c_unidadcosteo_uni='03' or IsNull(c_validarpeso_pro,'') ='S') 
         THEN n_cantidad_mov
       ELSE 
        (SELECT parte1.n_cantidad_mov * (S2.N_PESO_EQU / S2.N_CANTIDADEQUIVALENTE_EQU ) 
         FROM SOIEQUIVALENCIA S2 (NOLOCK)
         wHERE  S2.C_CODIGO_EQU=
                (SELECT mAX(S1.C_CODIGO_EQU) FROM SOIEQUIVALENCIA S1 (NOLOCK)
                wHERE parte1.c_unidadcosteo_uni = S1.C_CODIGO_UNI
                aND  S1.C_CODIGOCONV_UNI   = '03'   
                AND  S1.C_CODIGO_PRO    = parte1.c_codigo_pro
                )
        ) 
     END)
     
  FROM 
   (
   Select 
    m.c_codigo_mov as mov,
    c_tipodoc_mov as tipo,
    e.c_codigo_tmv as des,
    e.d_documento_ent as fecha,
    m.c_codigo_pro AS PRO,
    m.n_exiant_mov as exis,
    pro.c_unidadcosteo_uni as unicos,
    iSnULL(M.C_REPARTOACTIVO_EMP,'') AS REPAR,
     M.C_CODIGO_UNI AS UNI,
    m.n_cantidadxuni_mov AS CAN,
    m.n_cantidad_mov as peso,
    IsNull(pro.c_validarpeso_pro,'') as tp,
    /* variables nuevas para nuevo calculo */
    c_validarpeso_pro = IsNull(pro.c_validarpeso_pro,''),
    m.c_ajustexuni_mov,
    m.c_codigo_uni,
    pro.c_unidadCosteo_uni,
    m.n_cantidadxuni_mov,
    m.c_codigo_pro,
    m.n_cantidad_mov
      From  invmovimiento m  (NOLOCK),
     invproducto pro  (NOLOCK),
     inventrada    e  (NOLOCK)

   Where m.c_codigo_pro=pro.c_codigo_pro 
   and  m.c_tipodoc_mov='E' 
   and  e.c_codigo_ent=m.c_codigo_ent
   and  m.c_codigo_alm in ('16')  -- I NEED AN ARRAY HERE, like this ('01','02','03','04','13','15','16',)
   and  (m.c_codigo_pro >= '00000000'
   and  m.c_codigo_pro <= '99999999')
   and  e.d_documento_ent < ={ts '2015-09-14 00:00:00.000'}
   and  e.c_activo_ent = '1'  
   and pro.c_codigo_prv = '0010'

   Union
   
   Select 
    m.c_codigo_mov as mov,
    c_tipodoc_mov as tipo,
    l.c_codigo_tmv as des,
    l.d_documento_sal as fecha,
    m.c_codigo_pro AS PRO,
    m.n_exiant_mov as exis,
    pro.c_unidadcosteo_uni as unicos,
    iSnULL(M.C_REPARTOACTIVO_EMP,'') AS REPAR,
    M.C_CODIGO_UNI AS UNI,
    m.n_cantidadxuni_mov AS CAN,
    m.n_cantidad_mov as peso,
    IsNull(pro.c_validarpeso_pro,'') as tp,
    /* variables nuevas para nuevo calculo */
    c_validarpeso_pro = IsNull(pro.c_validarpeso_pro,''),
    m.c_ajustexuni_mov,
    m.c_codigo_uni,
    pro.c_unidadCosteo_uni,
    m.n_cantidadxuni_mov,
    m.c_codigo_pro,
    m.n_cantidad_mov
   From invmovimiento m  (NOLOCK),
   invsalida l (NOLOCK),
   invproducto pro  (NOLOCK) 
   
   Where m.c_codigo_pro=pro.c_codigo_pro 
   and  m.c_tipodoc_mov='S' 
   and  l.c_codigo_sal=m.c_codigo_sal
   and  m.c_codigo_alm in ('16')    -- I NEED AN ARRAY HERE, like this ('01','02','03','04','13','15','16',)
   and  (m.c_codigo_pro >= '00000000'
   and  m.c_codigo_pro <= '99999999')
   and  l.d_documento_sal < ={ts '2015-09-14 00:00:00.000'}
   and pro.c_codigo_prv = '0010'  
   
   ) AS PARTE1
 )two
where two.pro=invproducto.c_codigo_pro and invproducto.c_activo_pro='1'
group by pro,invproducto.v_nombre_pro,invproducto.c_validarpeso_pro,invproducto.c_unidadcosteo_uni,c_codigo_uni,invproducto.c_unidadvtainv_uni
ORDER BY PRO

The result is this:

And I need this result:

• Edited by Juan Pelototas 13 hours 6 minutes ago

How can we execute this?!?

will you open your server to public use and sent us the connection string? probably not :-)

If you need help regarding a query then we need to reproduce the issue in our environment. For this we need you to post DDL+DML: queries to create the relevant tables and to insert the sample data which we discuss.

Maybie can be testes with the script with table names, what you think?

First of all: arrays are a concept of procedural language. In T-SQL we use sets. So a solution to your problem is a simple JOIN to a table (variable). E.g.

DECLARE @Include TABLE ( ID VARCHAR(255) );

INSERT INTO @Include 
VALUES ( '01' ), ( '02' ), ( '03' ), ( '04' ), ( '13' ), ( '15' ) , ( '16' );


SELECT *
FROM yourTablesOrSubqueries
  INNER JOIN @Include I ON I.ID =  m.c_codigo_alm 
WHERE -- the rest
;

As you have problems with performance: Why do you store/use VARCHAR numbers like '16' instead of real INT's? Check the involved table. Maybe you have an unnecessary implicit cast.

Then, why do you need a UNION? Is it really necessary? Does one row in invmovimiento can have a match in invsalida and inventrada at the same time? When no, then you. can JOIN both and you don't need UNION.

But the real cause for the performance problem are imho the subqueries for pieze and kilos. I would start using a temp table which stores the result of

SELECT	S1.C_CODIGO_PRO,
		S1.C_CODIGOCONV_UNI, 
		S1.C_CODIGO_UNI,
		mAX(S1.C_CODIGO_EQU)
FROM	SOIEQUIVALENCIA S1
WHERE	S1.C_CODIGOCONV_UNI IN ( '02',  '03' )
GROUP BY S1.C_CODIGO_PRO, 
	S1.C_CODIGOCONV_UNI,
	S1.C_CODIGO_UNI;

and JOIN into your queries.

Whichc "table names" ?!?

We dont have your database! and maybe in my database the table named invproducto includes only one column named ID, which is a bit type?!?! More likely is that we do not have a table named invproducto and we will get an error message.

Invalid object name 'invproducto'.

I recommend you to post DDL+DML for more accurate help