Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Question Unanswered: slow query response from a linked server

    Hello,

    I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.

    My problem is that if I run a query returning few dozens of rows like:

    SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA
    WHERE Fieldx = 'anyval'

    from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.

    Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.

    I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.

    There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.

    Any suggestions will be very appreciated, thank you!!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If this is regardless of client then you are probably best posting in the SQL Server forum. The long and the short is - linked server queries like this are best executed on the host server not a server that is linked to the host server - get the client to choose the appropriate server.

    Security options won't make a difference. Ensure that you are subimitting exactly the same query to both servers. You have too many variables (network connections between servers, whether or not the query can be compiled by the executiing server etc).

    In any event - my experience is that you won't get the same response from linked servers especially if you are linking local and remote tables.

    Just an FYI - select * is a poor chooice of predicate - selecting only the columns you need is good practice and may be faster.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - beg your pardon - thought I was in the access forum
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •