UDR Performance Question

Sean Baker

unread,

Mar 1, 2004, 11:01:38 AM3/1/04

to

This is a multi-part message in MIME format.

------_=_NextPart_001_01C3FFA6.9A9C42E7
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello All,

We've been using SPL routines with some very good success the past year
(IDS version 9.3, soon to be 9.4, running on AIX 4.3, soon to be 5L).
We are also going to start using Java UDR's as well, and possibly some
UDR's written in C.

My question is, regarding straight performance of getting data out of
the IDS database and returning the results, which UDR platform has the
best performance (SPL, Java, C)? In my mind, I would think SPL would be
the fastest since it's basically internal to the Informix database. As
far as procedural logic performance (processing of flow control
statements), I can't imagine there is much difference between the 3. Is
there an obvious choice where performance is concerned? Can someone
point me to some documentation on this subject?

Thanks,

Sean.
- - - -
Sean Baker, Application Development
Money Mailer, LLC
14271 Corporate Drive
Garden Grove, CA 92843

714.265.8288 ph
714.265.8362 fax

------_=_NextPart_001_01C3FFA6.9A9C42E7
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.6944.0">
<TITLE>UDR Performance Question</TITLE>
</HEAD>
<BODY>

Hello All,

We've been using SPL routines with =
some very good success the past year (IDS version 9.3, soon to be 9.4, =
running on AIX 4.3, soon to be 5L).  We are also going to start =
using Java UDR's as well, and possibly some UDR's written in =
C.

My question is, regarding straight =
performance of getting data out of the IDS database and returning the =
results, which UDR platform has the best performance (SPL, Java, =
C)?  In my mind, I would think SPL would be the fastest since it's =
basically internal to the Informix database.  As far as procedural =
logic performance (processing of flow control statements), I can't =
imagine there is much difference between the 3.  Is there an =
obvious choice where performance is concerned?  Can someone point =
me to some documentation on this subject?

Thanks,

Sean.

- - - -

Sean Baker, Application =
Development

Money Mailer, LLC

14271 Corporate Drive

Garden Grove, CA 92843

714.265.8288 ph

714.265.8362 fax

</BODY>
</HTML>
------_=_NextPart_001_01C3FFA6.9A9C42E7--
sending to informix-list

Obnoxio The Clown

unread,

Mar 1, 2004, 12:41:30 PM3/1/04

to

Sean Baker wrote:

> This is a multi-part message in MIME format.
>
> ------_=_NextPart_001_01C3FFA6.9A9C42E7
> Content-Type: text/plain;
> charset="US-ASCII"
> Content-Transfer-Encoding: quoted-printable

Don't post frickin HTML :o(

> We've been using SPL routines with some very good success the past year
> (IDS version 9.3, soon to be 9.4, running on AIX 4.3, soon to be 5L).
> We are also going to start using Java UDR's as well, and possibly some
> UDR's written in C.
>
> My question is, regarding straight performance of getting data out of
> the IDS database and returning the results, which UDR platform has the
> best performance (SPL, Java, C)? In my mind, I would think SPL would be
> the fastest since it's basically internal to the Informix database. As
> far as procedural logic performance (processing of flow control
> statements), I can't imagine there is much difference between the 3. Is
> there an obvious choice where performance is concerned? Can someone
> point me to some documentation on this subject?

For database access and simple routines, SPL is probably fastest. For
complex or iterative routines, C is faster. Java just sucks lemons. :o)

> Thanks,
> 
>
> Sean.
>
> - - - -
>
> Sean Baker, Application =
> Development
>
> Money Mailer, LLC
>
> 14271 Corporate Drive
>
> Garden Grove, CA 92843
> 
>
> 714.265.8288 ph
>
> 714.265.8362 fax
> 
>
> </BODY>
> </HTML>
> ------_=_NextPart_001_01C3FFA6.9A9C42E7--
> sending to informix-list

--
"C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
- Coluche

Savio Pinto (s)

unread,

Mar 1, 2004, 1:44:00 PM3/1/04

to

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C3FFBD.2998B4A2
Content-Type: text/plain;
charset="iso-8859-1"

we had lot of performance issues with UDR's (it was UDR's written in C"),
the main problem was that it is very difficult to get fragment elimination
using UDR, we had to write duplicate functions with the fragment expression
hardcoded in the the UDR just to get fragment elimination and parallism.
also, UDA's are very slow, no matter what you do, they run as a single
threaded process.

-----Original Message-----
From: owner-inf...@iiug.org [mailto:owner-inf...@iiug.org]On
Behalf Of Sean Baker
Sent: Monday, March 01, 2004 10:02 AM
To: inform...@iiug.org
Subject: UDR Performance Question

Hello All,

We've been using SPL routines with some very good success the past year (IDS
version 9.3, soon to be 9.4, running on AIX 4.3, soon to be 5L). We are
also going to start using Java UDR's as well, and possibly some UDR's
written in C.

My question is, regarding straight performance of getting data out of the
IDS database and returning the results, which UDR platform has the best
performance (SPL, Java, C)? In my mind, I would think SPL would be the
fastest since it's basically internal to the Informix database. As far as
procedural logic performance (processing of flow control statements), I
can't imagine there is much difference between the 3. Is there an obvious
choice where performance is concerned? Can someone point me to some
documentation on this subject?

Thanks,

Sean.
- - - -
Sean Baker, Application Development
Money Mailer, LLC
14271 Corporate Drive
Garden Grove, CA 92843

714.265.8288 ph
714.265.8362 fax

------_=_NextPart_001_01C3FFBD.2998B4A2
Content-Type: text/html;
charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<TITLE>UDR Performance Question</TITLE>

<META content="MSHTML 5.50.4731.2200" name=GENERATOR></HEAD>
<BODY>
<DIV>we had
lot of performance issues with UDR's (it was UDR's written in C"), the main
problem was that it is very difficult to get fragment elimination using UDR, we
had to write duplicate functions with the fragment expression hardcoded in
the the UDR just to get fragment elimination and parallism. also, UDA's are very
slow, no matter what you do, they run as a single threaded
process.</DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: owner-inf...@iiug.org
[mailto:owner-inf...@iiug.org]On Behalf Of Sean
Baker Sent: Monday, March 01, 2004 10:02 AM To:
inform...@iiug.org Subject: UDR Performance
Question </DIV>
Hello All, 
We've been using SPL routines with some very good

success the past year (IDS version 9.3, soon to be 9.4, running on AIX 4.3,

soon to be 5L).  We are also going to start using Java UDR's as well, and
possibly some UDR's written in C.
My question is, regarding straight performance of

getting data out of the IDS database and returning the results, which UDR

platform has the best performance (SPL, Java, C)?  In my mind, I would

think SPL would be the fastest since it's basically internal to the Informix

database.  As far as procedural logic performance (processing of flow

control statements), I can't imagine there is much difference between the

3.  Is there an obvious choice where performance is concerned?  Can
someone point me to some documentation on this subject?
Thanks, 
Sean. - - -
- Sean Baker, Application
Development Money Mailer, LLC
 14271 Corporate Drive Garden Grove, CA 92843 
714.265.8288 ph 714.265.8362 fax </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C3FFBD.2998B4A2--
sending to informix-list

Paul G. Brown

unread,

Mar 1, 2004, 6:05:40 PM3/1/04

to

"Savio Pinto (s)" <spi...@cap.org> wrote in message news:<c201su$2d7$1...@terabinaries.xmission.com>...

> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
>
> ------_=_NextPart_001_01C3FFBD.2998B4A2
> Content-Type: text/plain;
> charset="iso-8859-1"
>
> we had lot of performance issues with UDR's (it was UDR's written in C"),
> the main problem was that it is very difficult to get fragment elimination
> using UDR, we had to write duplicate functions with the fragment expression
> hardcoded in the the UDR just to get fragment elimination and parallism.
> also, UDA's are very slow, no matter what you do, they run as a single
> threaded process.

