Oppolzer - Informatik / Blog

If it is absolutely necessary to do this computations in the SQL,
I would try to do it without the burden of the stored proc processing,
and without calling two nested stored procs, which makes it worse.
That is: put the whole computation of the distance into the calling
SQL statements, although the resulting expressions may become
very complex. But this may still be much cheaper than to go through
the stored proc processing two times.

If this does not seem feasible to you, I would at least suggest to turn
the two levels of the stored procs into one, by removing the ARC function
and moving its logic into the DIST function.

Maybe there is a simpler way to compute the distance?

The only reason for doing this in SQL is if you have very selective
WHERE conditions on this. Otherwise you could do the computation
in the application, after selecting the data from DB2. Or, another
suggestion:

you select the data with a condition that is much easier to compute (maybe
a bit too many data) and do the fine check afterwards using application
logic.

Kind regards

Bernd



Am 22.12.2013 11:59, schrieb R.B.:

Hmmm.. Could be a LE problem with those math functions but I seriously
doubt it ... I have never seen a math function cause CPU spikes before...

R.B.



On 22 Dec 2013, at 03:47, unknown wrote:

The code appears to be:

CREATE FUNCTION "HMS "."DIST "
(
"LAT1 " DECIMAL(9,6)
,
"LNG1 " DECIMAL(9,6)
,
"LAT2 " DECIMAL(9,6)
,
"LNG2 " DECIMAL(9,6) )
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
CALLED ON NULL INPUT
CONTAINS SQL
SPECIFIC "DIST "
RETURN 9 * ATAN (SQRT(1 -
POWER(HMS.ARC(LAT1,LNG1,LAT2,LNG2),2))
/ HMS.ARC(LAT1,LNG1,LAT2,LNG2))
RETURNS DECIMAL(9,5);

CREATE FUNCTION "HMS "."ARC "
(
"LAT1 " DECIMAL(9,6)
,
"LNG1 " DECIMAL(9,6)
,
"LAT2 " DECIMAL(9,6)
,
"LNG2 " DECIMAL(9,6) )
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
CALLED ON NULL INPUT
CONTAINS SQL
SPECIFIC "ARC "
RETURN (SIN(RADIANS(LAT1)) * SIN(RADIANS(LAT2))) +
(COS(RADIANS(LAT1)) * COS(RADIANS(LAT2)) *
COS(RADIANS(LNG2) - RADIANS(LNG1)))
RETURNS DOUBLE;


-----End Original Message-----

-----End Original Message-----

-----End Original Message-----