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.

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:
> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:

> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed
queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dyn
amic sql they always go fast. Is anyone aware of performance problems with p
arameters in the SQL Server OLE DB Provider?
Cheers,
James
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on per
formance of distributed queries.
HTH
"SqlJunkies User" wrote:

> Hey,
> I have been getting some odd results when performance testing my distribut
ed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as d
ynamic sql they always go fast. Is anyone aware of performance problems with
parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>