April 2, 2015 at 7:00 am
Hello SQL world! I have a problem and need your help. I’ve been working with SQL Server for a long time but have only recently encountered the need to work with spatial data. I have come into an environment that uses it pretty heavily and my first real challenge is to get queries to run faster (and stop timing out) against a table with a geography data type (and over 99 MILLION rows).
We use a query that identifies all geography points in this table that are found within polygons and multi polygons. I have NO friggin’ idea how to performance tune this beast!
I have identified the clustered index as being a bit larger than necessary and intend to add an identity column to do two things: 1) Reduce the size of the clustered index. 2) Eliminate page splitting for inserts. Although I expect to get some relief from doing this, I am not optimistic that it will help the spatial queries very much.
Given my almost complete lack of knowledge/experience with spatial data, I am asking you folks for help/guidance.
Table Design:
CREATE TABLE [dbo].[ProblemChild](
[Phone] [char](10) NOT NULL,
[Lat] [float] NOT NULL,
[Lon] [float] NOT NULL,
[Geog] [geography] NOT NULL,
[Recordsource] [varchar](2) NOT NULL
CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED
([Phone] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Index design:
CREATE SPATIAL INDEX [IDX_geog] ON [dbo].[[ProblemChild]]
([Geog]
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 20, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Number of records: 99,155,267
MinLatMinLonMaxLatMaxLon
18.957356 -166.51239471.292528 -66.967883
April 2, 2015 at 3:31 pm
When I first started using spatial indexes, I found this article very helpful. It does a pretty good job at explaining some spatial indexing concepts and how to tune spatial indexes.
http://boomphisto.blogspot.com/2011/04/black-art-of-spatial-index-tuning-in.html
April 3, 2015 at 8:37 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply