Splitting a table - a good idea?
-
Hi experts!
Straight to the point, I have a table like this:
Messages
msgID
SenderID
RecipentID
Subject
DateSent
Status
Message (text which can be up to 50,000 chars long)
Now, should I split the table into three smaller tables? Because I suspect that the Status, Recipent and Sender columns will be scanned almost all the time. So, basically my question is: Does SQL Server care whether there are 7 columns in a table when it scans only 3 of them? Is there a difference in performance?
Thanks!
/Tomi
-
I'd leave as is. The message is in a text col and so stored separately, rest of the columns are pretty narrow. I think it should scale well.
Andy
-
Thanks for the reply!
Great! I can leave it like that. Now I don't have to write triggers
/Tomi
-
Make sure you have proper indexes and you should be just fine. It would be better to look at spanning drives before splitting tables if you can but unless you have monster sized tables you should be fine and then good indexes make all the difference.
-
Thanks Antares,
How many does a table need to have before I can call it monster sized?
10 Million rows and 200 columns? Oh, and to avoid any misunderstandings, what I meant by splitting the table was to divide the colums (not the rows) into three smaller tables.When should I consider splitting tables? Are we talking 50 or 500 columns?
/Tomi
-
Ah, I was thinking rows not columns. Never split a table on columns unless you cannot fit it in 8K wide (not counting text).
-
Thank you! Excactly what I wanted to hear.
-
quote:
...Never split a table on columns unless you cannot fit it in 8K wide (not counting text)...Never? I would think there would have to be situations where splitting the table into main/supplemental tables can have advantages...
If you split a table into main info and supplemental info, technically, you can have 2 clustered indexes for a single entity, right...?
-
Good point!
But in my case I don't think I'll need any clustered indexes (except the PK). And doesn't having to JOIN the two tables also cause a cut in performance?
/Tomi
-
I'm not sure, but I would find it unlikely since you'd be joining on, presumably, an indexed foreign key field relationship. Just another option, I guess. Good luck!
-
Splitting a table can have advantages.
If it is a table that has several fields that are frequently read
but never updated and other columns are read and updated.
I ran into situations where only a few fields were updated and
a lock on the entire record would have impact on other users.
The table was split and only the sprocs were modified, the
locking problems were solved.
I know this is only an example but things like this can happen.
-
Tomiz,
You might want to look at establishing a "covering index" for your three hot columns. It would depend on how you filter the retrieved data (WHERE clause). See BOL for details.
HTH
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15 -
Thank you! This was very helpful.
-
Of course you could think about splitting table horizontally and vertically.
If you have tables with too many columns you could try too split them b yfrequently used and less frequently used columns. (Vertical splitting)
The goal is here to fit as many rows as possible within one page (8K).
The other possibility to speed up your queries is to put rows corresponding to a specific criterie to a different table, like archive table (Horizontal split).
Here you could use a partitioned view to make it transparent for the users/applications
All this should be considered carefully before any decision will be taken
Bye
Gabor -
Tomiz,
Just to speed up your queries you could considere to put the text column on a different filegroup which is on a separate physical disk with the TEXTIMAGE_ON filegroup clause of the CREATE TABLE statement
Bye
Gabor
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply