ThinkGeo.com    |     Documentation    |     Premium Support

Using With Parameter on MsSql2008FeatureLayer

Hi,

As you know if we use

WITH(INDEX(geom_index))

on our SQL queries, it will be much faster than not using it.

SELECT * FROM dbo.RoadNetworkFeature WITH(INDEX(geom_sidx)) WHERE (geometry::STGeomFromWKB(@Geometry,0).STIntersects(Location)=1)

is much faster than

SELECT * FROM dbo.RoadNetworkFeature WHERE (geometry::STGeomFromWKB(@Geometry,0).STIntersects(Location)=1)

but seems that MsSql2008FeatureLayer does not put it on the query you send to the SQL-Server.

is there a way to force MsSql2008FeatureLayer to use

With

on SQL-query.

Hi Behnam,

  1. You can modify the query string in MsSql2008FeatureSource.ExecutingSqlStatement event.
  2. You can remove the “where” from your sqlstatement, then set something like “WITH(INDEX(geom_sidx)) WHERE …” in the WhereClause.
  3. In fact some of our API will inner use “WITH(INDEX(geom_sidx))” in some special scenario.

Regards,

Don