Open and close connection each time you run the looping

Question

Open and close connection each time you run the looping

Asked 6 years, 11 months ago

Viewed 687 times

1

I have an application that runs a procedure, saved in a DataTable and then do a looping to add the data in an excel spreadsheet. My question is, if I open and close the connection to each insert will increase the execution time of the process and/or I can open/close only once while doing the whole process ?

Follows code:

private void AtualizarPerformanceEntrega()
{
    try
    {
        DataTable dt = ExportPerformanceEntrega();

        string dir = Session.SessionID;
        string sFileXLSX = Server.MapPath(dir) + @"\Performance_Entrega_base.xls";

        if (File.Exists(sFileXLSX))
        {
            string strConnXLSX = (@"Provider =Microsoft.ACE.OLEDB.12.0;Data Source= " + sFileXLSX +"; Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False';");

            foreach (DataRow row in dt.Rows)
            {
                using (OleDbConnection connection = new OleDbConnection(strConnXLSX))
                {
                    string strSQL;
                    OleDbCommand cmd;
                    //OleDbDataReader dts;
                    if ((string)row["StatusEntrega"] == "No Prazo" || (string)row["StatusEntrega"] == "Fora do Prazo" || (string)row["StatusEntrega"] == "Antes do Prazo")
                    {
                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [Base Entregue$] " +
                           " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                           " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                           " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
                            //Abrindo a conexão
                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }
                    }
                    if ((string)row["StatusEntrega"] == "Não Entregue")
                    {               
                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [Base Não Entregue$] " +
                          " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                          " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                          " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
                            //Abrindo a conexão
                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }
                    }
                    //Fechando qualquer conexão que tenha ficado aberta, evitando o load infinito na página
                    connection.Close();   
                }               
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

1 answer

Browser other questions tagged c# asp.net

You are not signed in. Login or sign up in order to post.

by Oralista de Sistemas • **23,115** points · Answer 1 · 2017-10-10T13:21:32+00:00

Performance will be negatively impacted if you open and close connections for each item in the loop. Then the shortest and fastest answer is that you should open the connection before the foreach, and close after. You already make use of best practices by creating connections with using, then the last missing tip is: if the data is dependent on each other, using transactions can be a good business.

Now, about how performance is impacted.

On the client

There is a time consuming processor to open and close connection, and you will pay this cost every time you do these things. But that cost is small.

There is a cost of allocating memory to keep a connection open. You already use good practices (using), so the platform and the Garbage Collector already make all the logical part for you, and the cost is relatively low. But in general it is better to keep the amount of objects allocated to it as small as possible.

On the server

This is where the bugs start. The problem is not the cost of keeping an individual connection open, but the fact that database servers in general have to serve many clients at once. Opening and closing connections at random can overload the server.

The most current banks use a mechanism to deal with this called "pooling of connections"; Pooling could be translated freely as rotation. The server keeps a set of internal connection objects alive indefinitely and changes only specific properties to vary the clients served. Each client who asks for a connection is served by one of these objects, the first one that is free. When all the objects of the castor are occupied, the next customers will wait in line for service. By default, SQL Server 2016 keeps a rotation of 100 connections, but this number can be configured. The castor can even be switched off if it is more convenient.

Why is this important? When a server connection is released, it takes a while before it becomes available again in the rotation. If your application is the only one that uses the bank and your loop has less than 100 items, or if they all take a long time, you won’t feel much impact.

But if:

multiple instances of your application accessing the bank at the same time, or;
The insertion operation is super fast and the loop has more than 100 items, or;
for whatever reason, the pooling connection is disconnected or reduced...

Then you can feel a difference in application performance if you test both situations: open connection for each item or open connection once for the loop.