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();
    }
}

asked 17 Jan '12, 10:42

Frum%20Dude's gravatar image

Frum Dude
136339
accept rate: 0%

edited 20 Jan '12, 12:20

Siger%20Matt's gravatar image

Siger Matt
3.2k496697

How are you invoking said function?

(17 Jan '12, 11:06) Glenn Paulley

select dbo.getnumerics(isnull(telephone1,'')) t1, * into #x from locinfo where loctype = 'C'

(17 Jan '12, 12:25) Frum Dude

8613 row(s) affected Execution time: 20.453 seconds

(17 Jan '12, 12:28) Frum Dude
Comment Text Removed

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.

permanent link

answered 18 Jan '12, 05:15

Alex's gravatar image

Alex
1.0k174053
accept rate: 25%

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:)

permanent link

answered 18 Jan '12, 06:35

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×242
×65

question asked: 17 Jan '12, 10:42

question was seen: 1,144 times

last updated: 20 Jan '12, 12:20