Bug: Using the ITableSort::Sort method with ArcSDE data sources results in poor performance


**This issue has been resolved in ArcGIS (Desktop, Engine, Server) 9.3.1 Service Pack 2.**

ArcGIS 9.2 introduced an additional query to count the number of rows in the objectclass referenced in the query filter when using the ITableSort::Sort method.

Executing a count(*) statement against a versioned feature class can consume a lot of DBMS resources to return the result and can impact application performance.

This behavior only happens when working with ArcSDE geodatabase data sources.


The following ArcObjects code block demonstrates how to use the ITableSort interface and the call to the sort method.

The example opens the feature class PRIMARY_OVERHEAD, defines a query filter where the feederid equals 817023, and defines the tablesort properties.

Dim pTablesort As ITableSort

Set pTable = pFeatureWorkspace.OpenTable("PRIMARY_OVERHEAD")

Set pQueryFilter = New QueryFilter
pQueryFilter.WhereClause = "feederid = 817023"

Set pTablesort = New esriGeoDatabase.TableSort

With pTablesort
.Fields = "OBJECTID"
.Ascending("OBJECTID") = True
Set .QueryFilter = pQueryFilter
Set .Table = pTable
End With

pTablesort.Sort Nothing

When pTablesort.Sort is called, the method executes the additional count(*) query in the DBMS, which can potentially impact performance.


If the application is being impacted by the additional query being executed, do not use the ITableSort::Sort method.

Instead, use a search cursor.

Set pCursor = pTable.Search(pQueryFilter, False)
Set pRow = pCursor.NextRow

    Related Information