c# – SQL query taking too long and returning a Time Out error

Question:

I'm trying to perform a query in SQL. Below is the method:

    public void excluirVenda(int Codigo)
    {
        Conexao conexao = new Conexao();
        SqlCommand cmd = conexao.CreateCommand();
        SqlTransaction transaction = null;

        Conexao conexaoNovo = new Conexao();
        SqlCommand cmdNovo = conexaoNovo.CreateCommand();
        SqlTransaction transactionNovo = null;

        Log.Trace(logger, "excluirVenda");
        try
        {
            conexao.OpenConnection();
            transaction = conexao.transaction();

            // Assign Transaction to Command
            cmd.Transaction = transaction;

            cmd.CommandText = "update venda set Cancelada = 'S' where ID in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda)";
                //"or ID_Venda_Controle = (select ID from Venda_Controle where ID_Venda_Produto =@ID_Venda or ID_Venda_Servico = @ID_Venda) ";

            cmd.Parameters.AddWithValue("@ID_Venda", Codigo);
            cmd.ExecuteNonQuery();

            cmd.CommandText = "update Atendimento_Item set ID_Venda = null where ID_Venda in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda)";

            cmd.ExecuteNonQuery();

            cmd.CommandText = "update Financeiro_C_Receber set Cancela = 1 where ID_Venda in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                "where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda) " +
                "or ID_Venda_Controle = (select ID from Venda_Controle where ID_Venda_Produto =@ID_Venda or ID_Venda_Servico = @ID_Venda) ";

            cmd.ExecuteNonQuery();

            //adicionar procedimentos de devolução de credito para cancelamento de venda

            cmd.CommandText = "select Valor_Credito from venda where ID in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda)";
            double valorCredito = Convert.ToDouble(cmd.ExecuteScalar());


            if (valorCredito > 0)
            {
                cmd.CommandText = "update Cliente_Credito set Cancelado = 1, Motivo_Cancelamento = Concat('Cancelamento da venda - ',ID_Venda) where ID_Venda = @ID_Venda";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select top 1 valor_final from cliente_Credito where id_cliente = "+
                    "(select id_cliente from Cliente_Credito where Cancelado = 1 and ID_Venda ID in " +
                    "(select v.ID from Venda_Controle vc " +
                    "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                    " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda)) order by id desc";
                double valorAnterior = Convert.ToDouble(cmd.ExecuteScalar());

                cmd.CommandText = "insert into Cliente_Credito (ID_Cliente, data, Valor_Anterior,Tipo, Valor_Final, Motivo, cancelado,  ID_Usuario_Logado, ID_Form)" +
                "values ((select id_cliente from Cliente_Credito where Cancelado = 1 and ID_Venda = @ID_Venda), GETDATE(), @valor_cred_anterior,'E', @Valor_cred_Final, "+
                "(select concat ('Devolução de Crédito - Cancelamento da Venda: ',@ID_Venda)), 0,@ID_Usuario_Logado, (select id from Sistema_Form where Descricao = 'Cancelar Venda'))";

                if (Functions.Id != 0)
                {
                    cmd.Parameters.AddWithValue("@ID_Usuario_Logado", Functions.Id);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ID_Usuario_Logado", DBNull.Value);
                }

                cmd.Parameters.AddWithValue("@Valor_cred_Final", valorAnterior + valorCredito);
                cmd.Parameters.AddWithValue("@valor_cred_anterior", valorAnterior);
                cmd.ExecuteNonQuery();
            }

            SqlConnection connection = new Conexao().GetConnection();
            cmd = new SqlCommand("Select ID_Produto, Quantidade From Venda_Item_KIT Where ID_Venda in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda) ",connection);

            cmd.Parameters.AddWithValue("@ID_Venda", Codigo);
            connection.Open();
            SqlDataReader sdr = cmd.ExecuteReader();

            while (sdr.Read())
            {
                SqlCommand cmdInserir = conexao.CreateCommand();
                cmdInserir.Transaction = transaction;
                cmdInserir.CommandText = "INSERT INTO Kardex (ID_Deposito, ID_Produto, Tipo, Data, Anterior, Quantidade, ID_Sistema_Form, ID_Sistema)" +
                    "Values ((Select ID_Deposito From Venda Where ID = @idVenda), @idProduto, @Tipo, getdate(), " +
                    "(Select Quantidade From Estoque Where ID_Produto = @idProduto and ID_Deposito = ( select ID_Deposito from Venda where ID = @idVenda)), " +
                    " isnull((Select Quantidade From Estoque Where ID_Produto = @idProduto and ID_Deposito = "+
                    "( select ID_Deposito from Venda where ID = @idVenda)), 0) + (@qtdeProduto), (Select ID from Sistema_Form where Descricao = 'Cancelar Venda'), "+
                    "(Select ID from Sistema where Descricao = 'Administração'))";

                cmdInserir.Parameters.AddWithValue("@Tipo", 'E');
                cmdInserir.Parameters.AddWithValue("@idProduto", sdr["ID_Produto"]);
                cmdInserir.Parameters.AddWithValue("@qtdeProduto", sdr["Quantidade"]);
                cmdInserir.Parameters.AddWithValue("@idVenda", Codigo);
                cmdInserir.ExecuteNonQuery();

                cmdInserir.CommandText = "UPDATE Estoque SET Quantidade = (Quantidade + @qtdeProduto) WHERE ID_Produto = @idProduto";
                cmdInserir.ExecuteNonQuery();
            }
            sdr.Close();
            connection.Close();
            conexao.transactionCommit();

            conexaoNovo.OpenConnection();
            transactionNovo = conexaoNovo.transaction();
            cmdNovo.Transaction = transactionNovo;
            try
            {
                String sql = "select vp.ID_Produto,sum(vp.Quantidade) as 'Quantidade',pag.ID as 'CodGranel',pag.Id_Produto_Pai, " +
                                "pag.Peso_Produto_Pai, v.ID_Deposito "+
                                "from Venda_Item_Produto vp " +
                                "inner join Venda v on vp.ID_Venda = v.ID " +
                                "inner join Produto p on vp.ID_Produto=p.ID " +
                                "left join Produto_Fracionado pag on pag.Id_Produto_Filho = vp.ID_Produto " +
                                "where v.ID in " +
                                "(select v.ID from Venda_Controle vc " +
                                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                                " where ID_Venda_Produto = @idVenda or ID_Venda_Servico = @idVenda) " +

                                "GROUP BY vp.ID_Produto,vp.Quantidade,pag.ID,pag.Id_Produto_Pai,pag.Peso_Produto_Pai, " +
                                "v.ID_Deposito ";
                SqlConnection con = new Conexao().GetConnection();
                SqlCommand cmdEstoque = new SqlCommand(sql, con);
                cmdEstoque.Parameters.AddWithValue("@idVenda", Codigo);
                con.Open();
                SqlDataReader sdrEstoque = cmdEstoque.ExecuteReader();
                VendaModel venda = new VendaModel();

                while (sdrEstoque.Read())
                {
                    cmdEstoque.Parameters.Clear();

                    cmdEstoque.Parameters.AddWithValue("@ID_VendaMovimentacao", Codigo);

                    ProdutoFracionadoModel produtoGranel = new ProdutoFracionadoModel();
                    if (sdrEstoque["CodGranel"] != DBNull.Value)
                        produtoGranel.Id = Convert.ToInt32(sdrEstoque["CodGranel"]);
                    if (sdrEstoque["Id_Produto_Pai"] != DBNull.Value)
                        produtoGranel.ProdutoPai.Codigo = Convert.ToInt32(sdrEstoque["Id_Produto_Pai"]);
                    if (sdrEstoque["Peso_Produto_Pai"] != DBNull.Value)
                        produtoGranel.Peso = Convert.ToDouble(sdrEstoque["Peso_Produto_Pai"]);

                    VendaItemModel vi = new VendaItemModel();

                    vi.Produto = new Model.ProdutoModel();

                    if (sdrEstoque["ID_Produto"] != DBNull.Value)
                        vi.Produto.Codigo = Convert.ToInt32(sdrEstoque["ID_Produto"]);

                    if (sdrEstoque["Quantidade"] != DBNull.Value)
                        vi.Quantidade = Convert.ToDouble(sdrEstoque["Quantidade"]);

                    if (sdrEstoque["ID_Deposito"] != DBNull.Value)
                        venda.IdDeposito = Convert.ToInt32(sdrEstoque["ID_Deposito"]);




                    cmdNovo.Parameters.Clear();

                    cmdNovo.Parameters.AddWithValue("@ID_VendaMovimentacao", Codigo);
                    cmdNovo.CommandText = "select Quantidade from Estoque where ID_Produto=@idProduto and " +
                    "ID_Deposito=@ID_Deposito";
                    cmdNovo.Parameters.AddWithValue("@ID_Deposito", venda.IdDeposito);
                    cmdNovo.Parameters.AddWithValue("@idProduto", vi.Produto.Codigo);
                    double quantidadeAtual = Convert.ToInt32(cmdNovo.ExecuteScalar());

                    if (produtoGranel.Id != 0)
                    {
                        double quantidadeUnidadePai = 0;
                        double quantidadeRestantePai = 0;
                        double totalPeso = 0;
                        cmdNovo.CommandText = "select isnull(Quantidade,0) from Estoque where ID_Produto=@idProdutoPai and " +
                                          "ID_Deposito=@ID_Deposito";
                        cmdNovo.Parameters.AddWithValue("@idProdutoPai", produtoGranel.ProdutoPai.Codigo);
                        quantidadeUnidadePai = Convert.ToDouble(cmdNovo.ExecuteScalar());

                        cmdNovo.CommandText = "select isnull(Quantidade_Restante,0) from Estoque where ID_Produto=@idProdutoPai and " +
                                         "ID_Deposito=@ID_Deposito";
                        quantidadeRestantePai = Convert.ToDouble(cmdNovo.ExecuteScalar());

                        totalPeso = (quantidadeUnidadePai * produtoGranel.Peso) + quantidadeRestantePai;

                        double restante = ((totalPeso / produtoGranel.Peso) + (vi.Quantidade / produtoGranel.Peso));

                        int UnidadeRestante = (int)Math.Truncate((totalPeso / produtoGranel.Peso) + (vi.Quantidade / produtoGranel.Peso));
                        double resto = restante - UnidadeRestante;

                        //Quantidade Fracionada do produto Atual
                        double QuantidadeFracionadaAtual = 0;
                        cmdNovo.CommandText = "select isnull(Quantidade_Restante,0) as 'Qtde.Restante atual' from Estoque where ID_Produto=@idProdutoPai and " +
                           "ID_Deposito=@ID_Deposito";
                        QuantidadeFracionadaAtual = Convert.ToDouble(cmdNovo.ExecuteScalar());

                        cmdNovo.CommandText = "insert into Kardex(ID_Deposito,ID_Produto,Tipo,Data,Anterior,Quantidade,ID_Venda, Fracionado_Anterior, Fracionado_Quantidade) values (@ID_Deposito,@idProdutoPai,'E',getDate(),@anterior,@novaQuantidade,@ID_VendaMovimentacao,  @FracionadoAnterior, @quantidadeRestante)";
                        cmdNovo.Parameters.AddWithValue("@anterior", quantidadeUnidadePai);
                        cmdNovo.Parameters.AddWithValue("@novaQuantidade", UnidadeRestante);
                        cmdNovo.Parameters.AddWithValue("@FracionadoAnterior", QuantidadeFracionadaAtual);
                        cmdNovo.Parameters.AddWithValue("@quantidadeRestante", resto * produtoGranel.Peso);

                        cmdNovo.ExecuteNonQuery();
                        cmdNovo.Parameters.RemoveAt("@novaQuantidade");
                        cmdNovo.Parameters.RemoveAt("@FracionadoAnterior");

                        cmdNovo.CommandText = "update Estoque set Quantidade=@unidade,Quantidade_Restante=@quantidadeRestante where ID_Produto=@idProdutoPai and ID_Deposito=@ID_Deposito";
                        cmdNovo.Parameters.AddWithValue("@unidade", UnidadeRestante);


                        cmdNovo.ExecuteNonQuery();
                    }
                    else
                    {
                        double novaQuantidade = quantidadeAtual + vi.Quantidade;

                        double? QuantidadeFracionadaAtual = null;
                        cmdNovo.CommandText = "select Quantidade_Restante as 'Qtde.Restante atual' from Estoque where ID_Produto=@idProduto and " +
                           "ID_Deposito=@ID_Deposito";
                        try
                        {
                            QuantidadeFracionadaAtual = Convert.ToDouble(cmdNovo.ExecuteScalar());
                        }
                        catch (Exception)
                        {
                            QuantidadeFracionadaAtual = null;
                        }


                        cmdNovo.CommandText = "insert into Kardex(ID_Deposito,ID_Produto,Tipo,Data,Anterior,Quantidade,ID_Venda, Fracionado_Anterior,Fracionado_Quantidade) values (@ID_Deposito,@idProduto,'E',getDate(),@anterior,@novaQuantidade,@ID_VendaMovimentacao,@Fracionado,@Fracionado)";

                        cmdNovo.Parameters.AddWithValue("@anterior", quantidadeAtual);
                        cmdNovo.Parameters.AddWithValue("@novaQuantidade", novaQuantidade);

                        if (QuantidadeFracionadaAtual != null)
                        {
                            cmdNovo.Parameters.AddWithValue("@Fracionado", QuantidadeFracionadaAtual);
                        }
                        else
                        {
                            cmdNovo.Parameters.AddWithValue("@Fracionado", DBNull.Value);
                        }


                        cmdNovo.ExecuteNonQuery();
                        cmdNovo.Parameters.RemoveAt("@novaQuantidade");
                        cmdNovo.Parameters.RemoveAt("@Fracionado");


                        cmdNovo.CommandText = "update Estoque set Quantidade=@new where ID_Produto=@idProduto and ID_Deposito=@ID_Deposito";
                        cmdNovo.Parameters.AddWithValue("@new", novaQuantidade);
                        cmdNovo.ExecuteNonQuery();
                    }
                }
                sdrEstoque.Close();

            }
            catch (Exception erro)
            {
                Log.Erro(logger, "excluirVenda", erro.Message);
                conexaoNovo.transactionRollback();
                conexao.transactionRollback();
                questions.erroGenericoSQL(" de excluir a venda");
                conexao.transactionRollback();
                return;
            }

            conexaoNovo.transactionCommit();            
            questions.mensagemExclusao();
        }

        catch (SqlException erro)
        {
            Log.Erro(logger, "excluirVenda", erro.Message);
            Excecoes.mensagemErroCodigo(erro.Number, " excluir a venda");
            conexaoNovo.transactionRollback();
            conexao.transactionRollback();                
        }

        finally
        {
            conexao.CloseConnection();
        }

