HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

What is the optimized way to pass multiple parameter from a single table to a function?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thewhatpasstablewayoptimizedfunctionsinglemultiplefrom

Problem

I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result.
Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring same data.
I have tried:

SELECT [dbo].[FN_CalcAerialDistance]
    (
       (select latitude from loc.locations where place_name = 'Delhi'),
       (select longitude from loc.locations where place_name = 'Delhi'),
       (select latitude from loc.locations where place_name = 'Mumbai'),
       (select longitude from loc.locations where place_name = 'Mumbai')
    )



Any way to optimise the code? I have also tried to access table datatype like an array

Solution

As a complement to Edgars answer you can do it without declaring any variables as:

SELECT dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name = 'Delhi'
  AND y.place_name = 'Mumbai' ;


I wouldn't expect it to perform any better though.

In addition you can calculate several distances at once

SELECT x.place_name
     , y.place_name
     , dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name in ('Delhi', 'Bangalore') 
  AND y.place_name = 'Mumbai' ;

Code Snippets

SELECT dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name = 'Delhi'
  AND y.place_name = 'Mumbai' ;
SELECT x.place_name
     , y.place_name
     , dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name in ('Delhi', 'Bangalore') 
  AND y.place_name = 'Mumbai' ;

Context

StackExchange Database Administrators Q#195775, answer score: 14

Revisions (0)

No revisions yet.