Queries containing columns with large NVARCHAR values ​​from SQL Azure are slow

Question

I have a table in an Azure database that responds slowly to queries. The request looks like this:

SELECT [Id] --nvarchar(128), PrimaryKey
      ,[Name] --nvarchar(max)
      ,[Description] --nvarchar(max)
      ,[Modified]  --datetime2(7)
      ,[LastModifiedBy] --nvarchar(max)
      ,[Opened]  --datetime2(7)
      ,[Editor] --nvarchar(max)
      ,[Json] --nvarchar(max)   <--THIS IS GIVING ME PROBLEMS
      ,[IsActive] --bit
  FROM [dbo].[TableName]

In particular, when I include the [Json] column in a query, the performance of SQL queries goes from less than a second to minutes. Even if you request only one entry, it may take minutes for the [Json] column to be enabled. This column contains long lines in json format (~ 500,000 characters). Performance only breaks when this column is turned on - other NVARCHAR (max) columns containing smaller rows are not a problem.

I discovered this problem due to performance issues with the MVC5 application using the Linq-to-entity query from the Entity Framework:

var model=await db.TableName.FirstOrDefaultAsync(s => s.Id == id);

sql, , . Edit , , . db, , , .

.

3 :

SELECT Json FROM [dbo].[TableName] WHERE [Id]=<id>

. , 10 :

SELECT SUBSTRING(Json,1,50000) FROM [dbo].[TableName] WHERE [Id]=<id>

, , .:

DECLARE @variable nvarchar(max);
SELECT @variable=Json FROM [dbo].[TableName] where Id='<id>';
SELECT LENGTH(@variable);

, , :

DECLARE @variable nvarchar(max);
SELECT @variable=Json FROM [dbo].[TableName] where Id='<id>';
SELECT @variable;

- , Linq-to-entity Entity Framework , #, , EF ,

, . , , EF linq-to-sql ?

, SQL Server ; .

-update -

, . . Azure. , , , , .

, .

Azure ( ) :

• Azure-SQL Server
• ,
• Azure-SQL Server ,
• - : ) b) . , , .
• . , 10 , 50000 10 , 500000 .. .
• ( ), , , .

, - , , , , Azure , . , Azure DB, . , , , , .

Answer 1

nvarchar (max), , , Azure:

• Azure Sql Db: https://feedback.azure.com/forums/217321-sql-database/category/129652-bugs
• @AzureSupport
• Azure: / .
• Azure

, - , , -, Azure.

Answer 2

, , , " [Json] , SQL- ". :

, , .:

DECLARE @variable nvarchar(max);
SELECT @variable=Json FROM [dbo].[TableName] where Id='<id>';
SELECT LENGTH(@variable);

, : ASYNC_NETWORK_IO. SQL- .

, . , , - . , .

, , , , . .

: JSON, DocumentDB Azure SQL Database? , T-SQL JSON, DocumentDB.

:

- : ) - ) . , , .

- , . Azure SQL Database, , , , . , , , .

, !