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?

  • Are the 3rd party devs willing to replace their code with different (but still agnostic) SQL? – Kirk Kuykendall Oct 15 '18 at 16:39
  • I was hoping there was some magic silver bullet that we could use and tada, instant envelopes. But if thats impossible we could try to convince them. What did you have in mind? – til_b Oct 16 '18 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.