When a custom application that uses spatial filters was used with SQL Server data, performance problems were observed. What causes this issue with SQL Server connections?
The following behavior was noticed regarding the custom application...
When creating a OriginatingPipe against SQL-server, the recordset gets loaded when referencing the OutputRecordset propery. The reason for creating the OriginatingPipe before setting the SpatialFilter, is to get information about the PrimaryGeometryFieldName and for transformation of the SpatialFilter blob. This was solved by setting the following: objPipe.Filter = "0=1" ' This prevents loading data from the server but this behavior is not apparent with any other dataservers.
This is a consequence of the standard behavior for all pipes. When you reference the output recordset the pipe must open the input recordset and do some amount of work to make the first record of the output recordset active. (For example, in the case of the sort pipe, the sort keys of all records of the input recordset must be examined to determine which record is first in the output recordset.) In the case of the originating pipe, the input recordset is opened and the GFields collection is examined to populate the GFields of the output recordset. What happens when you open the input recordset without fetching any data is dependant on the implementation of the underlying data server.
In the case of the SQL Server data server, we are using ADO client-side cursors for performance reasons. But the consequence is that the entire contents of the recordset will be transferred to client-side memory when the input recordset is opened. The filter property will be used to formulate a where clause that originating pipe uses when the input recordset is opened on the data server. In this case it will use "WHERE 0=1". Provided that this syntax is supported it should work for any data server to open an empty recordset. It's not a special feature of the data server, it's standard SQL. However, you don't have to open a recordset (which is what originating pipe is doing) to obtain the meta-data you need. You can obtain it from the Metadata Service. The primary geometry field name is exposed directly. You can also obtain the default coordinate system of the server, which is probably the same one used by the feature class. In the case of a connection with a single coordinate system,