Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column

Question

I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many relationship between the two. A single area may also be affected by multiple events at the same time.

For example:

Event ID	Area ID
1	15
1	31
2	46
3	46
3	55
4	15

The areas are represented in my table as ID values. I need to represent these spatially, so I need to join the event_id and area_id values to a lookup table that contains the area_ids and the geometry. Unfortunately, the tables are in two separate databases on the same SQL Server 2014 instance.

The problem is that this join process is quite slow; it takes anywhere from 90 seconds to 3 minutes to return 18k-24k rows, depending on how many events are active at the moment. The geometry lookup table contains 84k rows.

The events table structure (some additional columns removed for simplicity's sake):

USE Events_DB;
CREATE TABLE [dbo].[Events](
    [EVENTS_ID] [bigint] NOT NULL,
    [AREA_ID] [bigint] NOT NULL,
    [START_DATE_TIME] [smalldatetime] NULL,
    [END_DATE_TIME] [smalldatetime] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
(
    [EVENTS_ID] ASC,
    [AREA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The geometry lookup table structure:

USE Geom_DB;
CREATE TABLE [dbo].[Area_Geom](
    [AREA_ID] [int] NOT NULL,
    [Geom] [geometry] NOT NULL,
 CONSTRAINT [Area_Geom] PRIMARY KEY CLUSTERED 
(
    [AREA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My query is below. I'm using a CTE for the event and area IDs because they're being used in elsewhere in the full query, but I've narrowed down the bottleneck to this particular portion.

WITH active_ids (EVENTS_ID, AREA_ID) AS (SELECT DISTINCT EVENTS_ID, AREA_ID
        FROM EVENTS
        WHERE END_DATE_TIME > getUTCdate())

SELECT geom FROM Area_Geom WHERE area_id IN (SELECT area_id FROM active_ids);

The execution plan shows that the clustered index scan of the primary key index on the Area_Geom table; it's taking nearly 2 minutes to scan through the ~84k rows stored in that table.

I threw this question at one of our company DBAs and he was stumped. Can I do anything else to find some performance improvements? I know the cross-database portion of the join is part of the problem, and I can't do anything about that, unfortunately.

I did note that the two Area_ID values on the two tables are of two different types (bigint and int); however, casting the Area_ID within the CTE to int made little difference in performance.

Answer 1

⛈️⛈️⛈️

Good news! Your query finishes rather quickly!

Bad news! It takes a long time to get do other stuff.

Given the long elapsed time here when compared to CPU, you can bet that the query was either blocked, or receiving the results took a long time.

If you run the same query and dump it into a #temp table so that you're not returning results to SSMS, that should help determine if the bottleneck is server-side or client-side.

WITH 
    active_ids AS 
(
    SELECT DISTINCT 
        e.AREA_ID
    FROM [EVENTS] AS e
    WHERE END_DATE_TIME > GETUTCDATE()
)
SELECT
    a.*
INTO #active_ids
FROM active_ids AS a;

SELECT 
    a.geom 
INTO #Area_Geom
FROM Area_Geom AS a
WHERE area_id IN (SELECT ai.area_id FROM #active_ids AS ai);

SELECT
    ag.*
FROM #Area_Geom AS ag;