patternsqlModerate
What is the optimized way to pass multiple parameter from a single table to a function?
Viewed 0 times
thewhatpasstablewayoptimizedfunctionsinglemultiplefrom
Problem
I have a function
Now that 4 parameters come from the
I have tried:
Any way to optimise the code? I have also tried to access table datatype like an array
[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:
I wouldn't expect it to perform any better though.
In addition you can calculate several distances at once
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.