Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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 '' 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)
  call DBA.dll_Encrypt(D,K);

"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 ) || ( == NULL) ) {
  } else {
    Data = (char *);

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

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

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


asked 07 Oct '11, 08:43

morry's gravatar image

accept rate: 0%

edited 07 Oct '11, 10:33

Volker%20Barth's gravatar image

Volker Barth

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

(07 Oct '11, 08:46) Volker Barth

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

(07 Oct '11, 09:02) morry

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) 
external name ''
(11 Oct '11, 06:49) Volker Barth
Replies hidden

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.

(11 Oct '11, 10:17) morry

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 '';

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

permanent link

answered 07 Oct '11, 09:39

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 07 Oct '11, 09:41

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.

(07 Oct '11, 09:54) morry
Replies hidden

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".

(07 Oct '11, 10:30) Volker Barth
Comment Text Removed

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.

(10 Oct '11, 10:30) morry

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.

(10 Oct '11, 10:44) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 07 Oct '11, 08:43

question was seen: 2,502 times

last updated: 11 Oct '11, 10:17