|
|
Posted by David Portas on 06/20/06 20:27
TC wrote:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
>
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.
>
> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
>
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.
>
>
> -TC
1. Not unless your implementation is very bad indeed. 100 users and
500,000 rows is a small database by most standards.
2. Sometimes. Partitioning a database can make sense for administrative
and support reasons or as part of a solution where data is distributed
over multiple servers. But without other changes, partitioning a
database isn't likely to achieve much if anything in terms of
performance. Given the potential complexity of supporting that kind of
solution there are certainly much easier and more effective ways to
optimise performance.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|