server: Performance Issue with OpenQuery & SP best practices

We are using the linked server feature of SQL Server 2000 to execute queries
against SQL Server data source which is hosted on the remote computer. In
our analysis, it was found that OpenQuery() takes the a lot of time causing
the system to have a performance hit.
We are looking for the following answers:
a.. Is there any alternative to linked server in SQL Server to fetch the
recordset from the SQL database hosted on the remote machine?
b.. If we use linked server approach, then is there any way to optimize
OpenQuery() call?
c.. What are the best practices to write the stored procedures? Is it
recommended to use temporary tables and cursors?
Thanks
AshishAshish Sharma
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
Replications
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
Tunning the query.Make sure that you define proper indexes
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
It depends on your business requirements. You can use a temporary tables
within a stored procedure , however make sure that #table has indexes and a
stored
procedures does not get recompile each time as you calling it .
Cursors should be your last resort ,because you will be benefit from set
based solution in terms of perfomance
11
"Ashish Sharma" <ashish.sharma@.honeywell.com> wrote in message
news:%23M%23%23r5$nGHA.4604@.TK2MSFTNGP02.phx.gbl...
> We are using the linked server feature of SQL Server 2000 to execute
> queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time
> causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>|||Ashish Sharma wrote:
> We are using the linked server feature of SQL Server 2000 to execute queries
> against SQL Server data source which is hosted on the remote computer. In
> our analysis, it was found that OpenQuery() takes the a lot of time causing
> the system to have a performance hit.
>
> We are looking for the following answers:
> a.. Is there any alternative to linked server in SQL Server to fetch the
> recordset from the SQL database hosted on the remote machine?
> b.. If we use linked server approach, then is there any way to optimize
> OpenQuery() call?
> c.. What are the best practices to write the stored procedures? Is it
> recommended to use temporary tables and cursors?
> Thanks
> Ashish
>
>
You first need to identify why it takes so long for the OPENQUERY query
to complete. Are the necessary indexes in place on the remote table(s)
to support the query? How much data is the query returning? You're
essentially copying data over the network, the more data to move, the
longer it's going to take.
Tracy McKibben
MCDBA
http://www.realsqlguy.com