0

This is what im doing on C# :

        public static decimal ObterSaldoEstoqueEntrarProdutosFamilia(this IRepositorio<SaldoEstoqueProduto> saldoEstoqueRepositorio, string empresaUsuario, Int64 tipoEstoqueUsuario, string filial, string familia)
        {

            //TODO: Trocar por consulta pelo LINQ quando os objetos de programação estiverem criados no C#
            decimal resultado = 0;

            try
            {
                string connString = saldoEstoqueRepositorio.StringConexao();
                SqlConnection conexaoSql = new SqlConnection(connString);
                StringBuilder queryItens = new StringBuilder();

                queryItens.Append("SELECT isnull(SUM(T2.ProTrfQtd - T2.ProTrfCol),0) as Saldo ");
                queryItens.Append("FROM EST250 T1 (NOLOCK) ");
                queryItens.Append("INNER JOIN EST2501 T2 (NOLOCK) ON T2.EmpCod = T1.EmpCod AND T2.EstTip = T1.EstTip AND T2.ProTrfNum = T1.ProTrfNum AND T2.FilOriCod = T1.FilOriCod AND T2.FilDstCod = T1.FilDstCod ");
                queryItens.Append("LEFT JOIN EST2503 T3 (NOLOCK) ON T3.EmpCod = T1.EmpCod AND T3.EstTip = T1.EstTip AND T3.ProTrfNum = T1.ProTrfNum AND T3.FilOriCod = T1.FilOriCod AND T3.FilDstCod = T1.FilDstCod ");
                queryItens.Append("LEFT JOIN NTF006 T4 (NOLOCK) ON t4.EmpCod = t3.EmpCod and t4.FisOpe = 'E' and t4.FilCod = t3.FilOriCod and t4.FisEspCod = t3.ProTrfEspC and t4.FisSerCod = t3.ProTrfSerC and t4.FisEntCod = t3.ProTrfFisE and t4.FisCod = t3.ProTrfFisC and t4.NopCod = t3.ProTrfNopC ");
                queryItens.Append("LEFT JOIN EST020 T5 (NOLOCK) ON T5.EmpCod = t2.EmpCod and t5.EstTip = t2.EstTip and t5.ProCod = t2.ProCod ");
                queryItens.Append("LEFT JOIN EST023 T6 (NOLOCK) ON T6.EmpCod = t2.EmpCod and t5.EstTip = t2.EstTip and t6.ProFamCod = t5.ProFmlCod ");
                queryItens.Append("WHERE T1.EmpCod = @EmpCod AND T1.EstTip = @EstTip AND T1.FilDstCod = @FilCod AND");
                queryItens.Append("    ( ");
                queryItens.Append("        T6.ProFamCod like @ProFamCodLike OR T6.ProFamCod = @ProFamCod ");
                queryItens.Append("    ) ");
                queryItens.Append("AND T1.ProTrfOdfC = 0 AND T2.ProTrfQtd > T2.ProTrfCol AND ");
                queryItens.Append("    ( ");
                queryItens.Append("        (T1.ProTrfSta = 1 OR T1.ProTrfSta = 2 OR T1.ProTrfSta = 4 OR T1.ProTrfSta = 7 OR T1.ProTrfSta = 9) ");
                queryItens.Append("        OR (T1.ProTrfSta = 6 AND t4.FisSta in (1, 2, 3, 4, 5, 6, 12)) "); //Não considera o saldo a entrar de notas que estão em conclusão pendente, pois esta saldo já está contando no lógico,ou das programações Em recebimento que não possuem nota relacionada
                queryItens.Append("    ) ");

                conexaoSql.Open();
                SqlCommand comando = new SqlCommand(queryItens.ToString(), conexaoSql);

                comando.Parameters.AddWithValue("@EmpCod", empresaUsuario);
                comando.Parameters.AddWithValue("@EstTip", tipoEstoqueUsuario);
                comando.Parameters.AddWithValue("@ProFamCod", familia);
                comando.Parameters.AddWithValue("@ProFamCodLike", familia + ".%" );
                comando.Parameters.AddWithValue("@FilCod", filial);

                resultado = Convert.ToDecimal(comando.ExecuteScalar());
                conexaoSql.Close();
            }
            catch (Exception)
            {

            }

            return resultado;
        }

The comando.ExecuteScalar() takes 4 seconds to execute. But if i get the sql generated and execute on SQL Server with same parameters it takes just 1 second.

Someone know whats can be? There is a way to solve it?

