Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9

    Question Unanswered: Linked Server/SQL Server OLE DB performance problem with parameters?

    Hi,
    I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.

    I have set the dynamic paramters option on, and use collation compatible.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Linked servers in general are a performance nightmare.

    Get me that bucket of data over there....ALL of it.

    Careful...carry it over here now. Be careful. Oh crap...I dropped some. You need to start over.


    Great....it's finally here.....ALL OF IT. Can you throw away everything that doesn't match this one value?

    That's great. Thanks.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Replace a straight DML/SELECT with a call to a stored procedure on the remote side and you'll be home free!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2004
    Posts
    52
    SQL Attempts to retrieve statistics information from remote tables to determine which server should be the driving server - or where to filter rows first before joins. It runs some system stored procedures (I forget the names at the moment) If the account connecting to the remote server does not have permisisons on the procedures SQL will assume the worset and generate a query plan with the statistics available on the local server. I'm not recommending you grant the remote user dbo rights (not that I would do that ....) but if you did do it temporarily and saw a performance increase you could research exactly which permissions were really needed. Actually you can run profiler on the remote server and you will see the statistics gathering queries and identify the objects involved.

  5. #5
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9
    In testing I found that the query performed faster with the parameters declared 'inline'. Does this make sense? Or am I suffering from inconsistent testing conditions? Our production databases are on the same servers as our test dbs.

    The distributed queries are in stored procedures. And they (usually) don't join to the local server. However, the stored procs need to query the local db and store results there. It would be possible to put stored procs on the remote db. But we have already taken the functions into production so code changes are undesired.

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    Try using the openquery() method. When this method is used the calling SQL server instance does not attempt to retrieve statistics info and the linked server processes the SQL and simply returns the results.

    This especially makes a huge difference when the linked server is Sybase ASE.

Posting Permissions

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