SA-12: How to speed up call external function?

[Former Member]

I migrate a old database from ASA 7.0 Win to a new platform 12 Linux x32. The database uses an external library function implements DES encryption. Having dealt with the new interface of external calls, i made the .so library on Linux. But tests have shown that call external functions are now much more slowly than it did under the old interface. In my case, it is absolutely not acceptable. In operating mode, I need to call the DES encryption for 4-6 fields on a select of 100-300 thousand records. Make the function of encryption on the client side is also impossible, since "open" data should not leave the secure storage (

The question is whether there is at least some features or techniques to speed up the external call? Maybe I have something did not finish in the documentation or in implementation of the library. Maybe someone else has encountered this problem?

A external procedure declaration in the database:

ALTER PROCEDURE "DBA"."dll_Encrypt"( inout D char(16),in K char(16) ) no result set
external name 'xp_des_encrypt@libsacrypt.so' language C_ESQL32

The function code is used in Select:

ALTER FUNCTION "DBA"."Encrypt"(in D char(16),in K char(16))
returns char(16)
begin
  call DBA.dll_Encrypt(D,K);
  return(D)
end

"C" function is based on the example of samples SA12

_VOID_ENTRY xp_des_encrypt( an_extfn_api *api, void *arg_handle ) {
  char *Data, *Key;
  an_extfn_value    arg;
  an_extfn_value    retval;

  if( !api->get_value( arg_handle, 1, &arg ) || (arg.data == NULL) ) {
    return;
  } else {
    Data = (char *)arg.data;
  }

  if( !api->get_value( arg_handle, 2, &arg ) || (arg.data == NULL) ) {
    return;
  } else {
    Key = (char *)arg.data;
  }

//  des_encrypt(Data, Key); /*The call is blocked for the tests*/

  retval.type = DT_FIXCHAR;
  retval.data = Data;  //Same as input for test
  retval.piece_len = retval.len.total_len = DES_LEN;
  api->set_value( arg_handle, 0, &retval, 0 );

  return;  
};

[VolkerBarth]

Could you get rid of the encrypt call altogether by using the builtin ENCRYPT()/DECRYPT() functions introduced with v9?

If so, I guess they are much faster than an external call can be...

And the builtin AES cipher is definetely superior to DES:)

[Former Member]

Unfortunately in this case, DES is required. External client should receive the data from this database is encrypted with DES.

[VolkerBarth]

BTW, if you use your external procedure as function, wouldn't it be easier to define it as such?

CREATE FUNCTION "DBA"."dll_Encrypt"(D char(16), K char(16)) RETURNS char(16) 
[ NOT ] DETERMINISTIC
external name 'xp_des_encrypt@libsacrypt.so'

[Former Member]

This design, as described by me, is historical:). You specify the optimum method of course and will be implemented, but for compatibility must be maintained and an old challenge.

[VolkerBarth · Accepted solution]

Starting with v11, you can use an external environment to run your external procedures, i.e. to let them run with their own process. - However, for C/C++ based calls, you do not need to do so - you can still run external procedures in the database server's process.

When omitting an external environment specifier in the CREATE PROCEDURE declaration, you will force the external library to be loaded and executed in the database server's process, which - by design - will be faster than starting an external process. Of course, this leaves the risk of damaging the database server's process memory when the external procedure is buggy.

So the following should do to let your procedure run as with v7:

ALTER PROCEDURE "DBA"."dll_Encrypt"( inout D char(16),in K char(16) ) no result set
external name 'xp_des_encrypt@libsacrypt.so';

Some more info on this topic can be found in this FAQ.

[Former Member · Accepted solution]

I tried this method. However, the function returns the original data. Then I read the documentation:

Libraries written to the older interface, used in versions before version 7.0.x, are still supported, but in any new development, the new interface is recommended. Note that the new interface must be used for all Unix platforms and for all 64-bit platforms, including 64-bit Windows.

After that I kept trying to work as before. But if you believe that the old call to work, then I will look for my bugs.

[VolkerBarth · Accepted solution]

There's a (very understandable) misunderstanding:

The "older" interface refers to an API that was "old" even in v7. - That's a different topic than the question whether to use an external environment or not.

You are using the "newer" interface when your DLL contains the extfn_use_new_api method, and that function returns not 0.

You may look here for further clarification - though the words "external" are somewhat indifferent used there... However, I can confirm that you can use external C calls with the "newer" API as "in-process external calls".

[Former Member · Accepted solution]

Thanks for your help. I totally take care of the 'native-call'. It is important to accurately specify the number of out parameters in the api-> set_value:) On "select" of a 250K records with encryption of one field and decrypt of one field difference in query execution time 2-4 sec (30sec all time), in favor of the ASA 7.0, but this is not critical.

[VolkerBarth · Accepted solution]

Glad you got it working!

If your code is calling the external function n thousand times per SELECT, I surely expect the external environment to perform very bad - the cited small penalty will obviously sum up to a noticeable amount:

Running a compiled native function in an external environment instead of within the database server results in a small performance penalty.