|
midrange.com
Matt,
I've not ever noticed a 10x slowdown when using UDFs.
Here's my version:
create function CvtNumISOtoDate(indte Numeric(8,0)) returns Date
language SQL
not fenced
deterministic no external action
returns null on null input
contains SQL
set option datfmt=*ISO
begin
declare INVALID_DATETIME condition for '22007';
declare exit handler for INVALID_DATETIME
begin
resignal sqlstate '01HD1'
set message_text = 'Invalid date/time replaced by NULL';
return NULL;
end;
return(
case indte
when 0 then NULL
when 99999999 then date('9999-12-31')
declare exit handler for INVALID_DATETIME
begin
resignal sqlstate '01HD1'
set message_text = 'Invalid date/time replaced by NULL';
return NULL;
end;
return(
case indte
when 0 then NULL
when 99999999 then date('9999-12-31')
else date(substr(digits(indte),1,4) concat '-' concat
substr(digits(indte),5,2) concat '-' concat
substr(digits(indte),7,2))
end
);
end;
A couple of quick test does show it to be slower, but not 10x slower.
See what you find.
I'd consider opening a PMR with IBM on this issue.
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 Tyler, Matt Sent: Thursday, June 29, 2006 11:04 AM To: Midrange Systems Technical Discussion Subject: RE: AW: SQL - Calculate the number of hours between 2 timestamps Just a word of warning about using SQL UDFs. We implemented Birgitta's example http://archive.midrange.com/rpg400-l/200606/msg00263.html and a query, we added it to, slowed by an order of about 10 times. Not Birgittas fault from her example but I believe it's the way the system implements the actual code behind SQL UDFS. Putting the raw code from her UDF into the statement (even though it made the statement longer) was such an improvement that I doubt we will use a SQL UDF ever again. We have not implemented RPGLE UDFs yet to see how they stack up. By the way, the UDF had to be called about 7 times with in this one statement and the total number of records estimated to be processed was around 40,000. The job was stacking up the IO on QSYS2/QSQPTABL at well over 10,000,00. Thanks, Matt -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx Sent: Thursday, June 29, 2006 6:22 AM To: Midrange Systems Technical Discussion Subject: Re: AW: SQL - Calculate the number of hours between 2 timestamps I suppose one could write their own bif. Although then it would be a UDF. http://faq.midrange.com/data/cache/185.html Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com vhamberg@xxxxxxxxxxx Sent by: midrange-l-bounces@xxxxxxxxxxxx 06/28/2006 06:40 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc Subject Re: AW: SQL - Calculate the number of hours between 2 timestamps BTW, timestampdiff is not an iSeries exclusive extension - it is part of several SQL implementations. Its behavior in other scenarios is probably similar, although there is the chance that one vendor does it better than another. Is it part of any of the SQL standard specifications? Vern -------------- Original message -------------- From: rob@xxxxxxxxxI agree, it sucks. However, when you think about what you would dowith acalculating the difference between two timestamps, doesn'tthe decimalanswer work "good enough"? The OP wanted to know if theduration wasgreater than 8 hours. We posted something like selectts1-ts2 from ...and got a number. And if that number was greater than 80000 then itwasgreater than 8 hours. If is not "good enough" and you have a business reason, then see ifyoucan find a standard, preferably ISO, that says otherwise. IBM won't change timestampdiff because someone is probably using it and iscountingon it's anomalies. But if you have a strong enough businesscase, andsubmit a DCR then perhaps they'll come out with timestampdiffiso.Don'tknow what the odds of them ever creating one that saystimestampdiffMS forMicroSoft. Legal reasons, animosity, and possible confusion with milliseconds might intervene. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com Loyd Goodbar Sent by: midrange-l-bounces@xxxxxxxxxxxx 06/27/2006 09:27 PM Please respond to Midrange Systems Technical Discussion To Midrange Systems Technical Discussion cc Subject Re: AW: SQL - Calculate the number of hours between 2 timestamps Yeah, I saw that too. Frankly, that sucks. Does anyone know if this just an iSeries DB2 thing, or does it also afflict the "other" DB2? I mean, ILE RPG and even SQL Server can calculate accurately durations between two date/time stamps. --lg On Jun 27, 2006, at 10:59 AM, rob@xxxxxxxxx wrote:Study that timestampdiff bif carefully. A lot of assumptions are made. Which may, or may not, matter for your situation. Straight from the book: The following assumptions may be used in estimating thedifference:there are 365 days in a year there are 30 days in a month there are 24 hours in a day there are 60 minutes in an hour there are 60 seconds in a minute These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) isrequested for adifference in timestamps for '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month so the assumption of 30days in amonth applies. Rob Berendt-- This is the Midrange Systems Technical Discussion(MIDRANGE-L) mailinglist 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 listTo 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. -- 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
