Optimizing Geometry.STIntersect Query

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