data retrival time

How can i speed up the return time from search between 2 different server?

I am currently using SQL 2000 and SQL 2005 With the same query, the searching time is much faster to select between database within the same server instead of two different one. Is there something that i can do in the server configuration to speed up the searching time?

Thanks in advance

Could you be more specific? The query in question, the execution plans and the table metadata, including the indexes plus some basic data on the rough row counts/value distributions would be very helpful too.|||

Hi Tengiz,

I am Calling a SP with Views inside that will cross over defferent Server,

For Example,

in Server 2005 i have a SP called spSample in the data base called dbDEV

within that SP I have View1, View2 and View3 in my databse , Tempdt, in Server 2000

so inside my SP it'll be something like the following

CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
right JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL

GO

the sp Works find if both database was with in the same server, but was very slow if i moved on of the database to a different server. What can i do to make returning data time faster?

Thanks

|||

Since the SP is created in the database dbDEV and the 'local' (with respect to the database the SP is created in) table tblBK participates in an outer join in the way that assumes that the entire table also participating in the join (View1) will be pulled in order to produce the result you request, depending on the size of the table this could indeed take a long time.

Do you really need the following outer join here: tblBK RIGHT join View1? Considering the where clause on tblBK.Key_ID it doesn’t look like having the tblBK participate in a right join matters here since the rows where tblBK.Key_ID is NULL are filtered out anyway.

Hence if you simply remove the 'RIGHT' from the query text and use the normal inner join instead you should see the improvement in the perf and see the results that you request:

CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL