Home » SQL & PL/SQL » SQL & PL/SQL » user created function taking more time
user created function taking more time [message #256736] Mon, 06 August 2007 08:26 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi All,

I have created function to work same as "sysdate" like ----

create or replace function getcurrentdate
return date
as
outdate date;
begin
select sysdate into outdate from dual;
return(outdate);
end;
/



(As we have to generate report for both oracle and sybase using the same code and in sybase there is no sysdate function)


But in oracle ,when i am using sysdate in a query its taking 1.6 secs and with this function its taking 6.3 sec.

Can anyone please suggest what's wrong in this function.

Thanks & Regards
Karisma


Re: user created function taking more time [message #256737 is a reply to message #256736] Mon, 06 August 2007 08:31 Go to previous messageGo to next message
Noemi
Messages: 12
Registered: August 2007
Location: Hungary, Budapest
Junior Member

Do you need this function really?
Sysdate is a function. You can write outdate:= SYSDATE;
and you don't need a function.
Re: user created function taking more time [message #256739 is a reply to message #256736] Mon, 06 August 2007 08:34 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you just run that funtion from sql you are saying it takes 6 seconds to execute ??

It executes in .3 seconds on my database.

Are you sure it is that function that is the bottleneck, and not the code surrounding it ?


Re: user created function taking more time [message #256741 is a reply to message #256737] Mon, 06 August 2007 08:37 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
you have some context switches:
- From SQL you call the function -> PL/SQL.
- The function does a select on the database -> SQL
- The return value from the select is passed to the function -> PL/SQL
- The return of the function is used in your statement -> SQL.

SYSDATE on the other hand is a built in function. I'm pretty sure that Oracle made it as performant as it could.

MHE
Re: user created function taking more time [message #256743 is a reply to message #256739] Mon, 06 August 2007 08:39 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on how often the function is called.

When you have a lot of lines you have a context switch sql -> pl/slq -> sql for every line.

when you just do

create or replace function getcurrentdate
  return date
AS
begin
  return SYSDATE;
end;
/


You at least eliminate the pl/sql -> sql context switch in the function.




Re: user created function taking more time [message #256752 is a reply to message #256736] Mon, 06 August 2007 09:07 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Use sysdate nativity in oracle and write a function called sysdate in sybase. They will both operate the same.
Re: user created function taking more time [message #256871 is a reply to message #256736] Mon, 06 August 2007 23:39 Go to previous message
karismapanda
Messages: 58
Registered: January 2007
Member
thanks a lot for all your responses.It worked for me.
Previous Topic: Outer Join is not allowed in operand OR or IN
Next Topic: ORA-01722 during Procedure execution
Goto Forum:
  


Current Time: Thu Feb 13 11:07:08 CST 2025