SQL Batch - Slow Response - Remote DB query

[jim.clifford]

Hello.
I have a slow response with a system that I am setting up. The OS is
Win 2000 Server with SQL Server 2000. My first execution of the SQL
procedure is slow (about 40 seconds), while the second execution is
faster (approx. 3 seconds and what I expect).
Using query analyser I can leave the window up and come back ( after
say 10 minutes ) and my next response is will be slow (and faster
straight after this).

The query itself is the "TOP 10" rows from a table of a remote DB (MSDE
2000 - NT station).
This is similar to;
---------------------
DECLARE @SelectRowsFrom RemoteDB NVARCHAR(400)
SET @SelectRowsFrom RemoteDB = "SELCT a.* FROM OPENROWSET
('SQLOLEDB', 'Remote', ....) a"

EXEC sp_executesql @SelectRowsFrom RemoteDB
---------------------

The CPU usage goes high (with Task Manager), but oddly the task with
most usage is the "Idle Process".

Please help with an answer
or a good starting point to known where the resources are being used.

Regards JC.....

[nigelrivett]

Sounds like it might be copying the table to the local server to do
sorting maybe?
My first thought wouldbe that the second call was fast because the data
was in memory. Maybe the third was slow because something had purged
that data?

Try using the profiler on both machines to see what was executed on
them.

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***

[jim.clifford]

nigelrivett wrote:

Sounds like it might be copying the table to the local server to do
sorting maybe?
My first thought wouldbe that the second call was fast because the data
was in memory. Maybe the third was slow because something had purged
that data?

Try using the profiler on both machines to see what was executed on
them.

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***

I am sure it is not an issue with copying the data. I can continually
poll the remote DB manually and the speed is quick and it changes when
expected. The problem appears to be related to finding the path to the
remote server. This memory of where to look for the remote DB seems to
be short.

Regards JC......