18

I have a query which takes a json string as a parameter. The json is an array of latitude,longitude pairs. An example input might be the following.

declare @json nvarchar(max)= N'[[40.7592024,-73.9771259],[40.7126492,-74.0120867]
,[41.8662374,-87.6908788],[37.784873,-122.4056546]]';

It calls a TVF that calculates the number of POIs around a geographical point, at 1,3,5,10 mile distances.

create or alter function [dbo].[fn_poi_in_dist](@geo geography)
returns table
with schemabinding as
return 
select count_1  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 1,1,0e))
      ,count_3  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 3,1,0e))
      ,count_5  = sum(iif(LatLong.STDistance(@geo) <= 1609.344e * 5,1,0e))
      ,count_10 = count(*)
from dbo.point_of_interest
where LatLong.STDistance(@geo) <= 1609.344e * 10

The intent of the json query is to bulk call this function. If I call it like this the performance is very poor taking nearly 10 seconds for just 4 points:

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from openjson(@json)
cross apply dbo.fn_poi_in_dist(
            geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326))

plan = https://www.brentozar.com/pastetheplan/?id=HJDCYd_o4

However, moving the construction of the geography inside a derived table causes the performance to improve dramatically, completing the query in about 1 second.

select row=[key]
      ,count_1
      ,count_3
      ,count_5
      ,count_10
from (
select [key]
      ,geo = geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)
from openjson(@json)
) a
cross apply dbo.fn_poi_in_dist(geo)

plan = https://www.brentozar.com/pastetheplan/?id=HkSS5_OoE

The plans look virtually identical. Neither uses parallelism and both use the spatial index. There is an additional lazy spool on the slow plan that I can eliminate with the hint option(no_performance_spool). But the query performance does not change. It still remains much slower.

Running both with the added hint in a batch will weigh both queries equally.

Sql server version = Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)

So my question is why does this matter? How can I know when I should calculate values inside a derived table or not?

  • 1
    By "weigh" do you mean estimated cost %? That number is virtually meaningless, especially when you're bringing in UDFs, JSON, CLR via geography, etc. – Aaron Bertrand May 2 at 14:53
  • I'm aware, but looking at IO stats they are identical too. Both do 358306 logical reads on the point_of_interest table, both scan the index 4602 times, and both generate a worktable and workfile. The estimator believes these plans are identical yet performance says otherwise. – Michael B May 2 at 14:59
  • It seems like actual CPU is the issue here, likely due to what Martin pointed out, not I/O. Unfortunately estimated costs are based on CPU and I/O combined and don't always reflect what happens in reality. If you generate actual plans using SentryOne Plan Explorer (I work there, but the tool is free with no strings), then change actual costs to CPU only, you might get better indicators of where all that CPU time was spent. – Aaron Bertrand May 2 at 15:19
  • 1
    @MartinSmith Not per operator yet, no. We do surface those at the statement level. Currently we still rely on the initial implementation from the DMV before those additional metrics were added at the lower level. And we've been a bit busy working on something else you'll see soon. :-) – Aaron Bertrand May 2 at 15:38
  • 1
    P.S. You may get even more performance improvement by doing a simple arithmetic box before doing the straight-line distance calculation. That is, filter first for those where the value |LatLong.Lat - @geo.Lat| + |LatLong.Long - @geo.Long| < n before you do the more complicated sqrt((LatLong.Lat - @geo.Lat)^2 + (LatLong.Long - @geo.Long)^2). And even better, calculate the upper and lower bounds first, then LatLong.Lat > @geoLatLowerBound && LatLong.Lat < @geoLatUpperBound && LatLong.Long > @geoLongLowerBound && LatLong.Long < @geoLongUpperBound. (This is pseudocode, adapt appropriately.) – ErikE May 2 at 18:07
15

I can give you a partial answer that explains why you are seeing the performance difference - though that still leaves some open questions (such as can SQL Server produce the more optimal plan without introducing an intermediate table expression that projects the expression as a column?)


The difference is that in the fast plan the work needed to parse the JSON array elements and create the Geography is done 4 times (once for each row emitted from the openjson function) - whereas it is done more than 100,000 times that in the slow plan.

In the fast plan...

geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)

Is assigned to Expr1000 in the compute scalar to the left of the openjson function. This corresponds to geo in your derived table definition.

enter image description here

In the fast plan the filter and stream aggregate reference Expr1000. In the slow plan they reference the full underlying expression.

Stream aggregate properties

enter image description here

The filter is executed 116,995 times with each execution requiring an expression evaluation. The stream aggregate has 110,520 rows flowing into it for aggregation and creates three separate aggregates using this expression. 110,520 * 3 + 116,995 = 448,555. Even if each individual evaluation takes 18 microseconds this adds up to 8 seconds additional time for the query as a whole.

You can see the effect of this in the actual time statistics in the plan XML (annotated in red below from the slow plan and blue for the fast plan - times are in ms)

enter image description here

The stream aggregate has an elapsed time 6.209 seconds greater than its immediate child. And the bulk of the child time was taken up by the filter. This corresponds to the extra expression evaluations.


By the way.... In general it is not a sure thing that underlying expressions with labels like Expr1000 are only calculated once and not re-evaluated but clearly in this case from the execution timing discrepancy this happens here.

  • As an aside, if I switch the query to use a cross apply to generate the geography, I also get the fast plan. cross apply(select geo=geography::Point( convert(float,json_value(value,'$[0]')) ,convert(float,json_value(value,'$[1]')) ,4326))f – Michael B May 2 at 16:39
  • Unfortunate, but I am wondering if there's an easier way to get it generate the fast plan. – Michael B May 2 at 17:02
  • Sorry for the amateur question, but what tool is shown in your images? – BlueRaja - Danny Pflughoeft May 2 at 18:23
  • 1
    @BlueRaja-DannyPflughoeft these are execution plans shown in management studio (the icons used in SSMS have been updated in recent versions if that was the reason for the question) – Martin Smith May 2 at 18:26

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.