473,223 Members | 1,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,223 software developers and data experts.

One Database vs. Multiple Databases

TC
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

Jun 20 '06 #1
9 14200
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/m...S,SQL.90).aspx
--

Jun 20 '06 #2

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


I would go with 1 database per projet (so multiple databases):
- if your data model change, you will be able to migrate only projets
that you want, when you want.
- easier to separate projet, restart a projet, etc if you need.
- backup/restaure projet independantly
- Give acces to a particular projet to a user is easier.

Jun 20 '06 #3
I would go the other way - one database for all. It will more scalable
and flexible. The amount of data is not too much, the speed is not an
issue as long as it is properly indexed.

I actually did a data conversion merging several hundreds of databases
(Also called project) into one.

Jun 20 '06 #4
TC (go*********@yahoo.com) writes:
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.


Whether to use one or many databases has nothing to do with performance
whatsoever. If performance is the only motive for you to consider
separate databases, just forget about it given the volumes you indicated.

There may be other reasons for using separate databases. One project
says "oops, we deleted our data". With a separate database, a restore
is a quick thing. Or some projects may start to call for diverging
requirements, so that they no longer fit into the same model. There
can also be security considerations.

But all of that business requirements that are unknown to me. Since
maintaining 50 databases with the same model requires more overhead,
a single database with a good data model is a good way to start.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 20 '06 #5

"TC" <go*********@yahoo.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.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

Jun 21 '06 #6
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.


Others have addressed, both pro and con using a single or multiple
databases and my response would be that the consideration is one
of maintenance and security: Two issues you have not discussed.

But what I would like to add to this discussion is based on your
first two sentences.

From what you've written I can't see how you can justify, except
for security purposes, more than one set of tables with the same
data model. I think Date and Codd said something about it so you
might want to read what they wrote. But based solely on the above
sentences ... the correct solution is to add a column to your
tables named PROJECT_ID.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Jun 21 '06 #7
As everybody has mentioned, 500k records and 100 users is small by any
definition. But regardless, performance should not be a consideration
until it forces itself to become so.

Think about your idea from a maintenance perspective. At some point
you'll need to add a column to one of those tables, or even make a
simple stored procedure change. Imagine the pain this will cause you
down the road, trying to synchronize changes in all those databases.
Multiply every small hassle you'll ever come across in the future by
the number of "Projects" in your universe. Yikes!

Stick to one database per Application and you'll live a long and
healthy life.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

Jun 22 '06 #8
I recently recommended a multiple database solution, since my
assessment of the client's needs and data were that they needed the
flexibility of separate databases per data set. Also, my deadline for
completion was very short, and this product was not considered to be
used for longer than the near future.

Each external client's (about 20 clients) data set is different - even
the same client data set could vary - and the process was to massage
each set of data via stored procedures. I Initially opted for one
database per client, to allow for reuse of lookup data (holidays,
fiscal periods, accounts, etc.), but later realized that what I was
designing, an Excel workbook to analyze accounting data, would be
easier to modify if I kept everything the same and simply modified the
connection string.

Ultimately, the choice of multiple servers keeps the stored procs for
the XLW, and the XLW itself, the same, while the only modification
occurs in the stored procedures for building the data, since the data
requirements could vary wildly. The multiple database approach also
allows for multiple data sets to be massaged at the same time -
building the final data set could take several days, so some problems
in concurrency could develop - and with varying procedures for such the
independence was a benefit, albeit creating a lot of redundancy.

Given enough time to design a solution that preserved security - no
client specific information, nor information useful for hacking the
server data could be local, other than the connection string - and ran
as a single database, I could certainly design a solution for them.
James Igoe

ja********@gmail.com || http://code.comparative-advantage.com
Jason Kester wrote:
As everybody has mentioned, 500k records and 100 users is small by any
definition. But regardless, performance should not be a consideration
until it forces itself to become so.

Think about your idea from a maintenance perspective. At some point
you'll need to add a column to one of those tables, or even make a
simple stored procedure change. Imagine the pain this will cause you
down the road, trying to synchronize changes in all those databases.
Multiply every small hassle you'll ever come across in the future by
the number of "Projects" in your universe. Yikes!

Stick to one database per Application and you'll live a long and
healthy life.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/


Jun 22 '06 #9
TC
I want to thank everyone for their thoughtful responses. You've helped
me get perspective on this issue.

-TC

Jun 22 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: cover | last post by:
If you're writing many databases that aren't necessarily associated with each other (ie parts, vacation days, how you like your steak done, and school you attended, etc; as examples), does it make...
3
by: php newbie | last post by:
We are negotiating with a vendor and we have a few questions: 1) From a licensing point of view, what is a database? Can we install multiple copies of SQLServer on one box? In that case, would...
4
by: mike | last post by:
Hi, I am trying to determine what the overhead is per database in SQL Server 2000 Standard. I have the option to put several customers in one database, or give each customer their own database....
7
by: Randy Yates | last post by:
I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes,...
3
by: rallykarro | last post by:
Hi, How do I at the best way perform select statements over multiple databases? I have a couple of databases containing the same table definitions with diffrent data. Now I want them to act...
32
by: David Isaac | last post by:
I have no experience with database applications. This database will likely hold only a few hundred items, including both textfiles and binary files. I would like a pure Python solution to the...
9
by: Peter Duniho | last post by:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example,...
9
by: Bunty | last post by:
I am not beginer of this language. I've worked on this language for 2 or 3 months.But now i have a problem to inserting values in the database.It gives me error that DATABASE NOT SELECTED. I am...
25
by: pereges | last post by:
Hello, I'm trying to build a database driven website for a library management system. The database is stored on a remote server which all of my team mates can access. I've installed MySQL, PHP and...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.