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 queri
es
> 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 causin
g
> 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

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

server vs Advantage Local Server

I'm trying to define a linked server (from within SQL Server 2005
Express) to an Advantage Local Server.
Using OpenQuery, I get error message:
Cannot create an instance of OLE DB provider 'Advantage.OLEDB' for
linked server 'LStest'
A linked server to an Advantage Database Server (remote) works fine. Is
there a way to make it works locally?
Thanks a lot in advance for help,SQL Server Standard works fine...
"rcloutie" wrote:
> I'm trying to define a linked server (from within SQL Server 2005
> Express) to an Advantage Local Server.
> Using OpenQuery, I get error message:
> Cannot create an instance of OLE DB provider 'Advantage.OLEDB' for
> linked server 'LStest'
> A linked server to an Advantage Database Server (remote) works fine. Is
> there a way to make it works locally?
> Thanks a lot in advance for help,
>

server Update Problem

I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.

I can do this...

SELECT

Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')

When I try to Query using four-part names I get the following error...

7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.

UPDATE

When I try to update using this...

Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'

I get the following error...

The OLE DB provider 'MSDASQL' indicates that the object has no columns.

When I try to update using four part names I get the error that says that the provider does not support the necessary method.

OTHER INFO

I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.

I believe we are using version 6.1 of db2 connect.

The mainframe db2 is version 7.0 release 1.0

Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...

GaryKBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;270119) to resolve the update error issue.|||Satya, thanks for the reply but in my post I referenced this KBA. Neither of the workarounds mentioned by microsoft worked.

Originally posted by Satya
KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;270119) to resolve the update error issue.