Geek Answers Handbook

How to speed up this Sql Server Spatial request?

I have (it seems to me) a simple Sql Server spatial query:

Capture all US states that exist inside some 4-sided polygon (i.e. google / bing webpage map view / limiter window)

SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
    StateId, a.Name, Boundary.STAsText() AS Boundary, 
    CentrePoint.STAsText() AS CentrePoint
FROM [dbo].[States] a
WHERE @BoundingBox.STIntersects(a.Boundary) = 1

It takes 6 seconds to start: (

Here is the implementation plan ....

Removed

And statistics on the filter ...

Removed

Now I'm just not sure how to debug this. To find out what I need to fine tune, etc. Do I have any spatial indexes? I think so...

/****** Object:  Index [SPATIAL_States_Boundary]    
        Script Date: 07/28/2010 18:03:17 ******/
CREATE SPATIAL INDEX [SPATIAL_States_Boundary] ON [dbo].[States] 
(
    [Boundary]
)USING  GEOGRAPHY_GRID 
WITH (
    GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
    CELLS_PER_OBJECT = 1024, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Do I need to provide additional information about the returned data GEOGRAPHY? eg. points, etc.? Or do I need to run profilerand give some statistics from there?

Cells_per_object/Grids ( , , TBH).

- ? ?

UPDATE/EDIT:

@Bobs , , , ( ) , 50 ... ( shits-n-higgles): -

SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
    StateId, a.Name, Boundary.STAsText() AS Boundary, 
    CentrePoint.STAsText() AS CentrePoint
FROM [dbo].[States] a WITH (INDEX(SPATIAL_States_Boundary))
WHERE @BoundingBox.STIntersects(a.Boundary) = 1

... , .. .

WTF? - , ? , , /?

+5
2

, . . .

PK_States. . , , . ? , , States (50 , , , .., - ..).

SQL Server 8 . (. " " ) 8052 , , 50 . , 18 (. " " ). . , , , 50, 50 000 .

, , PK_States SPATIAL_States_Boundry. , , . , , . , , .

, (, ), , .

+6

, :

EXEC sp_executesql N'
  SELECT CAST(2 AS TINYINT) AS LocationType, a.Name AS FullName, 
      StateId, a.Name, Boundary.STAsText() AS Boundary, 
      CentrePoint.STAsText() AS CentrePoint
  FROM [dbo].[States] a
  WHERE @BoundingBox.STIntersects(a.Boundary) = 1'
, N'@BoundingBox GEOGRAPHY', @BoundingBox

, . :

SSMS, sp_executesql ( ), , "" , sp_executesql.

+2

More articles:


All Articles