Simple dapper select query with 20 rows(most of columns are nvarchar(max)) taking too long- 15 seconds and more

Question

My dapper code is given below with a select query:

const string Sql = @"SELECT  [id]
                              ,[groupName]
                              ,[reqCap]
                              ,[impCap]
                              ,[player]
                              ,[resumeDate]
                              ,[whitelist]
                              ,[blacklist]
                              ,[Macros]
                              FROM [VideoServer].[dbo].[TagGroup]";
return await dc.Connection.QueryAsync<TagGroup>(Sql);

My table design is given below:

[id] [int] IDENTITY(1,1) NOT NULL,
[groupName] [varchar](500) NOT NULL,
[reqCap] [int] NULL CONSTRAINT [DF_TagGroup_reqCap]  DEFAULT ((0)),
[impCap] [int] NULL CONSTRAINT [DF_TagGroup_impCap]  DEFAULT ((0)),
[player] [varchar](500) NULL,
[resumeDate] [date] NULL,
[whitelist] [nvarchar](max) NULL,
[blacklist] [nvarchar](max) NULL,
[Macros] [nvarchar](max) NULL

When I run this select query in SQL Server Management Studio it is returning within 0 milliseconds. But the same query from dapper (above code) is taking too long.

Any ideas? Is this because of nvarchar(max)?

If I clear data in nvarchar(max) fields, it's returning data very fast.

Answer 1

You are trying to pull 600+Kb out of the database for every record. 20 rows makes that almost 6Mb at a minimum per query.

The reason it runs quickly in SQL Server Management Studio is that it doesn't actually return the full column, it returns only the first X characters, so not all 6+MB is being processed. When you are running through code (dapper in this case) then all 6+MB is being returned.

If you are storing files in the database, you need to stop doing that and store them in the filesystem, and use the database to store the the locations and metadata of the files.