CODEWITHSUNDEEP
sql-server
Nikola Radosavljević
Nikola Radosavljević

Reputation: 6911

Structuring database table with large text field

I'm looking for an advice about structuring a data table as in title to make it efficient for querying and writing. I store information about an entity which has usual data types, numbers, short string etc. Now I need to store additional field with large amount of data (~ 30 KB) and I'm looking at two options:

  1. add a column an nvarchar(100000) in the entity table
  2. create separate table to store such data and link from the entity table

other factors:

Right now I'm leaning toward having a separate table, but it also has a bad side in that I have to remember some concerns about keeping data consistent.

It's hard to make a decision without doing a real benchmark, but this could require few days of work so I'm turning to SO for a shortcut.

Upvotes: 3

Views: 2435

Answers (2)

Eoin Campbell
Eoin Campbell

Reputation: 44268

We recently had this exact problem. (though it was an XML Column instead of an NVarchar(max)) but the problem is the exact same.

Our use case was to display a list of records on a web page (the first 6 columns) of the table and then to store a tonne of additional information in the nvarchar(max) column which got displayed once your selected an individual row.

Originally a single table contained all 7 columns.

TABLE 1
    INT ID (PK IDentity)
    5 other columns
    NVARCHAR(max)

Once we refactored it to the following we got a massive perf. boost.

TABLE 1
    INT ID (PK IDentity)
    5 other columns
    INT FID  (FK -TABLE2)

TABLE 2
    FID (PK IDENTITY)
    nvarchar(max)

The reason is that if the nvarchar(max) is short enough, it will be stored "in-row" but if it extends beyond the page size, then it gets stored elsewhere, and depending on a) the size of the table and record set your querying, and b) the amount of data in your nvarchar(max) this can have a pretty dramatic perf. drop.

Have a read of this link: http://msdn.microsoft.com/en-us/library/ms189087.aspx

When a large value type or a large object data type column value is stored in the data row, the Database Engine does not have to access a separate page or set of pages to read or write the character or binary string. This makes reading and writing the in-row strings about as fast as reading or writing limited size varchar, nvarchar, or varbinary strings. Similarly, when the values are stored off-row, the Database Engine incurs an additional page read or write.

I'd bite the bullet now, and design your tables to store the large nvarchar(max) in a seperate table, assuming you don't need the data it contains in every select query

With regards, your comment about using an ORM. we were also using NHibernate in our situation. It's relatively easy to configure your mappings to lazy-load the related object on demand.

Upvotes: 4

TomTom
TomTom

Reputation: 62093

Well, you could start with documentation...

add a column an nvarchar(100000) in the entity table

Given the documented max size of 8000 bytes for a field and thus nvarchar(4000) being the maximum, I am interested to know how you consider this an option?

nvarchar(max) - ntext etc. would be the right thing to do.

And then you should read up on full text search, which is in SQL Serve pretty much for ages. Your ORM likely does not support it though - technology choices limiting features is typical when people - have a problem abstract things. Not something I would access with an ORM.

Upvotes: 1

Related Questions