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 |
|
dzCepheus
Starting Member
4 Posts |
 Posted - 2006-03-08 : 23:29:14
|
| Hi there,I'm using SQL Server 2005 and my group is currently in the middle of designing a database schema for an application we're working on - basically just getting the main points down before we drive deeper into the details.Other details notwithstanding, our schema so far calls for two tables: one to hold one set of information, and another to hold lists of information relating to the first table. (Sorry for the vaguarities - I'm not allowed to disclose the nature of our project for security reasons.) Here's the basic topography:Table 1: EntryID some_entry_data_1 some_entry_data_2 etcTable 2: ParentID -- relates to Table 1's EntryID some_entry_data_1 etcTable 1 is expected to be sorted and displayed frequently (taking advantage of paging of course) - but Table 2 is only going to be displayed relative to a single record of Table 2.Basically - table 1 will be displayed on a page, and when the user clicks on an entry in table 1, the application will pull the entries from table 2 whose parent ID == the Entry ID for the selected entry.Regarding the usage of the tables: We expect table 1 to hold a great deal of entries, and to be searched over, displayed, and sorted on a very regular basis by a large number of users. Table 2, we expect, will have of course a great many tables, but the only operations we expect to employ are infrequent updates, and selects over a very tiny subset of the data.A question came up recently about the performance of our database assuming a very large number of entries in table 1 - say, 100,000 entries. If each entry in table 1 has, say, 5 related entries in table 2, that means table 2 has to have 500,000 entries.The only other recourse I can think of to mitigate the performance penalties associated with having such large tables is to allow each entry in Table 1 have its own table. I understand this is called Horizonal Partitioning.My question (after this long preamble) is: which way should our team head in? Do we stick with the two monolithic tables, or do we split the second table into a myriad of client-generated tables? Or are there some other options we could implement that may help in this area?I appreciate any help anyone can give me, and if anything is *too* vague in my description please let me know and I'll try to explain as best I can. Thanks. :) |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 01:15:52
|
| Hi dzCepheus, Welcome to SQL Team100,000 to 500,000 rows in a table should be a breeze for SQL Server, so I don't think you need to make any special provisions. You may need to tune your queries - making sure that appropriate indexes are in place, and that the Clustered Index is on the most appropriate column, but this isn't a "big" table by SQL Server standards!Decent hardware wouldn't do any harm either!Will you have more than 100 concurrently connected "clients"?Kristen |
 |
|
|
dzCepheus
Starting Member
4 Posts |
Posted - 2006-03-09 : 01:19:46
|
quote:
Originally posted by KristenWill you have more than 100 concurrently connected "clients"?
Hi Kristen, thanks for the welcome. :)No, I highly doubt it. This is going to be a web application, and each user's connection to the database should only live for as long as it takes the ASP.NET webserver to process their request.So you're saying that I can have my two monolithic-type tables and be okay? That is definately a relief. :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 01:25:27
|
| "each user's connection to the database should only live for as long as it takes the ASP.NET webserver to process their request."Hehehe ... I'll rephrase my question then!What sort of maximum "active" client count will you have?Web application, open to Joe Public, suggests potentially large numbers of users busy using the application."So you're saying that I can have my two monolithic-type tables and be okay?"I would be happy with a Parent-Child table pair with 100,000 and 500,000 rows.But there are badly-designed tables/systems as well as good ones of course! And:"Table 1 is expected to be sorted and displayed frequently (taking advantage of paging of course"is open to good as well as very bad! methodsKristen |
|
|
|
dzCepheus
Starting Member
4 Posts |
Posted - 2006-03-09 : 01:31:06
|
By maximum active, you mean the max number of people we expect to be using the web app at a given time? To be honest I'm not 100% sure. Of course we hope for as much exposure as possible but that's kind of up to the users themselves. ;) Basically we want to develop this thing in the hope that it can sustain a large amount of growth post-release.quote:
"Table 1 is expected to be sorted and displayed frequently (taking advantage of paging of course"is open to good as well as very bad! methods
What do you mean by this? Can you extrapolate? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 05:39:38
|
| "By maximum active, you mean the max number of people we expect to be using the web app at a given time?"That's the one. Doesn't really matter too much, just a factor for capacity planning, load testing, and then query tuning! It will influence how beefy your hardware needs to be.Kristen |
|
|
|
dzCepheus
Starting Member
4 Posts |
Posted - 2006-03-09 : 06:01:03
|
| Thanks a lot for your input Kristen, it helps a lot. :) I'll bring this up to the group and see what we come up with. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 12:36:20
|
| "I'll bring this up to the group and see what we come up with."Mine's a Pint please!! |
|
|
|
|
|
|
|
|
Mitigating performance penalties of large tables?