Cross Database access via Linked Server

Hello. When doing CROSS server CROSS database
select/joins, I use Linked servers. But for CROSS
database SAME server access, I'm thinking of a standard
where we ALSO use linked servers, even though the database
MIGHT be on the same server usually. I'm looking to do
that because on all test boxes the databases might not be
on the same server.
Question>> Is there any performance issue at all in doing
using linked servers when the databases involved are all
on the same server'
Option #1
select * from database2.dbo.sysobjects
Option #2
select * from Server.database2.dbo.sysobjects
Any performance hit in using Option #2, if the databases
are all on the same server? THanks, Bruce"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:010301c3c017$6aa27b80$a401280a@.phx.gbl...
> Hello. When doing CROSS server CROSS database
> select/joins, I use Linked servers. But for CROSS
> database SAME server access, I'm thinking of a standard
> where we ALSO use linked servers, even though the database
> MIGHT be on the same server usually. I'm looking to do
> that because on all test boxes the databases might not be
> on the same server.
> Question>> Is there any performance issue at all in doing
> using linked servers when the databases involved are all
> on the same server'
>
Yes. The performance hit is not small.
I always use views for cross-database access. It makes your life easier in
a number of ways.
For each foreign table, create a view like
create view FT
as
select * from otherdb..FT
or
create view FT
as
select * from otherserver.otherdb..FT
Then your application just uses FT, and the view resolves it to the other
database or other server.
David|||ok David. I wasn't thinking about views as an option on
cross-database stuff. Thanks for the reply, that's a good
approach! I'm not sure about having THAT many new views
though for all the combos of tables in other tables beign
accessed, but if self-referencing linked servers are that
much slower, then I'll be checking it out... Thanks, Bruce
>--Original Message--
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:010301c3c017$6aa27b80$a401280a@.phx.gbl...
>> Hello. When doing CROSS server CROSS database
>> select/joins, I use Linked servers. But for CROSS
>> database SAME server access, I'm thinking of a standard
>> where we ALSO use linked servers, even though the
database
>> MIGHT be on the same server usually. I'm looking to do
>> that because on all test boxes the databases might not
be
>> on the same server.
>> Question>> Is there any performance issue at all in
doing
>> using linked servers when the databases involved are all
>> on the same server'
>Yes. The performance hit is not small.
>I always use views for cross-database access. It makes
your life easier in
>a number of ways.
>For each foreign table, create a view like
>create view FT
>as
> select * from otherdb..FT
>or
>
>create view FT
>as
> select * from otherserver.otherdb..FT
>Then your application just uses FT, and the view resolves
it to the other
>database or other server.
>David
>
>.
>