MapServer Performance / Default Schema Configuration

Topics: User Forum
Mar 25, 2008 at 3:47 PM
I have configured mapserver to connect to a MsSqlSpatial database and to return point data. While I was able to get this work intially performance was very slow (6-7 seconds) to return three points from a table with a total of three rows. Upon running a SQL Server Profiler Trace I discovered that there was over 800 lines of commands happening for one request (logging both start and complete). One of the first lines

SELECT ftablename, fgeometrycolumn, geometrytype FROM geometrycolumns

was generating and error. Upon executing this line individually I was able to determine the error is, "Invalid object name 'geometry_columns'." I was able to work around this by setting the default schema of the IIS service account (IUSR) to have a default schema of ST instead of dbo. This reduced the trace statements from 800+ to 7 and resolved the performance issues.

I was shocked that after changing the default schema I did not have to explicitly specify the schema on my spatially enabled tables in map mapserver layer definition (which is using the OGR ODBC driver). I am assuming that this driver is not aware of specifying the schema for getting the geometry_columns table. Is there a way to do this in the Virtual Format file or is setting the default schema for the service account the proper approach. I could not find anything when searching for this issue so I at least wanted to document my work around for the community.