Wrong optimizer output (execution plan) when are used spatial indexes in query - by michal2

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
0
Sign in
to vote
ID 367772 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/15/2008 5:17:56 AM
Access Restriction Public

Description

Have table with 6M records, with geography spatial column. This column contains only 1 point in each record (gps coordinated of record)
spatial index is made on column geo.

Use this query:
Select top(@count) ID from GeoObj where geo.STDistance(@point) < @km 
... where @point is any GPS coordinate and @km is distance in kilometers.

this query doesn't use spatial index and used table scan !!

this query 
Select top(@count) ID from GeoObj where geo.STDistance(@point) < @km 
order by geo.STDistance(@point)

but in wrong way, query is very slow.

Only fast working query is query with table hint
Select top(@count) ID from GeoObj with (index (SPATIAL_nameH)) 
where geo.STDistance(@point) < @km order by geo.STDistance(@point)

Same situation is when I use STIntersects method

I think there is some issue inside optimizer in time of creating execution plan
Sign in to post a comment.
Posted by bixb0012 on 3/18/2009 at 10:07 AM
Is there going to be a March Cumulative Update? I ask because we are half way through the month, and I haven't seen or heard anything. Also, the SP1 CTP has hit the street, so I am wondering if the CU will be forgone. Is this fix in the SP1 CTP?
Posted by Microsoft on 12/8/2008 at 11:28 AM
We made some chnanges that should improve your results. They made it into SQL 2008 March public cumulative update! New plan costing will kick in automatically for spatial indexes in that release. These changes are also in the next major release of SQL Server, obviously.

Thanks for the data -- it really helped!
Posted by Microsoft on 11/25/2008 at 11:27 AM
Hi Michal,

Just a heads up that we do have a fix in place for this bug, but that we are still in the process of investigating and testing. We will let you know when and how the fix officially makes it through.

Thanks,
Goldie
Posted by bixb0012 on 11/17/2008 at 10:37 AM
Disappointing... This fix was not included in Cumulative Update 1 for SQL Server 2008 ( http://support.microsoft.com/kb/956717/en-us ).
Posted by bixb0012 on 11/10/2008 at 2:12 PM
Michal, I concur. If this issue has been resolved, then we need more details. As stated, will there be a hotfix? Will the fix be incorporated into the next service pack? Last I checked, nothing sounding similar to this has been posted to other MS support sites.
Posted by michal2 on 11/4/2008 at 12:59 PM
Hi. You closed this bug and set up it resolved. Could you send some info about it?
Was it bug or not?
If yes, how fix will be published? (new version, postfix, service pack....)

Thanks
Michal
Posted by michal2 on 10/21/2008 at 11:49 AM
You have it in your mail.
Posted by Microsoft on 10/21/2008 at 9:39 AM
OK, send me email with instructions to get the database file or .bak file or scripts.

Thanks,
Marc
Posted by michal2 on 10/21/2008 at 7:34 AM
Hi Marc. I'm very sorry, I was offline for a while.

there is same behaviour of query if you call it with params (@count & @point) or without parameters.
I tested it mostly without parameters (with static values instead parameters) and some time with parameters too and results was same.

Is there another way how to help you with it? I can sent you (you can download it from our server) my database and you can try it on it directly.
Posted by bixb0012 on 10/9/2008 at 8:07 AM
I will contact you directly. I don't mind having the data become available, but the dataset is probably a little big to attach to this thread. We can discuss over e-mail how to transfer it.
Posted by Microsoft on 10/8/2008 at 9:22 AM
That would be great -- please contact me directly by removing one part of my address below. Or, if you don't mind it being public, I think you can attach a file to this thread.

Marc Friedman
marcfr@donotreply.microsoft.com
Posted by bixb0012 on 10/8/2008 at 6:40 AM
Marc, I have had several issues with the query optimizer choosing to ignore spatial indexes. Usually, monkeying with the spatial indexes and rebuilding them can get the QO to behave better. How dependent on the spatial index settings is the QO's choice of using the spatial index? In most cases, wouldn't a less-than-ideal spatial index be better than a full table scan?

I have one readily packaged example I can send someone at MS, just let me know how to send it.
Posted by Microsoft on 10/6/2008 at 5:25 PM
To make progress on this, it would be very helpful if you could provide a stand-alone reproduction of the behavior, so I will know if I have fixed it. That usually means a database & a query I can run in Management Studio.

Thank you,
Marc
Posted by Microsoft on 9/23/2008 at 6:14 PM
Dear Michal

Thanks for your feedback... Can you please let us know whether the variable you pass is a parameter or a variable? Note that in the first case, the compiler may have compiled a plan based on the first invocation that is not well suited for your current value, or in the second case, the compiler does not get a good estimate (because of it being a variable) and therefore the index does not get chosen.

We are certainly looking into improving the optimizer's accurracy in picking the right plan, but for now you are best served with hinting the index in the query.

Best regards
Michael
Posted by bixb0012 on 9/16/2008 at 2:13 PM
I don't think this issue, i.e., query optimizer behaving strangely with geospatial indexes, is new to the SQL Server development team; however, I think it is one that absolutely needs to be addressed and quickly. I have run into many problems with the query optimizer choking on spatial indexes and choosing not to use them and performing long table scans instead. Using hints works in some situations, but sometimes the user does not have direct control over the SQL being passed to the database engine, such as with third-party products like ESRI's ArcSDE.

Geospatial data may be new to SQL Server, but it is apparent the query optimizer needs improvement in this area.