Slow SqlCommand Performance with Longer CommandText

Question

Does CommandText SqlCommand Mean Length? I'm not talking about thousands of characters either. Here is what I have:

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; for (int i=0; i<1000; ++i) { string name = i.ToString() + "Bob" + i.ToString(); string email = i.ToString() + "Jim" + i.ToString(); // etc... cmd.Parameters.Clear(); cmd.Parameters.Add(new SqlParameter("@name", name)); cmd.Parameters.Add(new SqlParameter("@country", country)); DateTime cmdStart = DateTime.Now; cmd.ExecuteNonQuery(); DateTime cmdEnd = DateTime.Now; TimeSpan len = cmdEnd - cmdStart; } 

If I use the following sql, the first iteration takes 0.5 seconds. The second takes 1.1 seconds. The third takes 3.3 seconds. And so on, until he just throws a timeout.

 string sql = "INSERT INTO Test " + " ([name] " + " ,[email] " + " ,[country] " + " ,[comment] " + " ,[date] " + " ,[key_v0] " + " ,[key_v1] " + " ,[expires_v1] " + " ,[expires_v2] " + " ) " + " VALUES " + " (@name " + " ,@email " + " ,@country " + " ,' ' " + " ,@date " + " ,@key_v0 " + " ,@key_v1 " + " ,@expires_v1 " + " ,@expires_v2 " + " )"; 

However, if I use the following sql, the whole loop runs in a second.

 string sql = "INSERT INTO Test " + "([name] " + ",[email] " + ",[country] " + ",[comment] " + ",[date] " + ",[key_v0] " + ",[key_v1] " + ",[expires_v1] " + ",[expires_v2] " + ") " + "VALUES " + "(@name " + ",@email " + ",@country " + ",' ' " + ",@date " + ",@key_v0 " + ",@key_v1 " + ",@expires_v1 " + ",@expires_v2 " + ")"; 

The only difference is a space. Removing the space led to a total number of characters from 428 to 203. I could not find anything referring to the length of the CommandText, except for references to the limits of 4k and 8k. I'm nowhere near

I launched both versions with the launch of the profiler, and for all calls the duration is less than 10 ms. The delay seems to occur when the command completes inside the SQL engine until ExecuteNonQuery returns.

I know that there are alternative ways to do this. I am not asking about the best ways to do this. I ask about the source of the slowdown.

UPDATE: As a test, I added spaces to the end of the query. As soon as I received more than 400 characters, it slowed down. Interestingly, with 414 characters, the first 99 inserts are fast. With 415 characters, the first 9 inserts are fast. Since I am modifying some lines based on the number of iterations, this kind of makes sense. for example, the 10th insert is slightly longer than the ninth, and the 100th insert is slightly longer than the 99th.

As long as I understand that longer inserts should take longer, I cannot understand the clear distinction between fast and slow and the absolute value of the difference. I also do not understand why time is increasing.

UPDATE 2: (More info in response to Peter Oelert's answer): The entire database is clean. There are no other tables, and the test table is discarded and recreated for each run. No indexes, triggers or foreign keys. There is an id column, which is the primary key.

This is code extracted from a console application written specifically to fix this problem. It contains only the necessary code to reproduce this behavior.

(Additional profiler information): When starting the SQL profiler, there is a column called TextData that shows what the command and data are. Example:

 exec sp_executesql N'INSERT INTO Test ([name] ,[email] ,[country] ,[comment] ,[date] ,[key_v0] ,[key_v1] ,[expires_v1] ,[expires_v2] ) VALUES (@name ,@email ,@country ,'' '' ,@date ,@key_v0 ,@key_v1 ,@expires_v1 ,@expires_v2 ) ',N'@name nvarchar(4),@country nvarchar(2),@email nvarchar(3),@date datetime,@key_v0 nvarchar(4000),@key_v1 nvarchar(4000),@expires_v1 datetime,@expires_v2 datetime',@name=N'9Bob',@country=N'us',@email=N'Jim',@date='2009-08-28 15:35:36.5770000',@key_v0=N'',@key_v1=N'',@expires_v1='2009-08-28 15:35:36.5770000',@expires_v2='2009-08-28 15:35:36.5770000' 

This string is 796 characters long and is fast. Changing the name from "9Bob" to "10Bob" results in a slow insertion. Neither 796 nor 797 seem significant. Removing the exec fragment sp_executesql means the lengths are 777 and 778. They also do not seem significant.

I'm at a dead end.

Update: Added here: http://www.jere.us/WierdInserts.trc

Answer 1

Well, I don't have a direct answer, but here's how I approach the problem.

• Determine who is causing the problem. The first step would be to run the SQL profiler and see if there is a problem in the database or something in the code.

SQL

If this is a database, I would look at a few things: each of them is faced with string concatenation problems, which, although true, are likely to be less than 5 ms of your time. I will also dissolve spaces as the source of the problem. Again, this will make a slight difference, but will not explain the degradation you described. You are looking for something that will have this progression (0.5, 1.1, 3.3).

I would look, in particular, on which indexes that you defined in this table, what restrictions / triggers are in this table and how many external key relationships are present. Alternatively, I could pull out the queries that are running slowly and run them in the sql enterprise manager.

The last thing I could investigate is whether there is a bad cache plan introducing problems with some data-dependent function. This would be true only if you had interesting triggers that use part of your data or certain types of indexes. You can look at this by calling DBCC FREEPROCCACHE between calls to your insert statement and see if this has changed. This command will clear the plan cache, forcing sql to update the new execution plan for your request.

Client

If this is a client, you need to determine what is causing the problem in your code. If you have a performance tracking tool (such as a Visual Studio performance analyzer) that will manage your code, I would use it, as it will VERY quickly tell you what exactly takes so long.

If you do not have this option, start by pulling the code into the new console application with the smallest possible set of unforeseen situations and see if you can play it. You are looking for everything that may be causing the progression that you see.

Answer 2

I think part of the performance hit is cleaning and adding parameters (along with inefficient string manipulation). What happens if you change the structure a bit?

 SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter("@name", "")); cmd.Parameters.Add(new SqlParameter("@country", "")); // etc.. for (int i=0; i<1000; ++i) { // etc... cmd.Parameters["@name"].Value = i.ToString() + "Bob" + i.ToString(); cmd.Parameters["@country"].Value = i.ToString() + "Uganda" + i.ToString(); DateTime cmdStart = DateTime.Now; cmd.ExecuteNonQuery(); DateTime cmdEnd = DateTime.Now; TimeSpan len = cmdEnd - cmdStart; } 

Added

I also looked for the code incorrectly, and now I realized that this is CommandType.Text, right?

Does it create a true stored procedure on the server and then call it by specifying CommandType.StoredProcedure and passing the name of the stored procedure instead of the QL statement for you? I understand that I am not responding to your underlying quesiton, but I really do not think that the length of the CommandText matters to SQL Server, so I am considering other possible performance barriers.

This is a shot in the dark, and I hope someone with a proven knowledge of the inside about how CommandObject parses SQL text instructions with parameters can check this, but I assume that the command object should parse the CommandText when it calls ExecuteNonQuery () (or ExecuteScalar (), etc.).

String processing is expensive, and if you force the command object to reanalyze the parameters each time, this can also be added at the time of arrival.

Add to this the fact that true stored procedures usually perform better due to compiled execution plans, and you can see some improvements.

Answer 3

Lines in .Net are immutable. This has many advantages, but one of the drawbacks is that to combine the two of them, you need to allocate a buffer for the entire new third line, and not just expand the buffer for the first line. The code you showed there contains 21 separate concatenation operations, which can be slow. Normally, I would expect jit-optimizer to take care of this problem for you, but it is possible to skip this anyway. You can try declaring the variable as static readonly and see if this helps anyone.

Despite this, I would expect this to result in a difference of a few milliseconds. This is unlikely to make your request or violate it. The best suggestion I can give you is to take both versions of your query and paste them into different windows of the management studio, as well as the DECLARE and SET instructions for each of your parameters and compare the execution plans.

Finally, David Stretton's advice on reusing the same parameters sounds. It makes no sense to remove and recreate the same parameters every round, when you can just update the values ​​and re-run the same query.

Answer 4

If 10Bob is much slower than 9Bob and 99Bob , this potentially indicates an INDEX on Name, where FILLFACTOR is set too high, or SQL Server should reinstall the page when it hits "1" in "10Bob".

So this explains Bob, except that you say there is no index, and spaces also matter ...

768 bytes is an important boundary in MySQL to determine if a BLOB is stored in a row or in a separate table. Perhaps the SQL query optimizer has a similar border?

This may explain the tiny difference in performance, but does not explain the order of magnitude.

By default, SQL Server uses 8k pages, so you can expect that the first INSERT will happen the first INSERT, which requires a new page, but again, nothing to do with the space and does not explain the amount of delay here.

Answer 5

Your trace has all the inserts lasting 0-3 ms. There are big times between executions: the insert ends at 12:53:10, and the next at 12:53:13, so there is a 3-second delay in the client between the two INSERTS. As a rule, there may be a delay anywhere between the client and the server, but from the symptoms that you describe, I would exclude the random flacky router between the client and the server (the behavior would be more random).

In some place I would look:

• database growth events / log growth event. This is very common in tests, because the test setup deploys a new db test, and then the test approaches the growth event at about the same moment (for example, the 10th insert). Easily verified using performance counters and profiling events: Auto Grow Event Class data file . The solution is to pre-develop the db test (in fact, you should always develop both mdf and ldf in advance for effective testing). The geometric rate of time increase will not be taken into account.
• Garbage collector in the client. Again, you can track in performance counters.
• Exhaustion of the connection pool (i.e. testing connection leaks, the pool must open new ones, and it is configured to save the number of minutes, so it works in batches). sys.dm_exec_connecitons will grow. There is also a perfmon counter for user sessions (both on the server and on ADO.Net counters).
• code defect. Some pending / processing in client code, possibly list processing. This is the most likely reason, the only one that explains the square rate of increase in delay (NxN for a list of length N, N increases with each test run, possibly a sort or saving of the test result or something like that).

Answer 6

The question was asked a long time ago, but maybe my answer will help someone. I just ran into the same problem and found out that this only happens inside the virtual machine (I use VirtualBox for development). I wrote several tests and ran them inside a virtual machine and on a production server. There are no performance issues on the production server.

Perhaps there is a strange error in Virtualbox, maybe something is wrong with the network settings of the virtual machine (I use the default settings).

Answer 7

SQL Server parses your query before executing it, simplifies parsing and less unwanted code = faster parsing. Before parsing, it will be necessary to remove all unnecessary spaces, however, each cycle with a smaller cycle is calculated when you perform several operations.

You can test the test by doubling the number of spaces, and you should see more slowdown, since parsing a character by processing characters is directly proportional to the number of characters. And parsing is the first step, even if its a parametric query, SQL still needs to be parsed and understood that it is a query.