Inline functions in pl/sql?

views:

206

answers:

+2 Q:

Inline functions in pl/sql?

I have 5 lines of code as function which is being called 100000 times is there a way to make the function inline so that I don't experienced a call overhead in PL/SQL. Oracle 9i is the version I am using.

Update: The In lining of code does not have much improvement but I gained 2 seconds though. Now I am looking for efficient version of float data type in pl/sql. BINARY FLOAT did not work, NUMBER(10,5) is the one already being employed.

You've determined that the function call overhead is not contributing much to the performance issue, so inlining would not be helpful here anyway.

I assume you're not running any SQL or calling any SQL functions in your PL/SQL? If so I'd look there next.

Your next option is native compilation - see Compiling PL/SQL Code for Native Execution for details. You might get some improvement since you're only doing maths.

Otherwise, you may need to look beyond PL/SQL. You can call code written in many other languages from PL/SQL, including C, C++ and Java. Refer to Calling External Procedures for more info.

Jeffrey Kemp 2010-04-27 01:35:47

actually I am calling sin and cos function that takes up to 7 seconds, how can i minimize that?

yesraaj 2010-04-27 04:21:49

how many times are you calling the function? how many sin/cos calls is it making? are the arguments very unique or are there many repeating arguments (i.e. could you cache the results for commonly-occurring values?)

Jeffrey Kemp 2010-04-27 13:33:46

@Jeffrey Kemp I can't cache sin/cos because they vary all the time but, I can eliminate select statement inside the loop, I am looking for a map(key, value) kind of data structure in pl/sql.

yesraaj 2010-04-28 07:07:36

@yesraaj: did you read my answer? "I assume you're not running any SQL or calling any SQL functions in your PL/SQL?" If you're running any SQL, it doesn't matter how often you call sin/cos, I'd be 99.9% sure that the SQL queries are the problem. sigh :(

Jeffrey Kemp 2010-04-28 11:11:31

@Jeffrey Kemp yes I read your answer, as you said major performance lag will be due to SQL, I am trying to eliminate the select query, but with out sin/cos functions I could save 6 seconds that is 25% of time needed for the whole procedure to run.

yesraaj 2010-04-28 11:55:49

Is the SQL the other 75% of the time? If so I'd focus on that first.

Jeffrey Kemp 2010-04-28 12:53:49

@Jeffrey Kemp because I could do the SQL removal myself and needed help on math function call, I am asking only about that here. I am caching the values of select statement during first call and populating the values in a map for rest of the run that would be fine for removing the SQL statement right or do you want to remove the SQL out of the loop? About cos function do you have any idea to get a approximated version of cosine that performs better than the usually cos fn?

yesraaj 2010-04-29 05:59:32

Have you measured the time it takes to actually run the SQL statement, not including the times when you use the cache? RE the cos function, sorry I don't know.

Jeffrey Kemp 2010-04-30 03:38:32

@Jeffrey Kemp yes I got 2 sec gain after the caching

yesraaj 2010-05-06 04:37:52

ansaurus

tags:

views:

answers:

Inline functions in pl/sql?

related questions