I have found that custom functions are pretty slow.  A simple SELECT
takes 10 times longer when a custom function is called than when it is
not.  This slowdown occurs even with a null custom function.  Is there
anything I can do to speed them up?


  Oliver

Oliver,

<< I have found that custom functions are pretty slow.  A simple SELECT
takes 10 times longer when a custom function is called than when it is not.
This slowdown occurs even with a null custom function.  Is there anything I
can do to speed them up? >>

Does your custom function have parameters ?  Most likely that is the biggest
slowdown due to the way that the parameters must be shuttled back and forth
to the TDBISAMParam objects that are used for exchanging this data.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Does your custom function have parameters ?  Most likely that is the biggest
> slowdown due to the way that the parameters must be shuttled back and forth
> to the TDBISAMParam objects that are used for exchanging this data.

Yes it does have parameters, and perhaps creating all these objects
might be slow.  I don't suppose you would consider an alternative,
quicker mechanism?  e.g. function pointers or an array of Variants.
Another option would be to create the parameters for each custom
function when the function was registered and then reuse them for each
call, just substituting in a new variant for each call.  That should be
reasonably quick.


  Oliver

Further to my last message, I have been doing some experiments with
DBSYS against my DBISAM server application, using a table with 25000
records.

1. Select a single currency field from the table: 0.25s.
3. A custom function taking no parameters: 43s
2. A custom function taking a single parameter from the table: 49s.
4. A custom function taking two parameters (this is my favourite),
   one from the table and one a constant boolean: 24s
5. (just for interest) A custom function taking five parameters,
   two from the table and three constant: 51s

These numbers may look all over the place, but they are easily replicable!

For all the above tests, my OnCustomFunction looks like this:

 procedure TServer.CustomFunction(sender: TObject; const name: string;
params: TDBISAMParams; var res: Variant);
 begin
    res := 0.0;
 end;

I added the following to my CustomFunction to see what the cost of
creating parameters is:

 p := TDBISAMParams.Create(nil);
 p.CreateParam(ftCurrency, 'blah');
 p.ParamByName('blah').AsCurrency := 1.0;
 p.Destroy;

and the numbers did not change, so the overhead of creating parameters
is not to blame.

Then I decided to comment out the declarations (ie. calls to
Engine.Functions.CreateFunction()) for all my other 25 custom functions,
leaving only the function from example (2) above.  Example (2) then took
8.5s, which is still unreasonably slow, but indicates that the function
name lookup is a big part of the problem.


  Oliver

Oliver


I'd like to try the same tests in ElevateDB just to see the differences. Can you post the table etc to the binaries?

Roy Lambert

Oliver,

<< Then I decided to comment out the declarations (ie. calls to
Engine.Functions.CreateFunction()) for all my other 25 custom functions,
leaving only the function from example (2) above.  Example (2) then took
8.5s, which is still unreasonably slow, but indicates that the function name
lookup is a big part of the problem. >>

I'll double-check the function name lookup code.   It may be performing a
lookup on each evaluation, and I'll have to see what I can do about caching
the function pointer.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy Lambert wrote:
> I'd like to try the same tests in ElevateDB just to see the differences. Can you post the table etc to the binaries?

The table I was testing contains customer data so I cannot upload it,
but I was able to reproduce the (2) result by creating a table with no
indexes and a single MONEY field containing 25000 entries (SELECT CAST(1
AS MONEY) INTO T FROM GL).  Therefore I suggest you can test any table,
so long as it has enough records.


  Oliver

Tim Young [Elevate Software] wrote:
> I'll double-check the function name lookup code.   It may be performing a
> lookup on each evaluation, and I'll have to see what I can do about caching
> the function pointer.

Thanks, Tim.


  Oliver

Tim Young [Elevate Software] wrote:
> I'll double-check the function name lookup code.   It may be performing a
> lookup on each evaluation, and I'll have to see what I can do about caching
> the function pointer.

Hi Tim,

Have you had a chance to look at this yet?  Can you reproduce it?  Would
you like a copy of my data?  (Although I do not think it is
data-specific, see previous message.)  The slowness we are experiencing
is causing real slowness for customers as their quantity of data
increases, and we have no easy way to speed it up because we use the
custom function for a particular style of rounding (no, not Banker's
rounding that is too complicated to express as SQL (it uses several
temporary variables and so would expand to a very large chunk of nested
IF expressions).

Regards,
  Oliver

Oliver,

<< Have you had a chance to look at this yet?  Can you reproduce it? >>

Yes, and yes.  I should have a new DBISAM build out next week after the new
support plans are rolled out.

--
Tim Young
Elevate Software
www.elevatesoft.com