How much more inefficient are text (blobs) than varchar/nvarchar's?

Question

We're doing a lot of large, but straightforward forms for a fairly big project (about 600 users using it throughout the day - that's big for me at least ;-) ).

The forms have a lot of question/answer type sections, so it's natural for some people to type a sentence, while others type a novel. How beneficial would it be to put a character limit on some of these fields really?

(Please include references or citations, if necessary/possible - Thanks!)

Solution 1

If you have no limitations on the data size, then why worry. This doesn't sound like a mission critical project, even with 600 users and several thousand records. Use CLOB/BLOB and be done with it. I have doubts as to whether you would see any major gains in limiting sizes and risking data loss. That said, you should layout such boundaries before implementation.

Usually varchar is best for storing values that you wish to use logically and perform "whole value" comparisons against. Text is for unstructured data. If your project is a survey result with unstructured text, use CLOB/BLOB

Semi-Reference: I work with hundreds of thousands of call center records sometimes where we use a CLOB to store the dialog between employees and customers.

Solution 2

I say, focus on the needs of the users and only worry about database performance issues when/if those issues arise. Ask yourself "will my users benefit if I limit the amount of data they can enter".

I keep a great gapingvoid cartoon on my wall that says "it's not what the software does. it's what the user does".

Solution 3

You don't mention which sql server you are using

If you are using MySql there are definite advantages in speed to using fixed length fields to keep the table in static mode, however if you have any variable width fields the table will switch to dynamic and you lose the benefit of specifying the length of the field.

http://dev.mysql.com/doc/refman/5.0/en/static-format.html
http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html

Microsoft SQL Server has similar performance gains when you use fixed length columns. With fixed length columns the server knows exactly what the offset and length of the data in the row is. With variable length columns the server knows the offset but has to store the actual length of the data as a preceding 2byte counter. This has a couple of implications that are discussed in this interesting article that discusses performance as a function of disk space and the advantages of variable length columns.

If you are using SQL Server 2005 or newer you can take advantage of varchar(max). This column type has the same 2GB storage capacity of BLOBs but the data is stored in 8K chunks with the table data pages instead of in a separate store. So you get the large size advantage, only use 8K in your pages at a time, quick access for the DB engine, and the same query semantics that work with other column types work with varchar(max).

In the end specifying a max length on a variable column mainly lets you constrain the growth size of your database. Once you use variable length columns you lose the advantage of fixed size rows and varchar(max) will perform the same as varchar(10) when holding the same amount of data.

Solution 4

blob and text / ntext are stored outside of the row context, and only a reference stored to the object, resulting in a smaller row size, which will improve performance on clustered indexes.

However because text / ntext are not stored with the row data retrival takes longer, and these fields cannot be used in any comparison statements.

Solution 5

from: http://www.making-the-web.com/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

There are a few variations of the TEXT and BLOB types which affect size; they are:

Type -                      Maximum Length -Storage
TINYBLOB, TINYTEXT          255             Length+1 bytes
BLOB, TEXT                  65535           Length+2 bytes
MEDIUMBLOB, MEDIUMTEXT      16777215        Length+3 bytes
LONGBLOB, LONGTEXT          4294967295      Length+4 bytes

View more solutions

Comments

[Ady]

Sorry, I was speaking of SQL server. You can convert back to n/varchar but the expense of doing this, combined with the risk of data loss means that it would not be worth it.

[Tom H]

Nevermind... by comparison I'm going to guess that you mean strict comparators, which do not work.

[onedaywhen]

In SQL Server, CHECK constraints cannot be created on columns of type TEXT and NTEXT.