I am seeding a database by copying over data from another database on the same server. In one of the tables, I am doing an INSERT INTO SELECT statement to bring over a few columns. While existing and later data may have geometry, the data that I am inserting has no geometry data.
The query is exceptionally slow, and when I examine the plan, I see that major culprit is the sort that occurs after the GetPlanarGeometryTessellation_VarBinary function. Basically, SQL Server estimates 1,000 rows coming out of this function for each row I am inserting. Since my set contains hundreds of thousands of rows, the plan estimates hundreds of millions of rows going into the sort.
The worst part is the geometry values are NULL, so I am not even explicitly inserting a value into that column!
I tried using the legacy cardinality estimator to no avail. Neither does trying to explicitly define the column as NULL short circuit that part of the execution plan.
I have tried this on both SQL Server 2022 and Azure Managed Instance.
Is this just a bug in SQL's execution engine's estimation and query execution generation? Any suggestions on how I might be able to speed up such a query?
Notice the sort and clustered index insert are 79% of the query, even though I am not putting a value in the column.
CREATE TABLE MyObjects (
ID UNIQUEIDENTIFIER,
my_value SMALLINT,
geom GEOMETRY,
CONSTRAINT [PK_SMS_ManagedObject] PRIMARY KEY CLUSTERED
([Id] ASC)
)
CREATE SPATIAL INDEX [IX_MyObjects_Geom] ON [MyObjects]
(
[Geom]
)USING GEOMETRY_GRID
WITH (BOUNDING_BOX =(-90, -180, 90, 180), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT INTO dbo.MyObjects
(
ID,
my_value
)
VALUES
(
NEWID(),
100
)
