SQL Server 2008 Spatial - Performance of database calls follow up
I recently blogged about the performance of passing
spatial data to the database.(SQL
Server 2008 Spatial - Performance of database calls?
)
In it I found that the the performance radically dropped off as the number of
points increases.
Whilst this was suprising I had used my usual approach of eliminaing other
factors and running the tests many times and so assume the results correct.
Following feeback from Steven Hemingray I did some more investigation.
Steven had said that the time was likely due to the validation process that
has to be done when the geometry instance is created. What I realised was that
the code I used to generate the geometries used totally random points. This
meant the shapes were extremely complex when determining if the shape is
valid.
I change the logic to produce less complex shapes and
the difference was dramatic.I have a wavy line with no intersections and another shape that is a bit like drunk spider with
intersections.
The performance is much more linear and also suprisingly
shows that the for uncomplex shapes the WKB format is better than passing
the base udt. However as the number of intersections goes up the udt starts to perform
better
This maybe explained by the fact that the base udt is very complex. It stores the Z
and M values. With the WKB format these are not included.
It is also worth noting that for small shapes < 100 points it seems WKB is
always quicker and if you need Z and M values WKT is quicker than the base
udt.
-