Home » SQL & PL/SQL » SQL & PL/SQL » Function need tuning
Function need tuning [message #248039] Wed, 27 June 2007 17:58 Go to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Hi Experts,

I want to change the logic for following function.
the reason is this function is taking too long to run.

Please advice any changes.
Database 10g.


create or replace function
num_Business_Days(start_date in date, end_date in date)
return number is
currdate date := start_date; /* holds the next date */
theDay varchar2(10);/* day of the week for currdate */
countBusiness number := 0; /* counter for business days */
begin
/* start date must be earlier than end date */
if end_date - start_date <= 0 then
return (0);
end if;
loop
/* go to the next day */
currdate := to_date(currdate) + 1;
/* finished if end_date is reached */
exit when currdate = end_date;
/* what day of the week is it? */
select to_char(currdate,'Dy') into theDay from dual;
/* count it only if it is a weekday */
if theDay <> 'Sat' and theday <> 'Sun' then
countBusiness := countBusiness + 1;
end if;
end loop;
return (countBusiness);
end;


Thanks.
Re: Function need tuning [message #248042 is a reply to message #248039] Wed, 27 June 2007 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
please enable SQL_TRACE & run results thru TKPROF to include EXPLAIN_PLAN.
use CODE TAGS when you CUT & PASTE results back here.
Re: Function need tuning [message #248044 is a reply to message #248042] Wed, 27 June 2007 19:02 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Thanks for your reply.

basically i am using this function in following query and response is too slow because of large date.

sELECT
count(*)
FROM

ifcas.CA_EMPLOYEES EMPL,
ifcas.CA_OFFICES OFFI,
CA_REGIONS REGI,
ifcas.ca_empl_billing_rate_history,
ca_departments
WHERE
EMPL.EMPL_OFFI_id = OFFI.OFFI_id
AND OFFI.OFFI_REGI_ID = REGI.REGI_ID
and nvl(nvl(empl.empl_hours, offi_hours), 0) * ifcas.num_business_days(to_date(greatest('01-jan-2007',brh_start_date))-1,'02-jun-2007') != 0

and brh_empl_id = EMPL.EMPL_id
and brh_end_date = '01-JAN-2999'
and EMPL.EMPL_OFFI_id = dept_OFFI_id
and EMPL.EMPL_dept_id = dept_id;



for execution plan for this query please find an attchment.



Thanks.
Re: Function need tuning [message #248045 is a reply to message #248039] Wed, 27 June 2007 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
please read & FOLLOW all the specified items in the #1 STICKY post at the top of this forum.

Since you chose to not answer my questions/requests for information, I choose to not answer yours.

You're On Your Own (YOYO)!
Re: Function need tuning [message #248085 is a reply to message #248044] Thu, 28 June 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition: a search on "business day" will give you how to do it.

In the end, do you think your title is useful to help someone search if a thread fit his question?
Why something with "business days" is not in it? Isn't it the purpose of your question?

Regards
Michel

[Updated on: Thu, 28 June 2007 01:00]

Report message to a moderator

Re: Function need tuning [message #248097 is a reply to message #248039] Thu, 28 June 2007 01:15 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you need to use SQL when a simple assignment is good enough?
Try :
theDay := to_char(currdate,'Dy');

Instead of:
select to_char(currdate,'Dy') into theDay from dual;


It will eliminate a scores of context switches.

Michael
Re: Function need tuning [message #248261 is a reply to message #248044] Thu, 28 June 2007 08:19 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
jamalfarooq wrote on Wed, 27 June 2007 20:02
ifcas.num_business_days(to_date(greatest('01-jan-2007',brh_start_date))-1,'02-jun-2007') != 0

and brh_empl_id = EMPL.EMPL_id
and brh_end_date = '01-JAN-2999'



[sigh]
Poor poor coding here. What is the greatest of a DATE and a character string? You got me. That's like asking which is heavier, a pound of bricks or a dream.
You must use the TO_DATE function.
Re: Function need tuning [message #248321 is a reply to message #248261] Thu, 28 June 2007 11:24 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
Thanks for your reply.

Here i need to take only greatest date and string is table column having date data type.
"ca_empl_billing_rate_history.brh_start_date"

ifcas.num_business_days(to_date(greatest('01-jan-2007',brh_start_date))-1,'02-jun-2007') != 0

bold date is in parameter.

if you required more info please let me know.


thanks in advance
Re: Function need tuning [message #248326 is a reply to message #248097] Thu, 28 June 2007 11:41 Go to previous messageGo to next message
jamalfarooq
Messages: 35
Registered: August 2006
Member
thanks Micheal,

I tried that also but does'nt make any difference Embarassed
Re: Function need tuning [message #248337 is a reply to message #248321] Thu, 28 June 2007 12:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
jamalfarooq wrote on Thu, 28 June 2007 12:24

Here i need to take only greatest date and string is table column having date data type.
"ca_empl_billing_rate_history.brh_start_date"

ifcas.num_business_days(to_date(greatest('01-jan-2007',brh_start_date))-1,'02-jun-2007') != 0



Well, if it's a DATE why do you continue to insist on using poor and ignorant programming methods????

FOO SCOTT>create table dummy (brh_start_date date);

Table created.

FOO SCOTT>insert into dummy values (sysdate);

1 row created.

FOO SCOTT>select greatest('01-jan-2011',brh_start_date) from dummy;

GREATEST('01-JAN-20
-------------------
06/28/2007 13:23:16


Compare a DATE to a character string and you're asking for trouble. I said it once already and maybe you missed it - you MUST use the TO_DATE function

[Updated on: Thu, 28 June 2007 12:31]

Report message to a moderator

Re: Function need tuning [message #248362 is a reply to message #248039] Thu, 28 June 2007 14:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why are you so sure that the function is causing performance problems?
It may be the SQL statement itself.

Post TKPROF.

Michael
Re: Function need tuning [message #248419 is a reply to message #248044] Fri, 29 June 2007 00:23 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
CREATE OR REPLACE FUNCTION Num_Business_Days(
DStartDate IN DATE,
DEndDate IN DATE
) RETURN NUMBER IS
PreviousSunday DATE; -- Last Sunday --
NextSaturDay DATE; -- Next Saturday --
DaySubtracted NUMBER; -- Days subtracted to get last Sunday --
DayAdded NUMBER; -- Days added to get next Saturday --
CountDays NUMBER := 0 ;
IsSunday NUMBER := 0 ;
IsSaturday NUMBER := 0 ;
BEGIN
IF DStartDate > DEndDate THEN
Return 0 ;
ELSE
SELECT DECODE(TO_CHAR(DStartDate, 'D')
,1 , 1
, 0
),
DECODE(TO_CHAR(DEndDate, 'D')
,7 , 1
, 0
),
DECODE(TO_CHAR(DStartDate, 'D')
,1 , 0
,2 , -1
,3 , -2
,4 , -3
,5 , -4
,6 , -5
,7 , -6
),
DECODE(TO_CHAR(DStartDate, 'D')
,1 , 0
,2 , -1
,3 , -2
,4 , -3
,5 , -4
,6 , -5
,7 , -6
) + DStartDate,
DECODE(TO_CHAR(DEndDate, 'D')
,1 , 6
,2 , 5
,3 , 4
,4 , 3
,5 , 2
,6 , 1
,7 , 0
),
DECODE(TO_CHAR(DEndDate, 'D')
,1 , 6
,2 , 5
,3 , 4
,4 , 3
,5 , 2
,6 , 1
,7 , 0
) + DEndDate
INTO IsSunday, IsSaturday, DaySubtracted, PreviousSunday, DayAdded, NextSaturDay
FROM DUAL ;

CountDays := NextSaturDay - PreviousSunday + 1 ;
CountDays := CountDays - ((CountDays/7)-1)*2 ;
CountDays := CountDays + DaySubtracted - DayAdded - IsSunday -IsSaturday ;

RETURN CountDays ;
END IF ;
END;

-- Try the above function definition --
Regards...Akhil
Re: Function need tuning [message #248420 is a reply to message #248419] Fri, 29 June 2007 00:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ouch
Re: Function need tuning [message #248421 is a reply to message #248419] Fri, 29 June 2007 00:30 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
One more thing is make the function "Num_Business_Days" as DETERMINISTIC. If you make the function DETERMINISTIC Oracle will never execute the function next time for same actual parameter. It will fetch previous execution value.

Regards...Akhil
Re: Function need tuning [message #248424 is a reply to message #248420] Fri, 29 June 2007 00:42 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Is a very simple logic. Perhaps this function definition would give performance benefit.
Re: Function need tuning [message #248815 is a reply to message #248419] Mon, 02 July 2007 02:36 Go to previous message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
CREATE OR REPLACE FUNCTION Num_Business_Days(
DStartDate IN DATE,
DEndDate IN DATE
) RETURN NUMBER IS
PreviousSunday DATE; -- Last Sunday --
NextSaturDay DATE; -- Next Saturday --
DaySubtracted NUMBER; -- Days subtracted to get last Sunday --
DayAdded NUMBER; -- Days added to get next Saturday --
CountDays NUMBER := 0 ;
IsSunday NUMBER := 0 ;
IsSaturday NUMBER := 0 ;
BEGIN
IF DStartDate > DEndDate THEN
Return 0 ;
ELSE
IsSunday := (CASE WHEN TO_CHAR(DStartDate, 'D') = 1 THEN 1 ELSE 0 END) ;
IsSaturday := (CASE WHEN TO_CHAR(DEndDate, 'D') = 7 THEN 1 ELSE 0 END) ;
DaySubtracted := ((-1)*TO_CHAR(DStartDate, 'D')) + 1 ;
PreviousSunday:= DaySubtracted + DStartDate ;
DayAdded := ((-1)*TO_CHAR(DEndDate, 'D')) + 7 ;
NextSaturDay := DayAdded + DEndDate ;
CountDays := NextSaturDay - PreviousSunday + 1 ;
CountDays := CountDays - ((CountDays/7)-1)*2 ;
CountDays := CountDays + DaySubtracted - DayAdded - IsSunday -IsSaturday ;
RETURN CountDays ;
END IF ;
END;

Previous Topic: Split string problem
Next Topic: Performance issue OUT parameter
Goto Forum:
  


Current Time: Sat Dec 03 18:01:51 CST 2016

Total time taken to generate the page: 0.08648 seconds