Timeline for How to optimize a query so that it seeks on one index first, and then another index after that
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 8, 2015 at 20:29 | comment | added | Rob Farley | Terrific. If you could add mjd and time to the spatial index, you might find that it started using that instead, which is where I figured breaking the sargability would help too. But the most significant thing is removing the lookups. I'm glad it helped. :) | |
| May 8, 2015 at 17:00 | comment | added | user261963 |
Taking out all table hints seems to work best in the end, so long as I do h between m and not the other way around. The query no longer uses the GeoPoint indexes at all, but it wasn't using them efficiently anyway. I included the GeoPoint column to the MJD index and that helped a lot. select top 10000 h.Time, m.Time, m.GeoPoint.STDistance(h.GeoPoint), h.mjd-m.mjd from L2V5.dbo.header h join L2.dbo.MLS_Header m on m.GeoPoint.STDistance(h.GeoPoint)<200000 and m.mjd between h.mjd-.125 and h.mjd+.125 order by h.mjd
|
|
| May 8, 2015 at 15:43 | vote | accept | user261963 | ||
| May 7, 2015 at 8:33 | history | edited | Rob Farley | CC BY-SA 3.0 |
added 2 characters in body
|
| May 7, 2015 at 8:32 | comment | added | Rob Farley | Suggested some changes with a quick edit. You don't need to hint about m, just h. Although if you can swap which one you're adding 1/8 to, to make sure you're modifying the column from the smaller table and using those values to seek into the larger one, that'll help too. If h is 8M and m is 1M, leave the BETWEEN predicate, and hint for just h. If it's the other way round, change your predicate and hint (but better than changing the hint is to add those columns to your index). | |
| May 7, 2015 at 8:25 | history | edited | Rob Farley | CC BY-SA 3.0 |
added 617 characters in body
|
| May 7, 2015 at 1:48 | comment | added | Rob Farley | ...so then the plan looks like your original one, but has an extra predicate or filter. | |
| May 7, 2015 at 1:46 | comment | added | Rob Farley | Ok. So is it acceptable to locate all the time-matched rows and then check each location without the index? | |
| May 6, 2015 at 22:41 | comment | added | user261963 | I don't know if it changes anything, but the time filter is a lot more selective. | |
| May 6, 2015 at 21:50 | history | edited | Rob Farley | CC BY-SA 3.0 |
added 846 characters in body
|
| May 6, 2015 at 21:38 | history | answered | Rob Farley | CC BY-SA 3.0 |