Printable View
I have a fairly small table, approx 100,000 records. It has one ntext column, which has data ranging from 50k to 250k.
Doing a select for a fairly small number of records (using an index) that includes the ntext column, can take 30 seconds.
Help!
-Ken Williams
How large are columns?. How is the performance without including ntext column in the resultset?.
Thank you for responding.
The table has blog entries, most of which run around 50k, but some of which as are large as 280k. Currently there are approximately 100,000 blog entries. There are a few other columns on the records, but nothing interesting. All colums except the blog html column itself are simple columns like, "Date Posted, entry Id, Title, Author"
I've done experimental queries and it is definitely the ntext field, the blog entry itself, that is clobbering the stats (running extremely slow).
Through googling I found a message saying that Ntext is deprecated in SQL and to use nvarchar(max) instead. I changed the column type on my local database, and the database is working fine. I don't seem to have clobbered any data by having made the change, although I'm worried about foreign language characters in the data. I still need to verify that nothing is being lost. My local database is small, and I want to wait until after tonight's backup to apply this change to the larger database.
My hope/theory is that Ntext is implemented poorly in SQL and that when I change to nvarchar(max) the performance will increase dramatically.
Fingers are crossed.
If anyone reading this thinks of any side-effects to swapping the columns from ntext to nvarchar that I'm not thinking of -- post here quick. You'll save me a lot of grief (and, our customers a bad day).
Thank you,
Ken W
Tried with full text index?