Insert Statement Times out due to nvarchar(max) field

Question

I have a Sql Server database on Azure that processes incoming EDI documents. Basically, it receives the data, and saves it to a row in a table. The size of the 'edi_data' column can be as large as 7 Megs.

We have been using this for about two years with no problems. However, in the last two days, the insert statement has exceeded the 30 second timeout, and so throws an error.

The DTU of this database has been increased from 15, to 100. Raising the DTU did help to process more transmissions, but this error is occurring again today. The max DTU is not getting above 35% after the increase.

The is the Insert as generated by EntityFramework 6:

 (@0 int,@1 int,@2 datetime2(7),@3 nvarchar(max),@4 nvarchar(max),@5 
 nvarchar(max),@6 nvarchar(max),@7 nvarchar(max),@8 nvarchar(max),@9 
 nvarchar(max),@10 nvarchar(max),@11 nvarchar(max),@12 nvarchar(max),@13 
 nvarchar(max),@14 nvarchar(max),@15 nvarchar(max),@16 nvarchar(max),@17 
 bit,@18 int)INSERT [dbo].[transmission]([transmission_status_id], 
 [transmission_attempts], [transmission_date], [edi_data], [originator_num], 
 [recipient_num], [error_message], [encryption_type], [gisb_version], 
 [receipt_signing_protocol], [receipt_type], [http_request], 
 [request_headers], [http_response], [response_headers], [edi_type], 
 [original_file_name], [file_name], [archive_flag], [group_control_code], 
 [orig_transmission_id], [direction])
 VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, NULL, @13, @14, @15, @16, @17, NULL, NULL, @18)

Is there some other way to resolve this other than increasing the DTU even more? (I know I can increase the Command Timeout to more than 30 seconds, but I would like to fix the speed issue if possible.)

ADO.NET offers streaming methods for blobs like nvarchar(max) and varbinary(max). Trying to store them as normal values on the other hand transfers the entire blob at once. An ORM isn't the proper tool for working with BLOBs — Panagiotis Kanavos
For example this query shows how to load a BLOB from a stream. This way you avoid allocating that huge 7MB string in .NET too — Panagiotis Kanavos
The SqlClient Streaming Support article explains how to use streaming. It shows that NVarchar-typed parameters can accept a TextReader as Value. — Panagiotis Kanavos
@PanagiotisKanavos, thanks very much. If you want to post as answer, I will accept. — Greg Gum

Panagiotis Kanavos Panagiotis Kanavos · Accepted Answer · 2018-07-19T16:39:52

The nvarchar(max) and varbinary(max) types are meant for storing CLOBs and BLOBs. While one can save a relatively small buffer or string directly, it's far more efficient to use a streaming API to copy large amount of data to the server. This can lead to significant memory savings for the client too, as it won't have to allocate the entire 7MB string at once, facing the inevitable garbage collection penalty.

Entity Framework doesn't offer any streaming functionality directly. ADO.NET and specifically the SqlClient provider does. The SqlClient Streaming Support article in the documentation explains how to use streaming to load or store big files into a BLOB field.

A SqlParameter with a text type like NVarChar can accept a TextReader as a value instead of a string. SqlClient will read the data from the reader and send it to the database.

Stealing the doc samples, for this table :

CREATE TABLE [TextStreams] (  
    [id] INT PRIMARY KEY IDENTITY(1, 1),  
    [textdata] NVARCHAR(MAX)
)

The following method will copy data from the source stream to the server :

  private static async Task StreamTextToServer() {  
     using (SqlConnection conn = new SqlConnection(connectionString)) {  
        await conn.OpenAsync();  
        using (SqlCommand cmd = new SqlCommand("INSERT INTO [TextStreams] (textdata) VALUES (@textdata)", conn)) {  
           using (StreamReader file = File.OpenText("textdata.txt")) {  

              // Add a parameter which uses the StreamReader we just opened  
              // Size is set to -1 to indicate "MAX"  
              cmd.Parameters.Add("@textdata", SqlDbType.NVarChar, -1).Value = file;  

              // Send the data to the server asynchronously  
              await cmd.ExecuteNonQueryAsync();  
           }  
        }  
     }  
  }

Insert Statement Times out due to nvarchar(max) field

1 Answers