If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Put large field in a table of its own?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-17-10, 05:28
PaulMolloy PaulMolloy is offline
Registered User
 
Join Date: Jun 2010
Posts: 9
Put large field in a table of its own?

I've got an SQL Server 2008 table of about 10k records with maybe 20 fields which are mostly integers and bits but has a description field that's varchar(max) and not called all that often compared to the rest of it.
Is there any performance gain to be had in putting that field in a linked table of its own as opposed to leaving it all in one table and just not including it in select statements?
Reply With Quote
  #2 (permalink)  
Old 06-17-10, 05:33
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
Depends on the actual data in the column, however if it is populated with lots of data for lots of rows then the answer is yes. You have basically described a technique called horizontal partitioning.
Reply With Quote
  #3 (permalink)  
Old 06-17-10, 05:34
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
BTW - empirical knowledge is always rewarding - why not try testing the two designs with sample data? Use REPLICATE() to create some large text values and use SET STATISTICS IO ON to see the effects.
Reply With Quote
  #4 (permalink)  
Old 06-17-10, 05:42
PaulMolloy PaulMolloy is offline
Registered User
 
Join Date: Jun 2010
Posts: 9
Thanks, I'll try that.
I just wanted to know if I was wasting my time with something the database was already doing transparently.
Reply With Quote
  #5 (permalink)  
Old 06-17-10, 05:52
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
No, you won't be. Your IO efficiency is essentially determined by how many rows you can fit on a page related to how much of the data in the rows you require to satisfy queries. If you have lots of text in each row then you have less rows per page. To read a row, SQL Server has to read the entire page irrespective of whether or not it actually needs that entire page's data.

So for the scenario you are describing moving the VARCHAR data into another table and only reading it when necessary will allow more rows per page in the main table.

Of course, if you are only retrieving single rows at a time then the this greatly reduces the advantage of this design. If you report on this data and return many rows, especially contiguous rows, then it is very efficient. I probably should have mentioned that too originally.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On