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.
[SS 1/8/2008 - The following was based on a geometry of random points I am in the process of doing an investigation with more organised lines]
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.
-