select on nvarchar(max) column really slow

Question

SQL Server 2008 R2. I'm doing a select on a table with ~70,000 rows. If I try to query an nvarchar(max) column, where the longest string is 2,433 characters, it takes 66 seconds.

select Comment from StudentAssessmentComments

Filtering by an indexed column to 17,000 rows still takes ~17 seconds.

select Comment from StudentAssessmentComments where FileYear = 2016

If I query a different, unindexed column, its immediate (< 1sec).

select StaffID from StudentAssessmentComments

Is it normal for an nvarchar(max) column to perform so badly? If not, does anyone have any suggestions? Thanks for any help.

Answer 1

I am going to guess that you have a relatively slow network connection to the database. The two timings you give are both pretty close to 1,000 rows per second.

If a typical row had 1,000 characters, then this would be about 2,000 bytes (wide characters) and about 2 Mbytes/second. That seems on the slow side, but it is not unreasonable.

So, my best guess is that this is your "network" connection to the database, so there is not much you can do about it if you need tens of thousands of rows with the full comments.