February CTP Optimizer Issue

Hi Folks,

 

We don't call them betas, but that's basically what CTPs are: they let us suss out problems before we drop a final product on everyone.  Well, we've found a regression in the February CTP spatial support that we'd like to let you know about.

 

Essentially, a costing problem was introduced that leads us to choose poor plans for spatial queries.  This seems to usually manifest itself by choosing merge joins instead of loop joins between the spatial index and the base table, and that generally doesn't work too well.

 

Why is a merge join such a bad plan in this case?  If you've been following my indexing posts, you'll understand that we generally end up with a small number of rows that pass our primary filter (our index).  We have to then join those back to the base table to pull out the spatial object to which the index row refers.

 

This is usually best done through a loop join that runs over each index row and seeks into the base table for each of the objects.  However, if a merge join is chosen, then we risk scanning the entire base table to feed into our merge.  This is usually not a wise choice.

 

What can you do about this if you're running the February CTP?  A good general fix is to force loop joins in your query.  Perhaps the simplest way to do this is by adding an "OPTION (LOOP JOIN)" to the end of your query.

 

On the positive side, this has already been fixed, so you should see better plans in our next public release.

 

Cheers,

-Isaac