Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: ? Regarding 'SQL user-defined function' execution performance ?

    Hi All,

    I have an user-defined function in SQL which am using in a SQL select statement and i found that the execution time taken by the user-defined function is 9-10 minutes as a result of which my whole query execution time is affected.
    How to i improvise the execution time. Should i do some sort of scheduling for executing the user-defined functions seperately. Am new to the database world. Your early response with a good suggestion would really be appreciated.


    Thanks in Advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What RDBMS are you using - I assume it's SQL Server?
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    3
    I am using Oracle 9i. Hope the problem is clear to you.
    Summary: An user-defined function that by default does generate result one by one is taking more time when included in a 'SELECT' statement of my query.
    Thus affecting the performance. Thanks for the reply.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to Oracle topic; you'll get better targetted response from here.
    George
    Home | Blog

  5. #5
    Join Date
    May 2008
    Posts
    7
    for me... it would actually depend on the contents of the function... if it's just getting values from other tables then the most probable thing to do is to check the performance of the individual queries within the function for performance issues... but that's just me...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Threads merged - please do not post the same question twice
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What is your function doing? If its possible use sql or standard oracle functions to do what your function is doing as you will get much better performance.

    Alan

  8. #8
    Join Date
    May 2008
    Posts
    3
    Quote Originally Posted by AlanP
    What is your function doing? If its possible use sql or standard oracle functions to do what your function is doing as you will get much better performance.

    Alan
    Well i will try doing the same via SQL standard functions and get back. Thanks Alan.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you post the code from the function, it would really help. You are asking a question like whats wrong with my car, without letting the mechanic look at the car.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you should
    SQL> ALTER SESSION SET SQL_TRACE=TRUE;
    & run your code & then run the trace file thru TKPROF to see where the time is actually being spent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •