What's the fastest way to load a text or ntext SQL Server column?

Question

I do this as simple as possible, but sometimes the query takes 3+ second. I'm using a basic parameterized query like this:

SELECT TextStuff FROM MyInfo WITH (NOLOCK) WHERE MyInfoID=@MyInfoID

Is there a faster way to do this? Would performance improve if I converted the ntext column into an nvarchar(MAX) column?

Answer 1

Have you profiled or analyzed the query to be sure that th text/ntext retrieval is the problem? Could be a bad index causing a table scan, for instance.

Answer 2

Yes, you may get a performance improvement with nvarchar max due to the differences in how the data is stored as described and analysed here

Another plus for moving to NVARCHAR MAX is the greater flexibility over ntext, plus ntext is being deprecated in favour of NVARCHAR MAX

The likely improvement in performance should be minimal when compared to a suitable index

Edit: When talking difference in execution times, this may be due to cached execution plan reuse/data being cached. If you're going to try optimising this, either by changing indexes or changing data type, make sure you compare performance fairly to make sure you don't get biased results. You can do this by clearing out the execution plan cache and data cache, though not recommended doing on production server:

DBCC DROPCLEANBUFFERS -- Clear data from cache
DBCC FREEPROCCACHE -- Clear plan cache

Answer 3

I'll go against some political winds here and say...

If you don't need 16-bit Unicode capabilities, use varchar(MAX) instead of ntext. Using varchar(MAX) instead of nvarchar(MAX) will cut the storage and transfer time for the text field in half.

If you are seriously network IO-bound (example: your "text field" is in the megabyte-range), you might consider using data compression: store a zipped version of the text in a varbinary(MAX) field rather than text. Thus, the shuffling of results over the network is all compressed. Compressing/uncompressing text is relatively straightforward in .NET and CPU cycles are usually cheap.

(SQL Server 2008's new "compression" features really wouldn't help here... the traffic over the wire would be the same.)