several databases or one big database?

[tonywilliams]

Hi

I am currently writing an application that links to 3 different databases. There are historic reasons for this, but also the data is grouped relevantly. As part of the application I have one query that needs to link to all 3 databases for data. This is the only query that does, but its quite a major one. The problem I am having is that as this is done over a network the performance is very poor.

I am now looking at ways around this and, one thought was to merge the data into one big database. Although this would still come under the maximum size of an access (2003) database (2gb?) it would mean i would have around 20 tables. Which as a new developer seems quite a lot?. Would this then harbour the performance of a single database to a point where I should stick with the 3 databases?

By sticking with one big database also means i would have more connections at one time to one database as opposed to the connections being split between the three. Again would this cause performance issues?

Any thoughts?

Kind Regards

 

[Arg81]

I merged 4 databases into 1.

Although 2 of them were "global" lookup databases such as customer and employee, they allowed me to use them in various applications I had created.

When I decided to upgrade the applications to .net 2.0, I wanted both apps to be "as one". Therefore I also merged (well I recreated and then imported) both databases.
The 2 lookup databases were brought in as well.

The database now has 15 tables, which isn't really a lot, and not all tables are linked together. In fact I have 3 different "relationship" designs.

After merging, and although I use SQL and not Access, my DB datafile is only 6Mb

If there will be no repercussions of you merging the databases together, I can't see any reason as to not doing it.
At least it will make your "major" query perform better.

 

[cjard]

i would have around 20 tables. Which as a new developer seems quite a lot?

Come develop at my place then. One of the Oracle based apps here has 753 tables, some with more than 2 million rows

Would this then harbour the performance of a single database to a point where I should stick with the 3 databases?

No

By sticking with one big database also means i would have more connections at one time to one database as opposed to the connections being split between the three. Again would this cause performance issues?

What's the difference?