We are using the linked server feature of SQL Server 2000 to execute queries
from the local database's stored procedure against the SQL Server database
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.



Please note that the schema of the remote database is different from the
schema of the local database.



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?
Ashish