High Performance Server Side Programming - Which Language

[nicholas....@gmail.com]

Hi,

I have a static list of 100,000 values in an array, given a string I
generate a hashcode and return an item from this array. I could join to
another table but everything has to be done in a function.

e.g select myFunc(field1) from myTable;

Any ideas what Oracle approach (language) would be best for this? So
far I've tried done the following.

PL/SQL Table or Varray instantiated within a package (Natively compiled
and interpreted)
4 seconds to translate 160k records.

Java static constants across multiple classes (constant pool size limit
would be reached with a single class)
33 seconds to translate 160k records

and calling lower() instead of my function takes 1 second to pass 160k
records, if i put lower in my package it also takes 3-4 seconds.

So any ideas, e.g. if I write the function in C and link it, would that
work well.

Thanks in advance.

[Sybrand Bakker]

On 2 Jun 2005 12:06:27 -0700, nicholas....@gmail.com wrote:

>I have a static list of 100,000 values in an array, given a string I
>generate a hashcode and return an item from this array. I could join to
>another table but everything has to be done in a function.

Why? issuing select in an 'embedded' function (also an implicit join)
usually precludes efficient execution paths and necessarily results in
abysmal performance. Also your C idea will result in an *external*
function, with additional overhead (communicating with the listener)

--
Sybrand Bakker, Senior Oracle DBA

[nicholas....@gmail.com]

To clarify:

There isn't a select going on in the embedded function, the function is
completely self contained. The query plan's so far with the PL/SQL
option match the same query using lower() or some type of encyption()
function

[DA Morgan]

nicholas....@gmail.com wrote:
> Hi,
>
> I have a static list of 100,000 values in an array, given a string I
> generate a hashcode and return an item from this array. I could join to
> another table but everything has to be done in a function.
>
> e.g select myFunc(field1) from myTable;

I've read the thread and now returned to your original post. I must
confess to being confused. Waht does the array with 100,000 values and
the hash code have to do with SELECT myFunct? It is far from clear.

And why 100,000 records? What "needs" 100,000 records at one time and
why?

I'd be heading straight for a syntax like this:
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

-- stuff here

EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;

And set p_array_size to 100-500.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[hpuxrac]

Nicholas, any time that you invoke a plsql function in a sql select
there is a big performance hit. The basic rules of thumb are don't
invoke a function in a select if you can avoid it, if you do have to
invoke a function try very hard to use the oracle provided "extentions
to sql" functions rather than a sql function. If you have to use a
function outside of "sql extended" then go for plsql rather than java
if you can ... etc.

Instead of containing things in an array perhaps you really want to use
an oracle hash table where you can use oracle to "randomize" or hash to
a block based on how you define the hash. Performance in that case
will be much better.

Try checking in the asktom.oracle.com site and do some searches using
hash and select you should fine some helpful information.

[nicholas....@gmail.com]

I certainly would like to divulge the reason for doing this but I would
probably break some kind of NDA. But loading the array/varray/table and
accessing it is not a real concern for me what I was most interested in
was an opinion on choice of languages, if there was a valid and better
alternative to PLSQL. So you can fully understand how I'm doing it in
PL/SQL here is the basic package structure. Joining to the seed table
is an option for read only operations but as it doesn't join on the PK
updates are not an option. So a function is used, it also makes life
easier for the dba and developers later on. So I think the real
question is, Is it possible to add to the list of SQL extensions in a
way the would result in better performance than the PL/SQL below.

-- pseudo-code
package X

TYPE itemStore IS TABLE OF myTable.field1%TYPE INDEX BY
BINARY_INTEGER;
items itemStore;

-- fetch value from items based on hash of value
function myFund(arg0 IN myTable.field1%TYPE) return
myTable.field1%TYPE
as
return items(mod(dbms_utility.hash(arg0,0,1000000),100000)+1);
end;

-- does this on initial call only
begin
select field1 BULK COLLECT into items from seedTable;
end;

Thanks

[Mladen Gogala]

On Thu, 02 Jun 2005 12:06:27 -0700, nicholas.wakefield wrote:

> So any ideas, e.g. if I write the function in C and link it, would that
> work well.

It depends on how you write it. If it is well written, it would work well.
If it isn't, it wouldn't.

--
Demagogue: One who preaches a doctrine he knows to be untrue to
men he knows to be idiots.
H.L. Mencken

[Connor McDonald]

9.2 and above, your strings *can* be the index into a plsql table if
that helps.

hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_...@yahoo.com

"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------