Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    5

    Question Unanswered: Stored procedures with linked servers

    I am working on an application for my company using SQL Server 2000. I have a fairly large (4500+ line) stored procedure that is used to import data from another SQL Server machine (a laptop running the desktop engine). The way I'm doing this is by using a linked server (called SyncServer) within my big procedure to refer to the laptop's data. The basic procedure works fine. The problem comes from the fact that a given customer may have many different laptops, all of which will need to be imported periodically. What I wanted to do was simply remove the linked server (SyncServer) each time the import begins and re-create it using sp_addlinkedserver keeping the name SyncServer but pointing it to the correct laptop machine. This way my big procedure would work regardless of which laptop the "SyncServer" was actually pointing to.

    The problem is that each time the linked server is destroyed and re-created, when the big procedure runs, the first time it hits a statement referring to SyncServer, it recompiles. Since the procedure is so big this takes a long time. So if the laptop has 6 reports that need to be imported, it takes 30-45 seconds to read in the first one while the procedure recompiles, and then the other 5 take less than a second each. I might be able to get around this by using sp_executesql to handle the statements referring to SyncServer, but this would be pretty difficult to get working correctly because I'm doing so much with the linked server. I didn't think it would help to split the big procedure into several little ones because then each small one would have to recompile. While each one would compile faster because it was smaller, it seemed like the total time to compile would be the same. This may be an incorrect assumption though.

    Does anyone know of any way around this problem? Is there some way to keep the procedure from recompiling even though the "definition" for SyncServer has changed? Any help anyone can offer would be greatly appreciated. Thanks in advance!

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    just a theory here but what if you write a stored procedure that has the exact same signature as your behemoth sp and in the new sp call the behemoth with all the passed parms. I am not sure if the behemoth would get recompiled or not but it would be a quick test.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2002
    Posts
    5
    Paul,

    I tried doing as you suggested and calling a "dummy" procedure that in turn called my big procedure. Unfortunately, the big procedure still recompiled. I also tried building a string for the call to the big procedure and using sp_executeSQL in my dummy procedure, but again, it recompiled. I appreciate the suggestion, though.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What happens if you select any thing form your linked server from the dummy sp prior to calling the big sp? Again just a WAG. The problem may be that SQL is smart enough to recompilae any sp that connects to a "new" linked server.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Sep 2002
    Posts
    5
    If I refer to SyncServer in the dummy procedure, it recompiles the dummy procedure, but then still recompiles the big procedure. It appears to be as you suggested - that SQL Server somehow stores information about the linked server that was used with each procedure when it compiles it. If there is any documentation anywhere about how this is done it might provide a way to fool SQL Server into thinking the server is the same even though it's pointing to a different machine. I certianly don't know of any such documentation, though.

Posting Permissions

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