0

I have to process daily files and it's a long process that deletes, imports and updates processes. Like literally 20 to 30 minutes per file for several files a day.

** EDIT: I also have some other processes that look like they are going to be going for 90+ minutes each and I'm worried about them too. Like, is this the best strategy?

I have a console application running with something like this:

For each file:

  using (SqlConnection cx = new SqlConnection(connectionString))
        {
           SqlCommand cmd = new SqlCommand("delete from " + tableName + " where fileName = '" + fileName + "' and date > @date", cx);
           cmd.AddWithParameters("@date", fileDate);
           cmd.CommandTimeout = 1280;
           cx.Open();
           cmd.ExecuteNonQuery();
          }

And then:

    using (var sbc = new SqlBulkCopy(connectionString))
      {
               sbc.DestinationTableName = tableName;
               sbc.BatchSize = 750; 
               sbc.BulkCopyTimeout = 640;
               sbc.WriteToServer(dt);
       }

And finally:

   System.Data.DataTable dt = new System.Data.DataTable();
   System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

   using (SqlConnection cx = new SqlConnection(connectionString))
          {
               SqlCommand cmd = = new SqlCommand("SELECT * from " + tableName + " where fileName = '" + fileName + "'", cx);
               cx.Open();
               da.SelectCommand.CommandTimeout = 1280;
               da.Fill(dt);
          }

           // this is followed by reporting on the data

So this is a low priority task but it has to get done and each file has to get processed sequentially. Is there any problem with setting the connection timeout for so long? We are on an internal network. I've run DTA and taken all their indexing advice.

Any other thoughts, advice and ideas welcome.

3
  • 1
    CommandTimeout <> ConnectionTimeout. The using block will implicitly close the connection after the queries complete. Is your concern about the implications of the 20+ minute command timeout?
    – Dan Guzman
    Aug 31, 2019 at 15:07
  • @DanGuzman -- Thanks for answering. I'm just generally concerned about doing this on an unsupervised task and I'm wondering if there are any implications in terms of command and connection time outs. Thanks for pointing out the difference though. I hadn't thought about anything except that it seemed like something I should at least check with someone about. I'm the only programmer and there is a ton of data. Other processes have been going for 90 minutes+ and I'm worried about them, too.
    – Missy
    Aug 31, 2019 at 15:11
  • 3
    Long-running queries can hold locks that block other processes that cause them to run longer. You could for example, perform the delete in batches instead of all rows at once, avoiding the delete from blocking select queries for long periods. I would take a look at the 90-minute queries to determine why they are running so long (i.e. blocking or in the need of query/index tuning). BTW, it's not a good idea to take all DTA advise but rather be selective for the most benefit with the least overhead.
    – Dan Guzman
    Aug 31, 2019 at 15:26

1 Answer 1

0

I solved this by creating a list of tasks and then starting all the long running tasks at the end of the processing and did not wait for them to finish. This way, the program is able to exit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.