Timeline for sql server spatial index performance
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 3, 2018 at 17:50 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 28, 2018 at 22:48 | answer | added | PolyGeo♦ | timeline score: 1 | |
| Mar 28, 2018 at 22:46 | history | edited | PolyGeo♦ |
edited tags
|
|
| Mar 13, 2018 at 5:20 | review | Close votes | |||
| Mar 13, 2018 at 9:24 | |||||
| Dec 27, 2017 at 21:04 | comment | added | flurbius | Depending on your setup you may be better off creating a WKB Geometry to use in your query instead of the WKT, check the output of explain to see if MSSQL is optimizing that much anyway. Also you should make the index with the bounding box and run a test to determine if it is required. Also Explain may indicate what index if any will help | |
| May 19, 2017 at 12:18 | comment | added | AnserGIS | Without an index, SQL can simply use the bounding box as a first pass, which is equally costly regardless of query polygon size (two sorts). With an index, a large polygon will cover more branches of the index so require separate sort operations for each. The actual intersect (as per AKK2's point) would not be affected by the index I think but of course a larger bounding box means more points to check. | |
| Apr 25, 2017 at 12:24 | comment | added | AKK2 | Another thing you have to remember is that an intersect is a complex operation, first it must look if the elements bound intersect, relatively fast operation through indexes, but then for each item that matches, it must calculate if every single item actually intersects, which is yet another, more costly operation, which becomes even more costly as the polygons are more complex and/or more numerous. | |
| Mar 7, 2017 at 17:47 | comment | added | mdm20 | It's been a while since I did this, but I tried every possible index creation syntax possible and never got good results. I was querying to find polygons, not points. In the end, I ended up using geometry stored using 4326 projection and had better results. I stored the original geometry also and would return that to the user. | |
| Mar 5, 2017 at 11:16 | comment | added | Michael | I have used 'Geography Auto Gird' and 'Cells per Object' = 4000. Intersected 110+ million points with ~45K polygons. | |
| Jan 8, 2016 at 16:31 | history | tweeted | twitter.com/StackGIS/status/685498994877960192 | ||
| Oct 20, 2015 at 18:19 | comment | added | user41185 | Are your records representing points? That was not stated. Also, can you publish the create index syntax that you used? Was it AutoGrid? | |
| Mar 21, 2014 at 22:18 | comment | added | Vince | If you think about it, changing the size of the search envelope should have a significant impact on the query -- the more rows that are returned through an index, the slower the response. At some point, it becomes faster to full table scan and throw away rows based on envelope. I would suggest that you spend more time with the spatial index options, since you probably have room for optimization of the index. | |
| Mar 21, 2014 at 22:10 | comment | added | Inactivated Account | I was just going through something similar dba.stackexchange.com/questions/61289/… the other day... I wasn't generating a polygon from text, but was intersecting points and polygons... I specified to use the spatial index on the point, which had great speed results. I then tried using the spatial index on the polygon, and had very poor performance...which seems like the exact opposite of your problem! | |
| Mar 21, 2014 at 20:00 | review | First posts | |||
| Mar 21, 2014 at 20:08 | |||||
| Mar 21, 2014 at 19:44 | history | asked | mdm20 | CC BY-SA 3.0 |