Multiple databases

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

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 Database filegroup on seperate
disks and see if that improves things.

"Chuck" wrote:

> 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,

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.

"Eric Russell" wrote:

> 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 Database filegroup on seperate
> disks and see if that improves things.
>
> "Chuck" wrote:
>
> > 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