VARCHAR (3500) slower than TEXT?

[rrhandle]

I have a table in which 2 of the fields are defined as TEXT. It was determined that neither of these fields would have to hold more than 3500 characters, so I changed both fields to VARCHAR (3500). The problem is that any query against this table takes 20 seconds to run. When the fields were defined as data type TEXT, queries ran lick-itty-split!? I would have expected just the opposite. Can someone explain this behavior?

 

[ESquared]

Text data is not held in the same physical location as varchar data unless you have the textinrow option turned on. Varchar is put right in there with the row, whereas text data is held in its own repository. I don't remember the technical details, you can find them in the SQL server help files, Books Online (BOL).

If you just changed a text column to varchar, you'd do well to rebuild and reindex the table before trying any performance tests as you probably had a godawful lot of page splits and so on.

Keep in mind that the total number of bytes in a row can never exceed 8060. With two 3500-character columns, you're left with 1060 bytes.

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E

 

[rrhandle]

ESquared, thanks for that prompt response. Your explanation makes perfect sense, and that is what I thought I would hear back. Maybe you can explain where I went wrong. It was my understanding from reading an article on perfomance tuning that if a TEXT field is going to hold less than 8000 characters, you should consider using varchar. Is that correct? Is the article wrong?

 

[ESquared]

I'm sure the article is likely correct, but have you defragged the table and its indexes? You must do this before your performance tests will be meaningful.

 

[rrhandle]

I am creating the table from scratch, then filling some of the key rows that link the table to other tables. None of the TEXT fields have anything in them.

 

[rrhandle]

I should add, the query is a SELECT * with no WHERE clause.

 

[ESquared]

I can't explain it, then. You'll need someone with more knowledge than I.