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.
|Event ID||Area ID|
The areas are represented in my table as ID values. I need to represent these spatially, so I need to join the
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.