SEO

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-informix-list@iiug.org
[mailtowner-informix-list@iiug.org]On Behalf Of
paul_geoffrey_brown@yahoo.com
Sent: Monday, March 01, 2004 5:06 PM
To: informix-list@iiug.org
Subject: Re: UDR Performance Question


"Savio Pinto (s)" <spinto@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