RE: performance hit when using accessing data on multiple servers

Ciaran,

I did as you suggested and moved the view to the server that most of the
records reside on and am thrilled to say that the performance is back.

Thank you so much,
Jackie


"Ciaran O''''Donnell" wrote:

> That is going to encounter a performance hit for the network traffic and
> possible a distributed transaction. I suggest you move things from box to box
> at the same time to avoid this.
> If you cant, look at filtering the rows as early as possible usings view on
> each box to allow filtering before data is transmitted. Run the query on the
> machine with the most records used in query (execution plan will help
> determine this).
> The reason its slow as the sql server you give the query to gets the tables
> send over from the other sql server that it needs, then runs the query, You
> need to use views on each box to lower the number of rows sent if possible,
> also run it on the machine with the most rows to lower the number
> transmitted. potentially get a faster network link too (change the connection
> options to use named pipes rather than tcp and put giga-nics in each box.
>
> --
> Ciaran O''''Donnell
> http://wannabedeveloper.spaces.live.com
>
>
> "Wishing I was skiing mom" wrote:
>
> > Thanks for your reply. I am accessing each database with a separate
> > connection string, but the SQL view that I am using on this one particular
> > tab accesses data on both servers.
> >
> > "Ciaran O''''Donnell" wrote:
> >
> > > The problem may be the link servers as the data has to double hop to get to
> > > you. Cant you have more than one connection string and connect to both
> > > servers directly or are you doing something crazy like cross database joins?
> > >
> > > --
> > > Ciaran O''''Donnell
> > > http://wannabedeveloper.spaces.live.com
> > >
> > >
> > > "Wishing I was skiing mom" wrote:
> > >
> > > > I have an application that currently accesses multiple databases on the same
> > > > server. I am attemepting to move one of the databases that I am accessing to
> > > > a new server and am using SQL linked servers to access the databases on the
> > > > old server. My application is experiencing a performance hit. I am using
> > > > tabcontrols, the screen refresh rate may take anywhere from 5 to 8 seconds to
> > > > display the data when running the application accessing databases on multiple
> > > > servers, compared to the 1 second on the production version. Has anyone else
> > > > experienced performance issues when accessing data on multiple servers.
> > > >
> > > > Thanks!