February 23, 2012 at 9:02 am
The 3rd party app I manage has a table (600K rows, 200K of those are active) with lat/long coordinates for addresses. I have a small (<500 rows) table of subdivisions with geography polygons. I can query for a list of active addresses that lie within a given subdivision by joining the tables using
geography::STGeomFromText('POINT(' + [LONGITUDE] + ' ' + [LATITUDE] + ')',4326).STIntersects([subdivision]) = 1
, and it takes about 7 seconds.
Now I'm wondering how much overhead is involved in creating the geometry for the point instead of having it stored in a table, and it seems like this is a good opportunity to try to learn to use a spatial index and see if it helps. So I create a table of the active records with only two columns: the addressID (as pk) and the geography point (no spatial index yet). Estimated query plans between the old query and the new (with the lat/long table replaced with my new geography table) show 78%/22%. Running the new query, however, is at 45 minutes and counting.
What the heck did I do?
February 23, 2012 at 12:46 pm
Sounds like the index is no good, but I'll bet you already figured that out.
To get started with spatial, I'd pick up a copy of Alistair Aitchison's book, Beginning Spatial. Excellent resource.
As to your immediate issue, try changing the density of the different layers of the index. I've found that makes a huge difference in performance. I blogged about it here.[/url] There are also a few other blog posts discussing indexing & spatial data there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2012 at 2:43 pm
That's what's weird--I never got to building the spatial index. The only index on the new table is the primary key on the addressID.
The only change was going from building the geography point on the fly from lat & long values, to reading that point out of a geography type column.
February 23, 2012 at 2:50 pm
Oh, well then... maybe you need an index.
Oh, and are you on the latest service pack? They had quite a few issues with the RTM version and SP1 didn't fix everything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2012 at 8:25 am
I'm on 2008 R2 SP1, but no CUs.
I've found that if I remove a particular table from the query (a simple inner join on id=id and active=1; this one doesn't touch any of the geography), then it works as I expected, meaning with almost no difference in performance. It did lead me to my first answer--building the geography point in the query didn't add significant cost; I could easily see the compute scalar operation in the plan. Yay, learning experiences!
I added a spatial index on both tables involved, and saw some improvement in the simplified query. However, I still can't get anything when I use the full query with my new table--I guess the optimizer is building a bad plan?
Everything still works fine with the production query, but it doesn't seem to want to use the spatial index--I saw some good stuff in the post you linked, so time to move on and try to digest that when I can get back to this.
Thanks for the guidance!
February 27, 2012 at 9:54 am
try pulling up an estimated plan to see what it's trying to do. Then you can compare the good execution with the bad one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply