Using MS SQL Server 2012, when we write a query with
select GEOM.STEnvelope() from table
where table holds approx. 4000 native SQL Server line geometries of type
geometry, with 2/6/180 points min/avg/max each, the query takes 18 seconds.
This query is generated by third-party software, so we cannot easily replace it . (Please don't judge the third party devs too harshly, they try for a database-agnostic tool which of course has it's drawbacks.) Ultimately the query tries to get the bbox of the whole table, so the third party tool wraps it in something like
select min(p0.STX) as x1,min(p0.STY) as y1,MAX(p1.STX) as x2,MAX(p1.STY) as y2 from ( select bbox.STPointN(1) as p0,bbox.STPointN(3)as p1 from ( select GEOM.STEnvelope() as bbox from ( select ... from table ) ) )
The replacement in TSQL (minus some fancy column naming and vertex extraction from the bbox is thus
select geometry::EnvelopeAggregate(GEOM) from table
which, as expected, returns almost immediately.
(How) can we reduce the cost of calling .STEnvelope() on our table?