Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-10 : 14:57:10
|
Jonathan writes "Dear Team:
I scoured the manuals and books on line; as well as your site and others. My question precisely has to do with the *advisability* of splitting a Very Hugh Table into lots and lots and lots and lots and lots and lots of Itty-Bitty Ones.
I have a simple application that I plan to offer on the web to all comers. Everyone gets a sign-on and a data space for his data. My gut feeling was that I should but everyone's data in a separate table. (Part of me -- the superstitious part that doesn't really trust machines -- feels this would be more secure since individual user's data are supposed to be confidential and even one slip-up could ruin my credibility.) But I also feel this would be somehow more manageable when I get that 10 millionth member a few months down the line. (We all should be so lucky.) I know I can have 10 million tables in one database file, but THE QUESTION IS:
Can SQL Server find the table easily?
Shall I assume SQL Server's internal mechanism for finding tables is based on the engine itself, perhaps using one of those sys tables to locate my Itty-Bitty table for me from out of the multitudes using some sort of indexed scheme? (I need to hear you tell me SQL Server does not SCAN for the table!) So that if that's true, assuming balanced binary trees, there really should be no additional over head (no significant increase in number of actual comparisons) between searching for user 3298907's unique record among his 500 records (i.e., the 500 records with UID=3298907) in Humongo table vs. locating table T3298907 and then locating unique record within the 500 records in that table. That is, log2(500 x 10 million) = log2(10 million) + log2(500). Right? That is:
Can I assume that there's no efficiency hit for locating data with splitting tables up on this scale?
So if that's true (just as efficient one way or the other), maybe I shouldn't bothering doing this? There is some extra code, for sure. Are lots of small tables in a big database file easier to maintain than one large table? I know little about maintaining huge databases? I assume you can split a single file across multiple disks if it comes to that. (You can, can't you?) But as an alternative, would it be easier/wiser to put some Itty-Bitties on disk A, some on B, some on C, etc?
So, to h-part or not to h-part?
One more thing. I assume creating a single view that UNIONs 10 million tables is pretty much out of the question. (Just the SQL statement itself would be too big for the machine to swallow!) But that's all right in this case because I would only be interested in UNIONing a few (say, 10 at most) tables together -- under special circumstances.
I am putting further development on hold until I can make a decision on this.
Thanks for your help.
Jonathan
River Vale, NJ" |
|
|
|
|
|
|
|
Practical limits on horizontal partitioning