# Oppolzer - Informatik / Blog

Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie

### DB2-L - Performance-Probleme mit mathematischer UDF

 Subject: From: Bernd Oppolzer Reply-To: DB2 List Date: 2013.12.22 08:48:00

 ```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----- ```