[Gdal-dev] OGR VRT performance through ODBC...

[Frank Warmerdam]

John Cole wrote:
> Hello,
>   I have a OGR VRT layer defined through ODBC to a MsSqlSpatial database:
> 
> <OGRVRTDataSource>
>     <OGRVRTLayer name='HUDPLS'>
> 		<SrcDataSource>ODBC:mapserver at HUDPLS</SrcDataSource>
> 		<SrcSQL>SELECT t.oid, t.casenumber, t.casestepnu, t.the_geom
> FROM ST.FilterQuery('properties', 'the_geom', ST.MakeEnvelope(%BBOX%, -1))
> AS q
> INNER JOIN properties AS t ON q.oid = t.oid
> where t.casestepnu='06' and t.inaca=0</SrcSQL>
> 		<GeometryType>wkbPoint</GeometryType>
> 		<GeometryField encoding='WKB' field='the_geom'/>
>     </OGRVRTLayer>
> </OGRVRTDataSource>
> 
> This works, but performance is a little disappointing.  MapServer renders
> this layer in ~3.5 seconds, while a postgis layer with the same data is
> rendered in ~0.35 seconds.  Running the query in SQL Server Management
> Studio has all 23 rows returned in 0.00 seconds, so I'm fairly sure that the
> issue is not MsSqlSpatial.  There are 260K points in the table, but only 23
> are returned in my test query.
> 
> Is there anything I can do to configure either the ODBC driver (SQL Native
> Driver) or the VRT layer itself to improve performance?

John,

Does it take 2-3 seconds for ogrinfo to run against the above .vrt file,
dumping the result features?  I'm wondering if the issue relates to
the simplest of OGR feature reading or if it might be something that
mapserver is doing (like the two pass read done for query rather than
simple draw operations).

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org