January 14, 2008 at 1:48 am
I have this kind of situation:
I have Message table, that contains content field (and 14 other fields). Content field contains (large) binary data.
90% of messages are smaller than 1000 bytes, 96% smaller than 2000 bytes, 99% smaller 3000 bytes. There is also some messages, whose length exceeds 8000 bytes.
So the problem is, which way is the most sensible? To store all data in one varbinary(max) field? Or to store messages in this way: Messages whose length are lower than (for example) 2000 bytes, to Message-table's varbinary(2000) field, and larger messages to LargeMessageContents-table, that would contain only content-field (+messageId-field of course).
Or is there another more effective way?
This is important question, cos Message table will contain very much messages. (almost 10000 new messages arrives each day and messages will be stored 3 months)
Thanks in advance,
-Tommi
January 14, 2008 at 3:29 am
Much depends on how frequently you want to access the binary data, and how many rows you want to squeeze in to your priamry data pages.
Smaller blobs can be stored in row automatically (so there is no need to create different columns for different sizes). So smaller BLOBS can be stored automatically as a varbinary(8000) column if they can be fit on a page, and as an out of row tree structure otherwise. You can control this behavior via a table option 'large value types out of row'.
See Books online "In-row data" http://msdn2.microsoft.com/en-us/library/ms189087.aspx
Regards,
Andras
January 14, 2008 at 8:49 am
Thanks for replying!
I have already read that document, but it didn't answer my question completely. (Am i stupid or something?) 😉
Andras Belokosztolszki (1/14/2008)
Much depends on how frequently you want to access the binary data, and how many rows you want to squeeze in to your priamry data pages.
I have to access that data fairly often, cos users are able to search messages by their content. (I just realized that perhaps I should use varchar instead of varbinary. In other case I should convert varbinary to varchar for search queries) Still, it is the most important that insert queries are fast in any case.
I have no any requirements in how many rows I want to squeeze in data pages (I don't even understand completely what do you mean). But anyway, I'm only interested in performance of insert/search queries in this case.
So what do you (or someone else) think, does it make any sense to split these messages to two tables? Or should I use one table and varchar(max) column with in row -option?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply