> -----Original Message-----
> From: Ann Harrison [mailto:harrison@...]
> Sent: Martes 16 de Mayo de 2000 17:22
>
> At 01:50 AM 5/16/00 -0400, Claudio Valderrama C. wrote:
>
> >If Java is going to be bundled into the engine
>
> >When I need to call a Java UDF on a 3 million records update, what's
> >the expected performance loss I should expect compared with C? 3%, 5%,
> >10%?
>
> First, UDF's are not generally used for data update - given that they
> lack client context.

I wanted to say that an UDF is being used in the context of an update
statement, as a function, not to perform the whole update, for example:
update mytable
set field = udf1(field2, field3)
where blah, blah, blah
If the "where" filters 3 million records, then I apply udf1 3 million
times. It has to have a difference between a C and a Java UDF in this
example. I wondered if the loss of performance was only marginally.

> A Java UDF would have that context. There is
> no simple answer to relative performance - Java implementations are
> likely to get faster.

Hope this is the trend and it's not only due to CPUs going faster. I didn't
intend to ask for an UDF with full context.


[portability]

> Ah, but as is well known, compiled C doesn't travel. Compiled Java does.

Compiled C/C++ no, you're right. Even so, C++ plus CORBA is an interesting
contender.

> But there are
> >some features that would make life easier:
> >- BREAK and CASE.
>
> CASE, no question. BREAK ... well there are alternatives (label & leave)
> that are more elegant.

It seems we clashed only in the name (I hope). If LEAVE means "going to the
next instruction past the immediate surrounding loop", then I'm happy. As a
disgression, an IBM language known as REXX has a variable name as an option
to the LEAVE instruction, so you can "leave" not only the most internal loop
in the case of nested loops. About LABEL, are you writing about the typical
BASIC programming? If this is more elegant, God save us! Perhaps I
misunderstood your point, but you didn't provide enough information to
guess.

> >- The ability to have a R/O context variable with the name of
> the table that
> >invoked a trigger if some trigger is being called. Of course, if this
> >trigger modifies another table and this table fires its own trigger, the
> >variable has to be updated until the second trigger returns... perhaps we
> >are speaking about a stack.
>
> That one needs more thought. I suspect it's the tip of an iceberg that
> has multi-table triggers under the surface.

This wasn't my thought but I suspect more than one person goes in the
direction of multi-table triggers. If I'm not wrong, Phil Shrimpton wrote
about calling a generic procedure from a trigger that, given the table's
name, allows him to traverse rdb$relation_fields and test all fields in a
loop. This won't work unless either the form
table.fieldname is allowed with fieldname being obtained from
rdb$relation_fields as an string (and this implies a FOR loop to get the
field names)
or
new.fieldname/old.fieldname syntax is enhanced under the same conditions
above and new & old are allowed to be passed "by reference" to a stored
procedure.
There's a catch here: we already have enough nightmares with in-place/real
time metadata alterations and database corruption as a final product. If in
the middle of a loop through the fields another thread alters the table, we
are isolated from changes by the transactions but... we know that there are
problems, so introducing new problems before solving the ones known doesn't
sound wise.
To summarize: I wanted to have a variable so I could use it to pass to a
procedure as another string variable. I'm always thinking in ways to enhance
logging capabilities. Actually, being able to concentrate audit logic in a
sproc is interesting. Having the table's name is an advancement. The field
themselves can come as a concatenation in the worst case.
I can guess some power on multi-table triggers but I can't realize the
metadata nightmare they can bring.

> >- The ability to have a R/O variable that gives me the number of records
> >affected by my last update/delete statement. AFAIK, Oracle, SqlServer,
> >Sybase and DB2 provide such facility. In IB this is only available in a
> >client application (and probably in embedded SQL, not sure).
>
> What do you get back if you update or delete from a view that's governed
> by update/delete triggers?

I will answer tangentially: I want to get whatever results I get from the
engine in DSQL through the API when I do the same operation from a client
application in the same scenario. To state in other words: if the client can
know the result of an operation regardless of the total accuracy (or lack of
in the case of views) without the need for committing, why I cannot have the
same number available in the procedure/trigger doing such change? I'm not
asking for reentrancy or esoterical things (although I suspect reentrancy is
always a spider waiting for a naive fly) but the modifying last command's
results are replaced by a new number when my procedure executes a new
modifying command. As I said, the same number reported by TQuery/TIB_Query
as RowsAffected.

> >- Some long needed functions, like substr, length and trim.

I assume you didn't comment because these functions would be embedded in the
server, so no need to fight for the preferred language.

> >- Does anybody knows exactly what's all the power of Interbase?
> Probably not, but we're working to gather all the bits together.

Maybe you'll discover that substr is already buried in the engine under the
name __substr or something alike, keep us informed!

> >- Does Jim know all the changes and enhancements made to the
> server while he
> >was "retired"?
> No, but he's a quick study.

I would be surprised with a different answer... the father almost always
retains some fast way to get in touch with his son.

> >- Does anybody care to document all the hidden but legal tricks that are
> >available?
> Care? Sure. And maybe when the code is open, it will happen.

As long as it's not 40% assembler, high level languages adherents will have
a chance to see and smell, too.

> > Personally I believe that (and excuse me for shouting) PERHAPS
> > THERE ARE
> >MUCH POWER THAT NEVER WAS DOCUMENTED.
> Worse, there are lots of things that were documented and no longer are.

Glup/gulp/other expressions.

> And there are lots of things that are partially implemented and never
> tested.

Maybe the CURSOR trick inside stored procedures being the most known?

> > how many options or system calls are in limbo
> >currently?
> About 70.

Given that you wrote this phrase at the end, about unknown features:

> Some are, some aren't. Some of the ones that used to work may have been
> broken in later improvements.
>
> Ann

... then I wonder if these 70 system calls have sense or some of them are
like a door to the street on the 25th floor of a building: better you don't
open (call) it.
SET FLAME ON
This fact only confirms that IB is not only the best kept secret of Borland
to the rest of the world but it's the best hidden secret even inside
Borland.
SET FLAME OFF

I've heard that if you take all the IB source code, combine it in a long
string, wipe out all punctuation and blanks, arrange the string in
80-characters rows and start reading from the last character up to the
first, you'll read Jim's testament along with a never seen prime class on
rdb$db_key and other intricacies.
:-)

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://members.tripod.com/cvalde