7

I am attempting to move simple Geoprocessing routines from ESRI based processes to SQL Server. My assumption is that it will be far more efficient. For my initial test I am working on an intersection routine to associate overlapping linear data.

In my WCASING table I have 1610 records. I am trying to associate these Casings with their associated mains. I have ~277,000 Mains. I have ~1,600 Casings.

I am running the query below to get a general sense of how long it will take to find individual matches. This query returned 5 valid intersections in 40 seconds.

SELECT Top 5 [WCASING].[OBJECTID] As CasingOBJECTID, 
    [WPUMPPRESSUREMAIN].[OBJECTID] AS MainObjectID, [WCASING].[Shape]
FROM [dbo].[WPUMPPRESSUREMAIN]
    JOIN [WCASING] 
        ON [WCASING].[Shape].STIntersects([WPUMPPRESSUREMAIN].[Shape]) = 1

My Primary questions;

Will this process faster depending on the search order?

  • Finding 'A' inside of 'B' vs
  • Finding 'B' inside of 'A'
  • Initial return on 5 records from these datasets is that it does not matter

Will this process faster, if I first buffer to limit to a smaller main set and then search?

Can I use SQL Server Tuning to work with Geometry based queries?

SELECT WCASING.OBJECTID AS CasingOBJECTID,
    WPUMPPRESSUREMAIN.OBJECTID AS MainObjectID, WCASING.UFID AS UFID,
    WPUMPPRESSUREMAIN_IPS.UFID AS MainUFID, WCASING.SHAPE
INTO WCASING_INTDefsV6
FROM WCASING with (index([FDO_ShapeWC])) 
    INNER JOIN [WPUMPPRESSUREMAIN_IPS] ON 
        [WPUMPPRESSUREMAIN_IPS].Shape.STIntersects(WCASING.SHAPE) = 1

This new query has improved definitions.

  • Now both tables have spatial indexes
  • Previously Casing Table (Smaller) did not have a Spatial Index
    • It did contain a Non-Clustered Index

The query also has the with index statement.

The new query took 37 minutes. The old query took 44 minutes.

I was hoping for better results and will keep testing.

  • 1
    fun, isn't it!? – DPSSpatial May 4 '15 at 17:47
  • It's Awesome. I have been in a new position for a year, working with very large datasets and the learning curve w/SQL Server has been the most enjoyable part. I am still a noob in many ways, but I have learned some cool stuff. – Rick Monteiro May 4 '15 at 18:32
  • 1
    We've been using SQL Server spatial data and functions for our analysis for almost 2 years and we're not turning back any time soon!!! – DPSSpatial May 4 '15 at 18:45
  • I would expect forcing the index on the larger table (mains) would have a better performance. – MickyT May 5 '15 at 3:08
6

I'm assuming that you are using Geometries, but the methodologies remain mostly the same.

When tuning spatial queries, these are the steps I take

  1. This is the most important step. Check the indexes are suitable for the table. If you have SQL Server 2012+ then I would suggest that you use the AUTO GRID. This gives you a finer grid. Make sure the extents cover the data and do not extend to far out from it. If indexing polygons or lines then try and determine a sensible Cells Per Object value. The spatial indexing overview is quite good at explaining it.

  2. Write the query in it's simplest form first. Check the Estimated Execution Plan to insure that the spatial index you expect is being used. Run the query and get the timings and IO statistics. An actual execution plan would also be helpful. This will be your baseline.

  3. Try variations of the query, including swapping the order of the geometries in the geometry comparison, etc. Gather statistics for each.

  4. Try breaking the query in smaller parts if possible (divide and conquer). Sometimes this results in an overall quicker execution.

  5. If all else fails and you can't get your queries to use the spatial index, use the index hint. This is a last resort and potentially means there may be issues with the indexing on the table. That said the optimizer doesn't always get it right.

Look at execution plans and spend a bit of time trying to understand them. They are your friend really. There are also tools out there that make them easier to interpret. The one I use is SQL Sentry.

As for the query you posted, I think that will be the best performer, but here is a variation you may want to try. Also the TOP operator will be affecting the way the execution plan is built, so it will mean that you probably won't get a good comparison between different methods.

SELECT c.[OBJECTID] As CasingOBJECTID, 
    x.[OBJECTID] AS MainObjectID, 
    c.[Shape]
FROM [WCASING] c -- Smallest Table
    CROSS APPLY (
        -- This query is essentially done for each casing
        SELECT p.[OBJECTID]
        FROM [dbo].[WPUMPPRESSUREMAIN] p -- Largest table
        WHERE p.[Shape].STIntersects(c.[Shape]) = 1 -- should use the pressure main spatial index
        ) x
1

What I have found speeds these intersection queries up the most is forcing the spatial index:

In a view I deliver to BI which intersects ~280,000 address points with ~300 boundary polygons, I force the use of the address point spatial index:

...

FROM [dpsdata].[Address_Master] as am with (index(SIndx_AddrMsterIC))
  left outer join [dpsdata].[SchoolBoundaries_All_Projected] as sbp
  on (am.shape.STIntersects(sbp.shape) =1)

(Assuming both tables have spatial indices built already of course...)

This takes close to 1 min to run, down from several minutes.

In your case, something like this should work:

SELECT Top 5 [WCASING].[OBJECTID] As CasingOBJECTID, 
    [WPUMPPRESSUREMAIN].[OBJECTID] AS MainObjectID, [WCASING].[Shape]
FROM [dbo].[WPUMPPRESSUREMAIN] with (index(PK__WPUMPPRE__E458E6E7F06C9A87)) 
    JOIN [WCASING] 
        ON [WCASING].[Shape].STIntersects([WPUMPPRESSUREMAIN].[Shape]) = 1
  • I tried this. It did not work the way I attempted to implement it. "SELECT Top 5 WCASING.OBJECTID AS CasingOBJECTID, WPUMPPRESSUREMAIN.OBJECTID_1 AS MainObjectID, WCASING.UFID, WCASING.SHAPE with (index(PK__WPUMPPRE__E458E6E7F06C9A87)) INTO WCASING_INTDefs_V2 FROM WCASING INNER JOIN WPUMPPRESSUREMAIN ON WPUMPPRESSUREMAIN.Shape.STIntersects(WCASING.SHAPE) = 1" returns an error. "Msg 319, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'with'. If this statement is a common table expression,..." – Rick Monteiro May 4 '15 at 19:29
  • I have not used the 'with' option before. Is this specific to a field or to the From statement in general. Can I have two 'with' statements one for each table? Second question, should the index be a spatial index? – Rick Monteiro May 4 '15 at 19:33
  • 1a) 'with' is used in this case in the FROM statement and 1b) I don't know if it will work if you use 2 'with' statements... 2) I've only tried this with a spatial index – DPSSpatial May 4 '15 at 19:35
  • 1
    I updated my answer to show how you might use your spatial index in the query in your original post... – DPSSpatial May 4 '15 at 19:37
  • 1
    @RichardMonteiro sorry I forgot the 'with' before the spatial index in my answer using your example ... I have updated. – DPSSpatial May 5 '15 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.