C# SQL inserts and the timeout property

The timeout on the connection property is how long to wait to acquire the actual connection before it fails.

>>inserts one row at a time into a SQL table

Why wouldn't you use bulk inserts?

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=dotnet-plat-ext-5.0

Because these are payroll processing records that need to be grouped and have rules applied to them before they can be inserted. They change with every pay run.Plus they need to be pushed through an ERP processing routine which has strict rules as to their layout.

Bulk processing was just a thought.  It is a lot faster than single rows but if it doesn't work for you, no problem.

Do you need anything else about the timeout?

Are you familiar with the Activity Monitor in SSMS? I asked before I can see a process  get locked onto the database as I start the import but then that process does not release until I completely exit my program. This is why I asked about the timeout parameter. But now I see it never lets go so it cannot be controlled by the timeout parameter.

There is a second timeout in SqlCommand as well. But I don't think it is the solution for your lock.

If you want to reduce the impact of your process on DB lock:
1. You can define IDLE CPU like described here: https://docs.microsoft.com/en-us/sql/ssms/agent/set-cpu-idle-time-and-duration-sql-server-management-studio?view=sql-server-ver15


2. Schedule SQL Server Agent Job:
3. It will start your job during IDLE time.
4. You job should insert some part of 2000 rows (1 batch) that does not lock DB for long time.
5. Next time - your job starts - it should insert another batch and so on.

In this way your process will not make any impact on production server.

The job step can execute EXE, SQL Script, SSIS package, etc. - whatever you need.

Without seeing the actual execute statements, there query plans and the involved table DDL including all indices it is hard to tell. Cause it is possible that due to transactional constraints this behaviour (other process are locked) is necessary.

Activity Monitor may be a non  issue. The task always goes blank after the import is done.

You mention seeing "something" locked but nothing really about it.  So, as ste5an mentioned, we really cannot tell you anything.

And no, sorry, I'm not familiar with SSMS.

This is becoming a non-issue. The more I discover the more it looks like something wrong with some Microsoft code.

The more I discover the more it looks like something wrong with some Microsoft code.

As already written, locked processes in a transactional system are an expected state depending on what concurrent workload is executed. I never had locked processes caused by "wrong [..] code".

I have probably misdiagnosed this. I thought the code was looking up. It is not. After the code runs I was trying to print the results to the screen. This is what locks up. I can print to the printer or file but not to the screen.  Something inside the host Microsoft code is preventing printing to the screen.

What are you talking about? SSMS`? There is no printing during execution.

Your application? Then you're missing Invalidate() or Repaint() or Refresh() calls in your application. Or your code runs not in the UI thread, then you need synchronization.

I have a method to create the SQL command. Part of this specifies the SQL timeout parameter. The client ran my code on Friday and received a timeout error. I have never gotten a timeout error running the same code in our data center. I will trying lengthening the timeout setting.

        static public SqlCommand MakeSQLCommand(CommandType commandType, string commandText, SqlParameter[] sqlParameters)
        {
            SqlCommand gpCommand = new SqlCommand(commandText);

            gpCommand.CommandType = commandType;
            gpCommand.CommandTimeout = 30;
            if ((commandType == CommandType.StoredProcedure) || (commandType == CommandType.Text))  //<-- Is this check actually needed???
            {
                if (sqlParameters != null)
                {
                    foreach (SqlParameter sqlParameter in sqlParameters)
                    {
                        gpCommand.Parameters.Add(sqlParameter);
                    }
                }
            }
            return gpCommand;
        }

>> gpCommand.CommandTimeout = 30;

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-5.0

The time in seconds to wait for the command to execute. The default is 30 seconds.

So, you set it to the default.

Are you sure that piece of code is what is causing the timeout error the client is seeing?

If an insert takes more than 30 seconds to run, I think you have bigger issues.

Yes, 30 seconds is the default. This insert could not be any simpler. Here are 12 columns on 1 record, insert it. At most each check run consists of 20 pay checks. I have had other issues with their server. Right now I will point to the fact they are using software RAID. Theirs is the only server that exhibits this issue.

RAID shouldn't affect a more than 30 second timeout on "something".  I put something in quotes because you didn't answer my question:
Are you sure that piece of code is what is causing the timeout error the client is seeing?

I can see a 30 second timeout on your initial concern, the connection, if there is a firewall or some other network issue and the app never makes it to the database.

So, you need to narrow down exactly where the app is failing and generating a timeout error.

The full stack trace of the error might also help.

The process is this. About 1,500 records are written into a work tables plus two tables that separate the data into pay checks and then all the distributions related to those pay checks. There is no issue here. The next step is I gave the user a preview button that runs two SQL queries. One for the pay checks and another for the distributions. There is grouping in the distributions and it is this step that times out. I will check the indexes on these tables and make sure they are optimized for the grouping.