Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: Stored procedure that calls linked server slow.

    I have a stored procedure that runs a select on a linked server. The stored procedure takes a parameter that is used in the query to the linked server. It is the primary key of the table being queried, so only one row will be returned. Below is an example:

    select * from linkedserver.db.dbo.table where primary_key = 1234
    returns instantly!

    But in the stored proc, it takes 7 minutes using this:
    select * from linkedserver.db.dbo.table where primary_key = @pk

    Both servers are running SQL2K Enterprise Service Pack 3.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Could it be a matter of coincidence and the table is locked?

    What else does the sproc do?

    Can you put just that statement in a sproc and test it?

    Did you set up a trace?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    I created a new proc that only selects the one row, and I get the same results. The table is not locked. Basically, what is happening is a table scan becuase the query is not using the index for the primary key.

    This query worked fine a few weeks ago when we ran it on SQL2K standard agains SQL 2K enterprise. But now that we have moved it to production, where both servers are on SQL2K enterprise, it doesnt work.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd say someone forgot to create the index....

    do you have access to production?

    Can you script the tbale?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    10
    The index is there. Keep in mind, when I run the query with the parameter value hard coded, it returns immediatly. The problem only occurs when the where clause uses @pk instead of 12345

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    you can also try to call a stored procedure that you'd put on the linked server that would do the actual select, and pass the key value as a parameter to it, like this:

    exec linkedserver.db.dbo.sp_select_proc 1234

  7. #7
    Join Date
    Dec 2003
    Posts
    10
    Unfortunatley, these two servers will be talking to each other quite a bit, so I need to be able to correct the actual problem rather than workaround it. Since it was working before, I know it can be done...I just need to figure out why the new server doesnt do it right!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you set up a trace using profiler, and then watch both?

    Don't understand the scan...

    maybe you can give it an index hint in the sproc...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    10
    Index hists arent allowed on linked servers. Profiler didnt turn up anything

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK,

    Lets eliminate the only difference between the 2 (other than 1 being in a sproc and the other not)

    Can you create a sproc with the same code, but with the predicate hard coded?

    What happend then?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2003
    Posts
    10
    With it hard coded, it returns in less than 1 second.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok...gonna go test a linked server here..

    Is your same box different instance, or different box altogether...

    Alos what if you do this in QA:

    DECALRE @pk int
    SELECT @pk = 1234
    select * from linkedserver.db.dbo.table where primary_key = @pk

    1second?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2003
    Posts
    10
    Already tried that. About 6 minutes!

  14. #14
    Join Date
    Dec 2003
    Posts
    10
    I think I found the problem.

    I create a new linked server, but instead of selecting SQL server and entering the servername, select the OLEDB Provider for SQL Server instead. Click the button called Provider options and check the box for "Dynamic Parameters" and click Apply. Then click cancel on the screen for the new linked server.

    Thanks for all your help

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Was "index as an access path" checked?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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