By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,543 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,543 IT Pros & Developers. It's quick & easy.

SQL Batch - Slow Response - Remote DB query

P: n/a
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 @SelectRowsFromRemoteDB NVARCHAR(400)
SET @SelectRowsFromRemoteDB = "SELCT a.* FROM OPENROWSET
('SQLOLEDB', 'Remote', ....) a"

EXEC sp_executesql @SelectRowsFromRemoteDB
---------------------

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.....

Oct 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 ***
Oct 16 '06 #2

P: n/a

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......

Oct 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.