Make remote query faster? RRS feed

  • Question

  • Hi experts,

    I am checking a few issues we have with 2 tables that we keep in sync… I run this to check it:


    select lead_id from Source with (NOLOCK) where lead_id not in (select lead_id from Target with (NOLOCK)  )

    select lead_id from Target with (NOLOCK) where lead_id not in (select lead_id from Source  with (NOLOCK)  )


    It takes almost 10 minutes to run, why? below what I see in sp_who2

    Yes its a remote server (the source)… I have seen that inserting it to a temp table and then doing select #temptable could improve the speed, is this true?

    Monday, September 19, 2016 10:21 AM


  • So we are expected to answer questions about tables we have no knowledge and tell you why its slow?

    If I am to make a guess completely out of the blue, my guess is that since the table in the remote query is called lead and you have a column lead_id, there is a clustered index on lead_id. Response time could possibly improve if you added a non-clustered index, since this index would be cheaper to scan.

    A better approach, though, may be to find a way to narrow down this sync query, so that it only queries rows that presumably could be missing.

    I don't know exactly what the purpose is with this query, but you say "sync". In such case, the use of NOLOCK seems highly appropriate, since you could get transitional errors because you happen not to read data that should be there. Thankfully, the hint is ignored for the remote table, but the problem still exists for the local table.

    • Marked as answer by maca128 Monday, September 19, 2016 11:39 AM
    Monday, September 19, 2016 11:16 AM