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.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.|||Replace a straight DML/SELECT with a call to a stored procedure on the remote side and you'll be home free!|||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.|||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.|||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.