What kind of bullshit is this?

a) What on earth does a UDF have to do with fragment elimination? If
you're putting SQL into the UDF, then that's the design problem.
UDFs are best thought of a procedural sub-routine, not as a
stored-procedure. (If your UDF touched BLOBs, then you might have a
point).

b) The UDAs were designed *specifically* to work with partitioned
tables (to exploit parallism). Did you define all of the UDA functions?
(INIT, ITER, MERGE, FINAL) And did you set them all up correctly?

Read Jacques Roy's excellent books on the subject. There are customers
using UDAs that require the parrallelism to get where they need to
go.

Please post details.

Paul G. Brown

unread,

Mar 1, 2004, 6:21:33 PM3/1/04

to

"Sean Baker" <SBa...@moneymailer.com> wrote in message news:<c1vnbf$uts$1...@terabinaries.xmission.com>...

> This is a multi-part message in MIME format.

Aaaaargh!

> We are also going to start using Java UDR's as well, and possibly some
> UDR's written in C.
>
> My question is, regarding straight performance of getting data out of
> the IDS database and returning the results, which UDR platform has the
> best performance (SPL, Java, C)?

I've posted on this before.

In a nutshell: there are three costs to consider:

a) Cost of whatever SQL queries the UDR contains.

b) Computational cost of the UDR (without queries).

c) Calling overhead.

1. If the UDR contains SQL, all options are equally bad. In general,
try to avoid 'black-boxing' SQL within a UDR. The optimizer can't
"see" what's going on in there. UDFs are really useful for
encapsulating some kind of repeated logic. If your UDR contains
SQL, just use SPL. It's simple.

2. 'C' is better than Java is better than SPL. 'C' compiles down to
the metal, Java has a better run-time interpreter than SPL.

3. Calling overhead is the amount of work the server has to do to set
up to call a UDF implemented in one of these three languages. For
example, before it calls a 'C' UDF, the server allocates memory
slabs, copies data, checks to see that the UDF's shared library is
loaded, etc. C and SPL incur about the same per-call costs. Calling
a Java function is *very* expensive. Before you call a Java method
you've got to whack the JVM hard, repeatedly, and then take what it
says and translate it back into the engine's runtime.

So: If it's got SQL in it, use SPL, and try to avoid using said routine
in SQL queries, if it's at all possible (sometimes, it isn't).

If the UDF logic is small and relatively simple, use SPL. (Developing
'C' is a pain not only in the you-know-what, but a pain spread
liberally throughout your entire body).

If it's beastly nasty complex ugly needs to be really fast and
you use it a lot, use 'C'.

Why Java at all? Because there are things you can do with Java that you
can't do (easily) with C or SPL, like call things outside the engine using
the java facilities.

Hope this yelps!

KR

Pb

Paul G. Brown

unread,

Mar 1, 2004, 6:21:33 PM3/1/04

to

"Sean Baker" <SBa...@moneymailer.com> wrote in message news:<c1vnbf$uts$1...@terabinaries.xmission.com>...

> This is a multi-part message in MIME format.

Aaaaargh!

> We are also going to start using Java UDR's as well, and possibly some
> UDR's written in C.
>
> My question is, regarding straight performance of getting data out of
> the IDS database and returning the results, which UDR platform has the
> best performance (SPL, Java, C)?

I've posted on this before.

Obnoxio The Clown

unread,

Mar 1, 2004, 7:39:54 PM3/1/04

to

Paul G. Brown wrote:

> What kind of bullshit is this?

It's OK, Paul, it's just a user. :o)

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

Savio Pinto (s)

unread,

Mar 2, 2004, 10:20:28 AM3/2/04

to

