Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BlackKnight
Starting Member
8 Posts |
 Posted - 2009-03-20 : 05:36:02
|
| Hi,We have a table in our database with a nvarchar column.It used to be nvarchar(4000), but we needed to store more text, such as a web page, so it was converted to nvarchar(max).However, inserts into this table are now taking upto 10 times longer! Nothing else has changed.The other columns are (id (PK), int), (ModifiedAt, DateTime), (PageNum, int not null), (SiteId (FK), int).The table grows by about 100,000 rows per day.The time taken to insert (which we do using a sp) is varying from between 60ms to over 5s, and is usually over 1s.Should there really be such a performance degredation simply by increasing the column size? Any help appreciated.Thanks |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-03-20 : 06:52:05
|
| When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored... |
 |
|
|
|
|
|
|
|
Performance drop nvarchar(4000)-->nvarchar(max)