Can anyone help. I'm using 8.1.7.0.0 on Win2k. I've a requirement to
allow a user to perform a search depending on which values he chooses.
I've run into a problem with dynamic SQL performance. I'll try and
demonstrate how I've come to this conclusion.
I've a package which contains the following simple procedure for
testing.
PROCEDURE find3(INP_table_name IN varchar2,
column_name IN varchar2,
col_value IN varchar2,
free_sql IN varchar2 := NULL,
form_name varchar2,
inp_sess_details IN varchar2,
the_cursor OUT utility_ref_cur)
IS
BEGIN
OPEN the_cursor FOR
SELECT ID "111",
NAME "85",
BIRTH_DATE "118",
F_ID "117",
M_ID "119"
,rtrim(CURR_H_NUM,' ') ||' '|| rtrim(CURR_HD_NUMBER,' ')"92",
A.ROWID "1"
from TEST a
WHERE a.ID = '036349910001' order by 1;
END find3;
The parameters are meaningless in this example. This returns the
cursor within 1 sec on table test containing 700,000 rows.
The next example uses a function to produce the same sql as above
dynamically.
PROCEDURE find2(INP_table_name IN varchar2,
column_name IN varchar2,
col_value IN varchar2,
free_sql IN varchar2 := NULL,
form_name varchar2,
inp_sess_details IN varchar2,
the_cursor OUT utility_ref_cur)
IS
BEGIN
OPEN the_cursor FOR
QUERY2(INP_table_name,column_name,col_value,free_sql,form_name,inp_sess_details,0);
END find2;
This example using dynamic sql takes around 9 secs to return the
cursor when there are 700,000 records in TEST but returns within 1 sec
with around 100 to 200 records in test. The overhead in the query
function is negligible.
It would appear there is a significant overhead in using the sql
produced dynamically as opposed to using static SQL.
Can anyone shine any light on this prob.
