The document page on Setting up a Microsoft Linked Server using OLE DB does tell that one has to set the "AllowInProcess" option for the SQL Anywehre OLE DB provider in order to make queries run at all (which I stumbled over lately).
There are several other provider-wide options as well.
I'd like to know if there are general recommendations to set some of them. As this relies partly on the facilities of the provider, I hope that a general recommendation can be made here. My aim would be to specify at least those options that could lead to performance improvements over the default settings (which is "Off" in general).
The list for MS SQL Server 2008 R2 includes these options - and I have included a comment whether I guess this could/should be specified for the 12.0.1 OLE DB Provider.
EDIT: I've corrected my wrong suggestions based on Jack's guidance:
Any clarification is highly appreciated.
Most of these options are for SQL Server backwards compatibility and are ignored by SQL Server when communicating with the SQL Anywhere OLE DB provider. For example:
"Supports LIKE operator" - SQL Anywhere supports LIKE but don’t expect SQL Server to pass a LIKE operator to SQL Anywhere even when this option is selected.
"Nested queries" - SQL Anywhere supports nested queries but don’t expect SQL Server to pass a nested query to SQL Anywhere even when this option is selected.
Check out this page http://msdn.microsoft.com/en-us/library/ms178039.aspx. When you read this, you'll see the correspondence between the SQLPROP properties and many of the options that you can select. Note that the remark "OLE DB providers that support DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE do not need any one of the SQLPROPSET_OPTHINTS properties" applies to the SQL Anywhere provider.
Do not select the "Index as access path" option. This option is not ignored but it generates invalid SQL Anywhere syntax.
I don't know whether the following have any affect but don't select them since they are meant to downgrade capabilities. - "Level zero only" option. SQL Server queries which interfaces are supported by the SQL Anywhere provider. - "Disallow ad hoc access" option. The provider supports OPENROWSET. - "Non transacted updates" option. The provider supports transactions.
answered 22 Feb '12, 10:01
Pursuant to the above, there are performance implications that you should consider. To illustrate, here are two queries that produce the same result set but with significant performance differences.
When you execute the query below using SQL Server Management Studio, the nested queries are executed on the SQL Server side. This means that all rows in all tables referenced in the query are prefetched by SQL Server before the query can be evaluated.
SELECT ID, LineID FROM [SATEST12].[demo].[GROUPO].[SalesOrderItems] WHERE ShipDate = ANY ( SELECT OrderDate FROM [SATEST12].[demo].[GROUPO].[SalesOrders] WHERE FinancialCode IN ( SELECT Code FROM [SATEST12].[demo].[GROUPO].[FinancialCodes] WHERE ( Description = 'Fees' ) ) ) ORDER BY ID, LineID;
When you execute the query below using SQL Server Management Studio, the nested queries are executed by the SQL Anywhere server and only the result set is returned. This yields a significant performance improvement.
SELECT * FROM OPENQUERY( SATEST12, 'SELECT ID, LineID FROM [SalesOrderItems] WHERE ShipDate = ANY ( SELECT OrderDate FROM [SalesOrders] WHERE FinancialCode IN ( SELECT Code FROM [FinancialCodes] WHERE ( Description = ''Fees'' ) ) ) ORDER BY ID, LineID;' );
Consider using OPENQUERY if your query uses LIKE, nested queries, and other result set reduction clauses. OPENQUERY queries are always executed in whole by the SQL Anywhere server.
By the way, you mentioned the "Allow Inprocess" option but not some others. The options to set are documented. The other two important options to select are RPC and RPC OUT. These two options must be selected each time you create a Linked Server object.
answered 22 Feb '12, 10:08