Multiple databases

[Chuck]

Hi,

What is the best way -- performance wise -- to handle data from multiple
databases if each database has several million records in a few tables.

One option is to create a single database and append all the records from
individual databases to that database. The other option is to create views
that span multiple databases. Which option will give me better performance --
everthing else being equal i.e. same server, same network, etc.

Thanks,

Chuck

[Eric Russell]

Are you talking about unionizing several partitioned tables or just joining
tables?
A view would potentially improve performance, if it were an indexed view,
but indexed views can't span databases.
If the tables are contained in seperate file groups on seperate disks
(whether they be in the same database or seperate databases), then you will
potentially benefit from parallel I/O. Rather than changing the logical
schema of your databases, first try placing each DB filegroup on seperate
disks and see if that improves things.

Post by Chuck
Hi,
What is the best way -- performance wise -- to handle data from multiple
databases if each database has several million records in a few tables.
One option is to create a single database and append all the records from
individual databases to that database. The other option is to create views
that span multiple databases. Which option will give me better performance --
everthing else being equal i.e. same server, same network, etc.
Thanks,
Chuck

[Chuck]

Eric,

Thanks for your response. Yes, these are separate databases that reside on
the same disk. They are data packages.

I guess the main thing that I'm trying to get to is whether it's a better
idea to import the data into a single database or try to access the data from
separate databases which contain data for different time frames.

Thanks again for your help.

Post by Eric Russell
Are you talking about unionizing several partitioned tables or just joining
tables?
A view would potentially improve performance, if it were an indexed view,
but indexed views can't span databases.
If the tables are contained in seperate file groups on seperate disks
(whether they be in the same database or seperate databases), then you will
potentially benefit from parallel I/O. Rather than changing the logical
schema of your databases, first try placing each DB filegroup on seperate
disks and see if that improves things.

Post by Chuck
Hi,
What is the best way -- performance wise -- to handle data from multiple
databases if each database has several million records in a few tables.
One option is to create a single database and append all the records from
individual databases to that database. The other option is to create views
that span multiple databases. Which option will give me better performance --
everthing else being equal i.e. same server, same network, etc.
Thanks,
Chuck