some of the UDR's are in SBLOBS and some of them in regular dbspace, the
problem was that when we were joining 2 more tables (tables having UDT)in a
query, with table size of about 300+ million rows, the query on them with
the UDR (batch query) used to take forever, so we had to break the query
that had the UDR's into small pieces based on the fragmentation expression
defined on the tables, this way we could run multiple occurrences of the
same query(that had the call to the UDR's) in parallel with different
fragmentation expression (and be sure that it doesn't do a full sequential
scan on the table), doing this the query that had an UDR used to fly for a
300+ million row tables. this is what i was trying to say but i think my
email was not clear.

for UDA's the statements (INIT, ITER, MERGE, FINAL) what you mentioned are
used, what do you mean by partitioned tables?

sure, Jacques Roy's book is excellent.

-----Original Message-----
From: owner-inf...@iiug.org
[mailto:owner-inf...@iiug.org]On Behalf Of

paul_geof...@yahoo.com
Sent: Monday, March 01, 2004 5:06 PM
To: inform...@iiug.org
Subject: Re: UDR Performance Question

"Savio Pinto (s)" <spi...@cap.org> wrote in message
news:<c201su$2d7$1...@terabinaries.xmission.com>...
> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
>
> ------_=_NextPart_001_01C3FFBD.2998B4A2
> Content-Type: text/plain;
> charset="iso-8859-1"
>
> we had lot of performance issues with UDR's (it was UDR's written in C"),
> the main problem was that it is very difficult to get fragment elimination
> using UDR, we had to write duplicate functions with the fragment
expression
> hardcoded in the the UDR just to get fragment elimination and parallism.
> also, UDA's are very slow, no matter what you do, they run as a single
> threaded process.

What kind of bullshit is this?

a) What on earth does a UDF have to do with fragment elimination? If

you're putting SQL into the UDF, then that's the design problem.
UDFs are best thought of a procedural sub-routine, not as a
stored-procedure. (If your UDF touched BLOBs, then you might have a
point).

b) The UDAs were designed *specifically* to work with partitioned
tables (to exploit parallism). Did you define all of the UDA
functions?
(INIT, ITER, MERGE, FINAL) And did you set them all up correctly?

Read Jacques Roy's excellent books on the subject. There are customers
using UDAs that require the parrallelism to get where they need to
go.

Please post details.
sending to informix-list

Paul G. Brown

unread,

Mar 2, 2004, 11:17:27 AM3/2/04

to

Obnoxio The Clown <obn...@hotmail.com> wrote in message news:<c20ll3$1ngndb$5...@ID-64669.news.uni-berlin.de>...

> Paul G. Brown wrote:
>
> > What kind of bullshit is this?
>
> It's OK, Paul, it's just a user. :o)

But I like users.

It occurs that the poster might be a troll, however. Darn.

Sean Baker

unread,

Mar 2, 2004, 1:59:42 PM3/2/04

to

No troll here ...

Sorry about the original HTML post, an honest mistake!

We are indeed 'black-boxing' some processing in UDR's, all of which
contain SQL. The advantage we get here is that the operations are
available to any app that has access to the IDS server (4GL, Visual
FoxPro, ASP, VB, Java, etc.). That way we don't have any duplication of
business rule logic all over the place. It's worked pretty well for us.

Since our requirements for having SQL in the UDR's isn't going to
change, it sounds like we will not get much of a performance boost from
using C or Java. In fact, Java may be worse due to the JVM whacking.

Thanks for the info. Can you point me to your original post on this
topic?

Sean.

> -----Original Message-----
> From: owner-inf...@iiug.org
> [mailto:owner-inf...@iiug.org] On Behalf Of Paul G. Brown
> Sent: Monday, March 01, 2004 3:22 PM
> To: inform...@iiug.org
> Subject: Re: UDR Performance Question
>
>

> "Sean Baker" <SBa...@moneymailer.com> wrote in message

sending to informix-list