Hi All,
I am doing some test to store some data within a complex structure in an external C module and access it from Oracle using PL/SQL.
It is strange how bad performances are...
Let me try to explain.
If from pl/sql I call a eternal C function that does a big processing, it is performing well.
For example:
The C code:
char *TestBtree(void)
{
char Key[15];
char Result[3000];
char Data[40];
char *pCh;
long C;
t_BTree *Tree;
char CptRendu[11];
char buff[200];
int nNb;
clock_t start, end;
float cputime;
Tree = AllocBT (CptRendu);
nNb = 1000000;
start = clock ();
strcpy (Data, "MyDataValue");
for (C = 0; C < nNb; C++)
{
sprintf (Key, "%d",C);
if (!AddItemInBT (Key, (void*)Data, Tree, CptRendu))
printf ("Error\n");
}
end = clock ();
sprintf (buff, "Load %d keys Done in %f seconds\n", nNb, (double)(end-start) / CLOCKS_PER_SEC);
strcpy (Result, buff);
sprintf (buff, "\n\nSearching keys from 1000000 to 2000000 Press any key\n");
strcat (Result, buff);
nNb = 0;
start = clock ();
for (C = 100000; C < 200000; C++)
{
nNb++;
sprintf (Key, "%d",C);
if (!FindItemInBT (Key, Tree, CptRendu))
printf ("Error Find %s \n", Key);
}
end = clock ();
sprintf (buff, "%d key found in %f seconds\n", nNb, (double)(end-start) / CLOCKS_PER_SEC);
strcat (Result, buff);
sprintf (buff, "\n\nSearching keys from 4000000 to 8000000 Press any key\n");
strcat (Result, buff);
nNb = 0;
start = clock ();
for (C = 400000; C < 800000; C++)
{
nNb++;
sprintf (Key, "%d",C);
if (!FindItemInBT (Key, Tree, CptRendu))
printf ("Error Find %s \n", Key);
}
end = clock ();
sprintf (buff, "%d key found in %f seconds\n", nNb, (double)(end-start) / CLOCKS_PER_SEC);
strcat (Result, buff);
DeleteBT (Tree, CptRendu);
return Result;
}
The PL/SQL function:
CREATE OR REPLACE FUNCTION TestBtree RETURN string IS
external
library sys.LibInMemBtree
name "TestBtree"
LANGUAGE C
calling standard C;
/
And the test case:
SQL> select testbtree from dual;
TESTBTREE
--------------------------------------------------------------------------------
Load 1000000 keys Done in 0.710000 seconds
Searching keys from 1000000 to 2000000 Press any key
100000 key found in 0.050000 seconds
Searching keys from 4000000 to 8000000 Press any key
400000 key found in 0.220000 seconds
Elapsed: 00:00:00.88
SQL>
It is fast and gives the same performance if I run it outside Oracle.
Let's redo a similar test but little bit differently
SQL> create table MyTest as select cust_id from sh.customers;
Table created.
Elapsed: 00:00:00.11
SQL> select count (*) from mytest;
COUNT(*)
----------
55500
Then I populate all keys from that table into an immemory btree like this (pl/sql functions targeting c functions)
set timing on
set serveroutput on
declare
CURSOR cGetCust IS
SELECT CUST_ID from MyTest;
NbAdd number := 0;
Err Number := 0;
lCust_Id varchar2(20);
begin
Err := InitBtree;
NbAdd := 0;
OPEN cGetCust;
LOOP
FETCH cGetCust INTO lCust_Id;
EXIT WHEN cGetCust%NOTFOUND;
NbAdd := NbAdd + 1;
Err := AddANewKey ('MYTEST;'||lCust_Id,lCust_Id);
END LOOP;
CLOSE cGetCust;
dbms_output.put_line( 'NbAdd Ids='||NbAdd);
end;
/
NbAdd Ids=55500
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.02 to long! to add 55500 keys!
Now I am having the stuff in memory, let's try to access it...
SQL> select sum (Val) from (select GetKeyExist ('MYTEST;'||cust_id) val from MyTest);
SUM(VAL)
----------
55500
Elapsed: 00:00:07.70
Why is it so slow??
Thanks for your input.
Cheers
jko