Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: Tuning a distributed query

    Hello

    I have 2 servers: myLocalServer (SQL2005) and myRemoteServer (SQL2000), both in the same LAN. I wish to syncronize a remote table with a local table (both share the same structure) by means of a stored procedure. The amount of rows to carry from the local to the remote table is about 20,000. The query takes more than a minute, and I would like to take down that time. Can you please help me?

    myRemoteServer is declared in myLocalServer by means of a Linked Server object, and I declared a synonym called Syn_RemoteTable which represent the remote table.

    First I tried a cursor, but it did not worked:

    declare curLocalTable cursor local forward_only static read_only for
    select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate

    open curLocalTable
    fetch curLocalTable into @ID, @Value

    while @@Fetch_Status=0
    begin
    if exists(select ID from Syn_RemoteTable where ID=@ID)
    begin
    update Syn_RemoteTable set Value=@Value where ID=@ID
    end
    else
    begin
    insert into Syn_RemoteTable (ID, Value) values (@ID, @Value)
    end
    fetch curVentasMensuales into @ID, @Value
    end

    close curLocalTable
    deallocate curLocalTable


    Other way that I tried -performing equally poorly- was:

    update Syn_RemoteTable
    set Value=T.Value
    from Syn_RemoteTable
    inner join
    (
    select ID, Value from myLocalTable where UpdateTimeStamp>@LastUpdate
    ) as T
    on T.ID=Syn_RemoteTable.ID

    insert into Syn_RemoteTable
    (
    ID,
    Value
    )
    select
    ID,
    Value
    from myLocalTable
    where
    UpdateTimeStamp>@LastUpdate and
    ID not in (select ID from Syn_RemoteTable)
    Last edited by EMoscosoCam; 04-11-07 at 14:26.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quite honestly, I cannot see why this takes so long. Well, I could understand it if the servers were on a 10BaseT hub or 10Base2 network, but I guess that is not the case. However, I miss some information about the datatypes. If ID and/or TimeStamp are character data, then we might have found the issue. If for instance the timestamp columns are varchars, and there are different collations on the two servers, the local server cannot trust that the remote server compares the data in the correct way, and thus retrieves all the data from the remote server first, which may be timeconsuming.

    So, please check the execution plan to see if the local server appears to read out the whole table on the remote server.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Mar 2003
    Posts
    144
    Thanks a lot for your reply. The Remote table has about 90,000 rows, and is correctly indexed.

    Both servers have the same collation and I have already run the store procedure sp_ServerOption like this (in the Local server), but it still takes too long:
    execute sp_ServerOption 'MyLocalServer', 'collation compatible', 'true'
    execute sp_ServerOption 'MyRemoteServer', 'collation compatible', 'true'

    The UpdateTimeStamp column is of the datetime data type.

    In order to simplify the query sample of my previous post, I mentioned an ID column. But the real columns that are used for comparisons are a Product_ID (a varchar(20)) and a Date_ID (a datetime column), for example:

    if exists(select Product_ID from Syn_MyRemoteTable where Product_ID=@Product_ID and Date_ID=@Date_ID)
    begin
    end

Posting Permissions

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