servers and partitioned view?

Hi,
I have 2 database servers running W2K/SQL 2000 with Gigabit Ethernet between
them. Server1 contains 2003 and 2002 data in one database. Server2 contains
2001 data in one database.
I have an web application that points to Server1 all the time. So, I created
a linked server from Server1 to add Server2 in. I then created Server1.VIEWS
of all tables from Server2.database.dbo.table_name...
The problem is that accessing data via Server1.VIEWS from the web
application is very slow. Reports aren't performing for 2001 data
physicially stored on Server2. The reports aggregate millions of rows on
each table.
what are ways to improve query performance via linked servers? I still want
to keep one web application pointing to one physicially server, if possible.
Thanks!
HHIf you haven't done so already, run a trace against the database and try to
index the tables on server2 based on the trace.
For some of the more common reports, if the data is aggregated maybe you can
bypass some of the work SQL Server has to do by creating tables with the
aggregated data in it.
You could try a solution using Analysis Services (this solution would
require a lot of FTE hours)|||Our relational reporting systems works great when the data is local. Reports
usually run within seconds. Only the set of data physically stored on
another machine via linked server performs severely. I didn't expect it to
be this slow because the two machine are on the same Gigabit Ether LAN. It
could be because the query is running on Machine1, but it has to pull all
data from Machine2 over first' It'd be ideal if queries are being run on
Machine2, and only results get transferred over the wire.
Thanks!
HH
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:eZhKrsJmDHA.964@.TK2MSFTNGP10.phx.gbl...
> If you haven't done so already, run a trace against the database and try
to
> index the tables on server2 based on the trace.
> For some of the more common reports, if the data is aggregated maybe you
can
> bypass some of the work SQL Server has to do by creating tables with the
> aggregated data in it.
> You could try a solution using Analysis Services (this solution would
> require a lot of FTE hours)
>