Vinicius Botelho Bizarri
  • 73
  • 1
  • 10
  • 1
    How do you know it takes 4 seconds? Are you using Sql Profiler? I dont see any timing code... –  Sep 01 '14 at 13:35
  • 1
    Without going to the details of your SQL code, you're catching an Exception with an empty handling block. That's a very bad design. – kevin Sep 01 '14 at 13:38
  • 1
    what is this query actually doing? seems quite extreme? It could be the way you are appending the 'query' string which is taking the time - in your SQL i suppose you've already got the query built? where as this you seem to be creating THEN executing the command? (I also can't actually see where you are actually executing the command in the code above (unless i'm mistaken)? – jbutler483 Sep 01 '14 at 13:39
  • 1
    Take a look at this similar question: http://stackoverflow.com/questions/16930295/query-times-out-when-run-from-ado-net-but-runs-fine-in-query-analyer/16934393#16934393 – Joe Sep 01 '14 at 13:42
  • 2
    @ojf I'm debugging and timing with my cellphone... – Vinicius Botelho Bizarri Sep 01 '14 at 13:47
  • @kayson I know that, im going to implement when its done, for now its just a test. – Vinicius Botelho Bizarri Sep 01 '14 at 13:48
  • 3
    Could it be SQL result caching? The query has already run once from the code so the results / pages are cached in memory ready for the run you are doing in SQL? – ajg Sep 01 '14 at 13:54
  • 1
    @ViniciusB.Bizarri; This code **time = timeToCreateString+timeToExecute;** **SQL time = timeToExecute;** //since this query in SQL has already been written 'fully'. See what i mean now? – jbutler483 Sep 01 '14 at 13:58
  • 1
    Another thing worth pointing out - you're using a StringBuilder to concatenate string which are already defined in compile time. Thus you can just use `"` and `+`+ to build the string. I'd suggest you copy the SQL code to a ConsoleApp project then execute it 4 or 5 times. It eliminates possible problem in the ASP.NET pipeline. If the first run is particularly slow than it's most likely SQL caching. – kevin Sep 01 '14 at 13:58
  • @ajg I understood your point, It could be. But there's a way to i erase te chaching to test if it takes more time too? – Vinicius Botelho Bizarri Sep 01 '14 at 13:58
  • 1
    as @kayson says you need to run it multiple times to prove anything really. This answer has info to clear the cache between runs - http://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache – ajg Sep 01 '14 at 14:01
  • 2
    You should time just the `resultado = Convert.ToDecimal(comando.ExecuteScalar());` line, since `conexaoSql.Open();` (opening the connection) can take some time if the connection is not in the pool. – Wagner DosAnjos Sep 01 '14 at 14:05
  • @jbutler483 but i just timing this execution `resultado = Convert.ToDecimal(comando.ExecuteScalar());` with debug, even this way he has to create string and etc? – Vinicius Botelho Bizarri Sep 01 '14 at 14:07
  • @wdosanjos I'm doing exactly this! – Vinicius Botelho Bizarri Sep 01 '14 at 14:07
  • @Joe using `.add` instead `.addWithValue` don't changed the time. – Vinicius Botelho Bizarri Sep 01 '14 at 14:12
  • 1
    using your 'cell phone' wouldn't exactly be the best way to measure this; but it does seem to be a slight jump. How many records do you have currently? But still, your code will NEVER be as fast as running it straight off the dbms, because that's what your sending your data to! have you tried other queries and found/had similar results? – jbutler483 Sep 01 '14 at 14:12
  • @ViniciusB.Bizarri - look at your ANSI_NULL settings – Joe Sep 01 '14 at 14:20
  • Yes, i know that's not the best way to measure, but the diference between times is very noticeable. Yes, i have many other queries and they're similiar. This problem occured more than once, but its rare. – Vinicius Botelho Bizarri Sep 01 '14 at 14:22

1 Answers1

0

I resolved my problem changing the way i add parameters to my query!

I was doing like this:

   comando.Parameters.AddWithValue("@EmpCod", empresaUsuario);
   comando.Parameters.AddWithValue("@EstTip", tipoEstoqueUsuario);
   comando.Parameters.AddWithValue("@ProFamCod", familia);
   comando.Parameters.AddWithValue("@ProFamCodLike", familia + ".%" );
   comando.Parameters.AddWithValue("@FilCod", filial);

Then i changed to this:

   SqlParameter sqlParmInsLeitura1 = new SqlParameter("EmpCod", SqlDbType.VarChar);
   sqlParmInsLeitura1.Value = (object)empresaUsuario.Trim();
   SqlParameter sqlParmInsLeitura2 = new SqlParameter("EstTip", SqlDbType.BigInt);
   sqlParmInsLeitura2.Value = (object)tipoEstoqueUsuario;
   SqlParameter sqlParmInsLeitura3 = new SqlParameter("ProFamCod", SqlDbType.VarChar);
   sqlParmInsLeitura3.Value = (object)familia.Trim();
   SqlParameter sqlParmInsLeitura4 = new SqlParameter("ProFamCodLike", SqlDbType.VarChar);
   sqlParmInsLeitura4.Value = (object)familia.Trim() + ".%";
   SqlParameter sqlParmInsLeitura5 = new SqlParameter("FilCod", SqlDbType.VarChar);
   sqlParmInsLeitura5.Value = (object)filial.Trim();

   comando.Parameters.Add(sqlParmInsLeitura1);
   comando.Parameters.Add(sqlParmInsLeitura2);
   comando.Parameters.Add(sqlParmInsLeitura3);
   comando.Parameters.Add(sqlParmInsLeitura4);
   comando.Parameters.Add(sqlParmInsLeitura5);

Now it executes in 1 sec. instead of 4 sec.

Thanks for all!

Vinicius Botelho Bizarri
  • 73
  • 1
  • 10