SQL Azure - Low performance of External Data Table with nvarchar(max) column

Question

I can't find out the reason for a select query against an external data table being so dramatically longer than a direct query against the same table.

The schema of the table is as follows:

CREATE EXTERNAL TABLE [WebApp].[TestTable]
(
    [TestTableId] [int] NULL,
    -- some other int columns
    [Body] [nvarchar](max) NULL,
)
WITH (DATA_SOURCE = [ExternalInterface])

The external data source refers to the database located in the same resource group and location.

Direct querying of a total of 70k rows completes in 1second. A query against the external table will take up to 20 minutes.

Removing the body column from the select will cut the total time to several seconds. So definitely it has to do with the amount of data transfered.

I guess something goes wrong with my configuration. I just don't believe the elastic query engine can be so slow.

Answer 1

So, I've managed to sort this out by casting the nvarchar(max) column to a nvarchar(n) .

SELECT [TestTableId], cast([Body] as nvarchar(3000)) from WebApp.TestTable

Alternatively, specifying the max-length for the Body column on the target table will also do.

alter table WebApp.TestTable alter column Body nvarchar(3000)

SQL considers the data in nvarchar(max) columns as large objects and for some reason the performance of external tables which contain LOB columns is just desperately bad...