|
I recently converted a customer from SQL Anywhere 9 to version 12. The system performance is overall AMAZING - both due to better hardware as well as the SQL Anywhere software. I'll even go so far to say that the SOFTWARE is the greater contributor to the performance gains. However, despite the general 50% - 150% overall performance gain, Java-based functions are creeping. I have a super simple sample below ... Under SQL Anywhere 9, the function processed 4579 rows in 0.953 seconds (4800 rows per second). Under SQL Anywhere 12, it ran in 10.874 seconds (421 rows per second). Below is the code ... HELP! CREATE FUNCTION "dbo"."GetNumerics"( in inputString varchar(500) ) returns varchar(500) external name 'com.gemtrak.general.StandardFunctions.getNumerics (Ljava/lang/String;)Ljava/lang/String;' language java package com.gemtrak.general; public class StandardFunctions { public static String getNumerics(String inputString) { StringBuffer input = new StringBuffer(inputString); StringBuffer output = new StringBuffer(); int theLength = input.length(); char c; for ( int i=0; i<theLength; ++i ) { c = input.charAt(i); if ( c >= '0' && c <= '9' ) { output.append(c); } } return output.toString(); } } |
|
Try using Character.isDigit() instead of c >= '0' && c <= '9' comparison and the inputString.charAt(i) insteaof input.charAt(i) (use the StringBuffer method). I'm not expert on SqlAnywhere, but I think this is going to help you to run your function faster. |
|
It doesn't explain the performance difference with respect to the external Java calls, but the following FAQ deals with several ways to code an user-defined SQL function to just return the digits from a string input. In this particular case, this might be much more performant than an external Java call - obviously depending on the length of the strings, their "digit" quote and the number of calls (with different input) per query. It could be much worse, on the opposite - one would have to try and test:) |
How are you invoking said function?
select dbo.getnumerics(isnull(telephone1,'')) t1, * into #x from locinfo where loctype = 'C'
8613 row(s) affected Execution time: 20.453 seconds