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.

Published 07 January 2008 21:32 by simonsabin


21 March 2008 11:17 by Jakob

# re: SQL Server 2008 Spatial - Performance of database calls?

Interesting stuff spatial datatypes mapping Katmai.

BTW .....and the base spatial type (SqlGeometry or SqlGeometry)....

I quess you mean SqlGeography in one of the SqlGeometry in you text.