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
If I clear data in
nvarchar(max) fields, it's returning data very fast.
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.
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.