Hi dear FDO Guru’s,
I have tried 3 versions of the SQL Server provider, Autodesk MapGuide 2010 Update 1, OS Geo 3.4.0 and 3.4.1 RC1 and I face the same problems.
The main problem is that with a map including only one layer (based on one table containing 3400 parcels stored in GEOMETRY type) it takes about 3 seconds to refresh the map despite the far over-dimensioned architecture, at least for unit tests (MG Server : 4 CPU, 8 Gb RAM, high speed + DB Server : 4 CPU, 4 Gb RAM).
On the Mapguide Server, the CPU never exceeds 20%, on the DB Server, one query the CPU can reach 80% CPU on a single request.
I’ve trapped the SQL Server trace and have seen 2 things (see trace file attached) :
- The following query takes between 1,5 and 2 seconds :
SELECT F.[GEOMETRY].STAsBinary() as GEOMETRY,F. ID_TRANSAC,F.ID_ENTITY,F.ID_STRUCTURE,F.ID
FROM CARTO.PG F
with (INDEX([SPATIAL_PG]) )
WHERE
[GEOMETRY].Filter(geometry::STGeomFromText('POLYGON ((242353.225061205 2093829.3626259, 442712.156613794 2093829.3626259, 442712.156613794 2212891.8626259, 242353.225061205 2212891.8626259, 242353.225061205 2093829.3626259))', 0))=1
AND
[GEOMETRY].MakeValid().STEnvelope().STIntersects(geometry::STGeomFromText('POLYGON ((242353.225061205 2093829.3626259, 442712.156613794 2093829.3626259, 442712.156613794 2212891.8626259, 242353.225061205 2212891.8626259, 242353.225061205 2093829.3626259))', 0))=1
- After this request, depending on the number of parcels retrieved, I see many sp_cursorfetch executions.
My questions :
- If I remove the part [GEOMETRY].MakeValid().STEnvelope().STIntersects in my query, I have the same result, but far faster (between 0,4 and 0,6 seconds). How can I skip this step in my query ? (see the test “spatialOp == FdoSpatialOperations_EnvelopeIntersects” in ..\Providers\GenericRdbms\Src\SQLServerSpatial\Fdo\FdoRdbmsSqlServerFilterProcessor.cpp)
- If I cannot skip this step, how can I optimize my data or MapGuide configuration to make it faster ?
- If I remove the function [GEOMETRY].STAsBinary(), the answer is a bit faster (between 0,3 and 0,5 seconds). I guess this is mandatory to retrieve Binary data, but is there a way to optimize the execution of this function ?
- Is there a way to reduce the number of sp_cursorfetch after this query and to retrieve everything one shot ? (I don’t see anything obvious in ..\Providers\GenericRdbms\Src\SQLServerSpatial\fetch.c)
Thanks a lot,
Geomakhs.
