user2189331
user2189331

Reputation:

Should database variable-length text fields be powers of 2?

A friend of mine claims that in a typical database, using (for example) nvarchar[256] will give marginally better performance than nvarchar[200] or nvarchar[250] because of the granularity of page allocations.

Is there any truth to this whatsoever?

Thanks!

Upvotes: 5

Views: 652

Answers (5)

myplacedk
myplacedk

Reputation: 1564

Think of it this way: If nvarchar[256] performs better than nvarchar[200], wouldn't the DBMS just make an nvarchar[256] when you ask for an nvarchar[200]? (But still make it look like nvarchar[200])

Good DBMS's have some extremely advanced optimizations. I'm pretty sure they have all the simple ones too.

Upvotes: 1

Andrew G
Andrew G

Reputation: 2496

I wonder if the friend of yours somehow arrived at his conclusion on his/her own or if this was a case of myth-propagation.

There's a great presentation by Tom Kyte on "Things you know" that pretty much everyone should watch before making claims like the one above: Things you know

Upvotes: 1

Sean Reilly
Sean Reilly

Reputation: 21836

This is not true. Tables are allocated on disk in 8k pages. When a table is read from disk, the entire page is read in one IO operation and stored in memory. Therefore, the length of a column will not affect memory alignment at all. In fact, with non-variable length data types, shorter is definitely better: an nchar(200) column will allow more rows per page than an nchar(256) column. This allows more rows to be read per single physical IO, which can have a dramatic affect on database performance.

Upvotes: 15

Rob Walker
Rob Walker

Reputation: 47502

If anything it is probably worse because of allocation overhead. When you allocate nvarchar(256) the database likely includes a couple of bytes for the length, so the storage requirement may actually be 258.

There are so many levels of abstraction at play here that you are not going to see any benefit by trying to optimize at the top for something that only matters at the very bottom, and you might just make things worse!

Upvotes: 5

MusiGenesis
MusiGenesis

Reputation: 75396

No, there isn't.

Upvotes: 5

Related Questions