This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, We are currently having a problem using spatial operations with LightSpeed (v5.0.2335) in which these operations are not taking advantage of spatial indexes in the database. I've tracked down the cause of this to an aspect of how LightSpeed generates SQL. I’ll explain the problem with STIntersect, but we are having exactly the same problem with STWithin, and I’d imagine STContains, STEquals, STOverlaps, and STTouches are also affected. I've attached a sample application that demonstrates some of the queries that aren't using indexes (run the CreateSpatialIndexSampleDatabase.sql script to install the database). The application itself doesn't do anything exciting, but SQL Profiler shows the SQL that LightSpeed generates from the queries. In the DoSpatialQueries() method of the sample app, we have a LINQ query that looks like this:
This gets translated to this SQL:
This SQL executes correctly, but viewing SQL Server’s actual execution plan shows that it isn’t using the spatial index defined on the Region.Boundary column (it’s doing a clustered index scan instead). If we add an index hint to the query (uncomment the call to DoSpatialQueriesWithIndexHints() in the sample app):
The index hint comes through in the SQL:
But executing this SQL results in an error:
I did some Googling and found http://msdn.microsoft.com/en-us/library/bb895373.aspx, which says that spatial indexes are supported for STIntersects (and those other geometry methods) for predicates of the form
i.e. not = 0 – the index can be used to find items that intersect, but can’t be used to find items that don’t intersect (which makes sense when you think about how spatial indexes are implemented). Sure enough, if I take the last query, change it so that the result of the STIntersects to a literal 1 instead of @p1, and execute it directly:
This executes correctly, and the execution plan indicates that the spatial index is indeed being used. (I also tried removing the index hint but comparing to a literal 1, and found that it still doesn’t use the index without the hint, but that’s quite easily worked around, and it’s not LightSpeed’s problem anyway). So “.StIntersects(@p0) = 1” uses the index, but “.StIntersects(@p0) = @p1” can never use the index, and throws an error if you try to force it, even when @p1 is 1. I’m guessing it’s because SQL Server compiles the execution plan before it substitutes the parameters. In a nutshell, what we need is a way for LightSpeed to generate “.StIntersects(@p0) = 1” (literal 1, no parameter substitution) in its SQL. Is there a way to do this? |
|
|
No unfortunately not as we parameterize all variables when constructing the SQL statement and "1" is a variable here. The fallback from this would be to look at a stored proc to handle this or construct a manual command yourself using UnitOfWork.PrepareCommand.
|
|
|
Hi Jeremy, The catch for us, in using a stored proc, is that we are also running the same code in our "unit test mode", where it runs in memory (via Linq to Objects, instead of Linq to LightSpeed). So, if we go down the stored proc route, that complicates things at unit testing time. So is there any way to tell LightSpeed to not treat literal values (such as the 1) as parameters? Or even to not parameterize anything in the query at all? John |
|
|
No we dont have any options for this Im afraid.
|
|