Post reply

select into takes forever from remote server.

  • May 20, 2010 at 7:22 am

    #141049

    Hi,I upgraded one of us sql server from 2000 to 2005 version and now i am running one stored procedure which is taking forever(17 hours),The SQL Statement inside SP is select [COLUMN NAMES] into TABLENAME from [VIEWNAME].The view is created(accessing data from remote server) on behalf of the remote server table.

    I used to run this SP every month before upgrade and it used to take 30 minutes.

    For temporary solution i imported the remote sever table into local server and pointed view to that local table and it works.

    Any idea why SP is taking forever when it is quering data from remote server.Note,It select data from remote server but create table into local server.

    Thanks

    Irfan

  • May 20, 2010 at 8:49 am

    #1170176

    linked servers are slow.

    usually a SELECT from a linked server, if it is joining on a local table, will copy the ENTIRE table from the linked server into your local temp db, then do the join locally, and then (i presume) do your INSERT INTO. call your procedure twice,a dn it's going to copy the enttire table over the wire a second time...it will not be cached for reuse.

    much better to grab a local copy like you did to resolve the issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 20, 2010 at 12:30 pm

    #1170394

    But why it was not happened before,I am doing this procedure since last year.Is it due to upgrade or it is due to link server?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply