Why are the `nvarchar` parameters faster than other types for the` text`` SqlCommand` commands?

Question

Overview

This question is a more specific version of this:

• sql server - performance when passing an argument of type C # Int64 to the parameter of the bigint stored procedure in T-SQL

But I noticed the same thing as for other data types (and, in fact, in my case, I do not use bigint types at bigint ).

Here are some more questions that seem to cover the answer to this question, but I observe the opposite of what they indicate:

• C # - When should I use "SqlDbType" and "size" when adding SqlCommand parameters? - stack overflow
• .net - What is the best way to pass SQLCommand parameters? - stack overflow

Context

I have C # code to insert data into a table. The code itself is data driven, as some other data indicates the target table into which the data should be inserted. That way, I could use dynamic SQL in a stored procedure, I decided to create dynamic SQL in my C # application.

The text of the command is always the same for line I, so I generate it once before inserting the lines. The text of the command has the form:

 INSERT SomeSchema.TargetTable ( Column1, Column2, Column3, ... ) VALUES ( SomeConstant, @p0, @p1, ... ); 

For each insertion, I create an array of SqlParameter objects.

For the " nvarchar " behavior, I simply use the SqlParameter(string parameterName, object value) constructor and do not set any other properties explicitly.

For the "degenerative" behavior, I used the SqlParameter(string parameterName, SqlDbType dbType) constructor SqlParameter(string parameterName, SqlDbType dbType) , and also set the Size , Precision and Scale properties.

For both versions of the code, the value passed to the constructor method or separately assigned to the Value property is of type object .

The < nvarchar 'code version takes about 1-1.5 minutes. The code "degenerate" or "specific type" takes more than 9 minutes; therefore, 6-9 times slower.

SQL Server Profiler does not detect obvious criminals. The coding type of code generates what seems to be the best SQL, that is, a dynamic SQL command whose parameters contain the corresponding data type and type information.

Hypotheses

I suspect that since I pass the value of type object as the parameter value, the ADO.NET SQL Server client code launches, converts or otherwise checks the value before generating and sending the command to SQL Server.I am surprised that the conversion from nvarchar to each of the corresponding column types of the target table that SQL Server should execute is much faster than what the client code does.

Notes

I know that SqlBulkCopy is probably the most efficient option for inserting a large number of lines, but I am more curious why the nvarchar case executes a type-specific case, and my current code is fast enough, because the amount of data that it usually handles.

Answer 1

The answer depends on the database you are running, but it is related to the character encoding process. SQL Server introduced NVarChar and NText field types for processing encoded data in UTF format. UTF is also an internal string representation for the .NET CLR. NVarChar and NText do not have to be converted to another character encoding, which takes a very short but measurable amount of time.

Other databases allow you to define character encoding at the database level, while others allow you to define it in columns by columns. The differences in performance really depend on the driver.

It is also important to note:

• Paste using a prepared statement emphasizes inefficiency when converting to the internal database format
• This does not affect how efficient the database queries are for the row - UTF-16 takes up more space than the standard Windows-1252 encoding for Text and VarChar.
• Of course, global application requires UTF support

Answer 2

They are not (but they are almost as fast)

My initial inconsistency was completely my fault. The way I created SqlParameter objects for a "degenerate" or "type-specific" version of the code used an extra loop than the version of the " nvarchar " code. As soon as I rewrote the type code to use the same number of loops (one), the performance is almost the same. [About 1-2% slower, not 500-800% slower.]

A slightly modified version of the type-specific code is now slightly faster; at least based on my (limited) testing - about 3-4% faster for ~ 37,000 teams.

But still (a little) surprisingly, this is not the way I expected SQL Server to turn hundreds of nvarchar values ​​into many other data types (for each execution) much slower than C # code to add type information to parameter objects. I assume that it is very difficult to notice a big difference, because the time for SQL Server to convert the parameter values ​​is quite small compared to the time for all other code (including the SQL client code associated with SQL Server).

One lesson that I hope to remember is that it is very important to compare how with the like.

Another lesson is that SQL Server quickly converts text to other data types.