CODEWITHSUNDEEP
c#sql-serverssmsdappernvarchar
Manu Mohan
Manu Mohan

Reputation: 1794

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

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.

Upvotes: 2

Views: 1902

Answers (2)

eftpotrm
eftpotrm

Reputation: 2271

I'm not anti storing of JSON/XML in the database, but it does give very big lumps of data to return sometimes which will take time to return - and more than in SSMS, which typically doesn't return the full information to you.

BUT - when you're returning this much data, it's important to filter. I doubt your application really needs all the fields or all the records for what it's trying to do; if you filter down to what you actually need in your query, you should get a faster result.

Upvotes: 0

user1666620
user1666620

Reputation: 4808

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.

Upvotes: 3

Related Questions