SQL Server 2008 Spatial - Performance of database calls?
There are 4 ways of holding spatial data, Well Known
Text (WKT), Well Known Binary (WKB), Geography Markup Language and the base
spatial type (SqlGeometry or SqlGeometry).
This leads me on to the question how should you pass the data to SQL
Server.
I've heard bad things about passing udts and so expected the WKB format to be
the best, based on the my previous post (SQL
Server 2008 Spatial - Which is faster WKT or WKB? )
So I ran my test, I generated a spatial instance, and then created 3
procedures one for WKB, one for WKT and one for the base udt type. (I've ignored
the GML becuase it will be similar if not worse than the WKT .
As expected the WKB wins out, over the WKB and the base udt instance,
Initially.
I was shocked to find that after your geometry hits ~50 points, assing the
udt instance is quicker than passing the WKT or WKB. In fact the performance of
passing the base instance is a fairly straight line of performance with only a
slight drop off, probably due to the size of the data rather than anything else.
However for the WKT and WKB the performance falls through the floor, almost
exponetially.
The green line is passing the spatial variable as is using the
SQLDbTypes.Udt, the Blue line is the WKB and the Red is the WKT. There are no
numbers on this for a reason. suffice to say that the udt method vaires from
about 3ms to about 6 ms. The last reading is for a geometry with 900 points. For
the WKB type it took 1s (not second and not millisecond) to parse
Steven Hemingray has provided an explanation on the
forums here Shocking performance for parsing geometries
in SQL saying it is related to the validating of the
data.
-