|
|
Posted by Greg D. Moore \(Strider\) on 06/21/06 04:15
"TC" <golemdanube@yahoo.com> wrote in message
news:1150833955.445569.42840@h76g2000cwa.googlegroups.com...
> 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.
This is a small database by today's standards.
In general a single database will probably give you better performance since
only one copy of query plans will be cached, as opposed to 50 (assuming you
use stored procs, etc.).
disk I/O will probably be less as SQL can do a better job of reading in
batches of rows.
So performance-wise, single probably wins out.
In terms of maintenance, etc, a single one is generally better. Assume you
develop an updated version of a stored proc, or need to change a table.
Would you rather do it once or 50 times?
>
> 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
>
Navigation:
[Reply to this message]
|