RE: SQL UDF

Wes,

If you want to cut down the time it takes, you need to optimize your
SQL.

Specifically:
SELECT DRDL01
INTO x_Des
FROM F0005
WHERE DRSY = x_SY
and DRRT= x_RT
and LTRIM(DRKY) = ltrim(rtrim(x_KY))


The 'and LTRIM(DRKY) = ltrim(rtrim(x_KY))' line  is going to cause
problems.  I would bet that you look at the query optimizer messages,
you'll find out that an index is not being used in your query.

If the line was 'and DRKY = ltrim(rtrim(x_KY))' then you'd be ok.  But
the LTRIM on the table field usually forces the query optimizer to do a
full table scan.


HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wes
Sent: Friday, October 21, 2005 12:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL UDF

Hi Wilt,

I understand and agree with your point. I'm using the function to build
a
data extract table for a data warehouse project. When we are using that
function, the table doesn't change. The table has about 67000 rows but
it
central for all our user defined values. During the data extract the
table
IO for that table is around 5million and it adds about 2hrs processing
time
so you can see there are a lot of mutiple reads going on for the same
criteria. I was hoping to reduce the IO to around 2x the table size and
figured deterministic would do the trick.

"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC014C411F@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Wes,
>
> As I understand it, DETERMINISTIC simply tells the optimizer that it
> only has to run the function once for a given set of results.  It in
> know way guarantees that it will only run it once.
>
> In any event, is this function really deterministic?  What happens
when
> a record is added to the table?
>
> My philosophy is that every function that access a table is NOT
> DETERMINISTIC.  Only functions that don't access a table, for example
a
> convert date function, can be DETERMINISTIC.
>
> Perhaps the optimizer feels the same?
>
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
> -----Original Message-----
> From:
midrange-l-bounces+cwilt=meaa.mea.com-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxx
e.or
g
>
[mailto:midrange-l-bounces+cwilt=meaa.mea.com-Zwy7GipZuJhWk0Htik3J/w@pub
lic.
gmane.org] On Behalf Of
> Wes
> Sent: Wednesday, October 19, 2005 4:02 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL UDF
>
> Hi List,
>
> I have a function that will always return the same result if the input
> criteria is the same. After running it and noticing the IO for that
file
> was
> too high, I changed the function to deterministic, hoping it would
cache
> the
> result after the first call. That doesn't seem to be the case...
>
> Here is the function:
>
> CREATE FUNCTION DataWH/CatCodes
> (x_SY varCHAR(4),
> x_RT varCHAR(2),
> x_KY varCHAR(10))
> RETURNS varCHAR(40)
> LANGUAGE SQL
> DETERMINISTIC
> READS SQL DATA
> Begin
>
> DECLARE x_Des varCHAR(50);
> DECLARE at_end INT DEFAULT 0;
> DECLARE not_found
> CONDITION FOR '02000';
>
> DECLARE CONTINUE HANDLER FOR not_found
> SET at_end = 1;
>
> SELECT DRDL01
> INTO x_Des
> FROM F0005
> WHERE DRSY = x_SY
> and DRRT= x_RT
> and LTRIM(DRKY) = ltrim(rtrim(x_KY));
>
>
> IF at_end <> 0 THEN
> return 'N/A';
> else
> return ltrim(rtrim(x_KY))||' - '||ltrim(rtrim(x_Des));
> END IF;
> END
>
> Is there a reason why the table IO still too high?
>
> Thanks, Wes
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>