The error occurs in this part of the method:

     SqlConnection connection = new Conexao().GetConnection();
            cmd = new SqlCommand("Select ID_Produto, Quantidade From Venda_Item_KIT Where ID_Venda in " +
                "(select v.ID from Venda_Controle vc " +
                "inner join venda v on vc.ID_Venda_Produto = v.ID or vc.ID_Venda_Servico = v.ID " +
                " where ID_Venda_Produto = @ID_Venda or ID_Venda_Servico = @ID_Venda) ",connection);

            cmd.Parameters.AddWithValue("@ID_Venda", Codigo);
            connection.Open();
            SqlDataReader sdr = cmd.ExecuteReader();

It returns this message: "Execution Timeout Expired. The timeout period expired before the operation completed or the server is not responding." Note: I already tried to increase the TimeOut.

Answer:

Gabriel, a strong suggestion for your code is to break it down into smaller parts (methods), this makes it easier to understand and a clean code pattern .

Reading your vi method you can create a sequence as follows:

  1. UpdateSaleAsCancelled
  2. Update Service Item
  3. Financial Update
  4. SearchValueCredit
  5. UpdateCustomer Credit
  6. SearchClientCredit
  7. InsertsCustomer Credit
  8. BuscaKitItemSale
  9. Insert Kardex
  10. Updatestock
  11. SearchProductItemSale
  12. Searchstock

I took this list just by reading your DMLs in the code, this makes the code maintenance much easier, for example the error already found which was trying to open a query in the base that was locked in a transaction not carried out.

The last item in the BuscaEstoque list you can parameterize it so that a single method is able to solve any query in the inventory table, because you use this table a few times in the same method.

I hope I collaborated.

Scroll to Top