RSS Feed

Method R Discussion Group

I'm developing a web application in Cold Fusion.  The app runs most code on the client side and it's not great at handling large numbers of database calls within a web page.  I have a query "Select col1, col2, col3, col4 from table1, table2 where ........".  This query returns 10k rows in 30-40ms.  But then I need to get lookup info for each of these rows.  This involved 10k individual calls to the database, which was very slow.  I couldn't figure out how to do a simple join with the lookup table since it's a one-to-many relationship so I tried using a function in the 1st query to retrieve the lookup data so I could get all the data in one trip to the database.  It's something like this: "Select col1, col2, col3, col4, function(col1, col2, col3, col4) from table1, table2 Where ......".  This works really good for a few rows but when the query returns 10k rows running it with the function takes 11-13 seconds as opposed to the 30-40ms without the function.  This is still some faster than the 10k separate Selects to the lookup table but it's not satisfactory.  The function itself contains a Select for the lookup table, then packs the result rows into a comma-delimited list which is the return variable back to the query.  I've optimized this query via TOAD and coded it using bind variables.  Since it's not a simple Oracle function I didn't think I could use a function-based index.  I also ran the query with the function through the TOAD optimizer but it didn't have any helpful ideas.  I wondered if I might be able to use some type of correlated sub-query with the function in one Select and the other stuff in another Select but wasn't sure how to do this.  We're running Oracle 10R2.  Does anyone have an idea or two I can try? 


Thanks much,

Paul Hargreaves

Well, the simplest correlated query form you could use would be to use scalar subqueries for each column to do the lookup like this:

select col1,
(select desc from lookup where code = col1) as col1_desc,
col2,
(select desc from lookup where code = col2) as col2_desc,
col3,
(select desc from lookup where code = col3) as col3_desc,
col4,
(select desc from lookup where code = col4) as col4_desc,
from table1, table2
where ....

Now, this may look "bad" because you've got 4 queries occurring for each row, but Oracle will attempt to optimize these calls by using subquery caching.  For example, if the col1 value for the first row is 100 and the subquery against the lookup table returns ABC.  If you have another row (say row 100) that has the same col1 value of 100, then the query doesn't execute again, it simply gets the answer (ABC) from the cache.  So, if you have just a few distinct values of each column, then the caching should significantly reduce the calls and the performance won't be suffer so much.

I'd try this to test it and see just how "cacheable" the subqueries are.  With any luck, it will find the answers in cache most of the time and it should work much more efficiently than the repeated function calls you're using now.

By the way, if you could post the actual query and even some basic AUTOTRACE results at a minimum, it would be helpful.  Of course, the more detail you can provide, the easier it is to suggest appropriate courses of action.

I've got another idea or two, but try this first and let us know how it goes.

Thanks Karen,

I don't think I did a real good job of explaining.  Because of the sensitivity of our app I can't post the actual query with names but here's a generalized one that matchs:

Select Distinct B1.col1, B1.col2 B1.col3, A1.col5,
      fcn_funcname(B1.col1, B1.col2, B1.col3, A1.col5) as my_col
  From Table1 A1,
      Table2 B1
  Where A1.col1 = B1.col1
  And A1.col4 = B1.col4
  And B1.col6 = 'XXXX'

This query retrieves 10k rows in ~100 MS without the function but takes 18 seconds with the function.  I'm using the function to retrieve the last column (my_col) because there are multiple rows of this column that match the criteria of each distinct combination of the 1st 4 columns (B1.col1, B1.col2, B1.col3, A1.col5) and I that won't work.  What I do in the function is create a comma-delimited list of the values that match and return them as one value, which I then use to drive an WHERE IN clause in a subsequent query.  I've used this strategy with great success in another case when I've just sent one variable as input to a function and the "lookup table" was something entirely different from the tables in the driver query.  I think a root of my current problem may be that the query in the function uses the same two tables as the driver query and contains 4 input variables to the Where clause.  Heres the code in the function:

CREATE OR REPLACE function ALPHA_USER.fcn_SCC2_formrowid
 (p_col1 varchar2,
  p_col2 varchar2,
  p_col3 date,
  p_col5 date)
    return varchar2 is


sql_statement := 'Select Distinct TO_CHAR(col4) As my_col 
                  From Table1 A, Table2 B     
                  Where A.col1 = :p_col1       
                  And B.col2 = :p_col2   
                  And B.col6 = 'XXXX'   
                  And B.col4 = A.col4
                  And B.col3 = :p_col3               
                  And A.col5 = :p_col5
                  And A.col1 = B.col1';

I'm using Dynamic SQL to build the query and I tried to use bind variables to make it faster.  Hopefully this explanation is a bit clearer than the 1st try.  Hope you're doing well and it's not raining too much.  We miss you over here in East TN.  Thanks.  Paul Hargreaves

OK...I see what you're trying to do now.  Well, how about trying out Tom Kyte's "stragg" function.  I've copied and pasted it here, along with how your query example would look using it.  The problem with your current use of the function you wrote is that you have to keep accessing the same tables over and over (Table1 and Table2).  With a function like stragg, you only hit the tables once and simply let the function do the work of consolidating the multiple rows of col4 values into a single comma-delimited string.

Here's the code (you can also search asktom.oracle.com for stragg):

create or replace type string_agg_type as object
(total varchar2(4000),
 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,
member function
 ODCIAggregateIterate(self IN OUT string_agg_type ,
      value IN varchar2 )
return number,
member function
 ODCIAggregateTerminate(self IN string_agg_type,
        returnValue OUT  varchar2,
        flags IN number)
return number,
member function
 ODCIAggregateMerge(self IN OUT string_agg_type,
                      ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
  sctx := string_agg_type( null );
  return ODCIConst.Success;
end;
 
member function ODCIAggregateIterate(self IN OUT string_agg_type,value IN varchar2 )
return number
is
begin
  self.total := self.total || ',' || value;
  return ODCIConst.Success;
nd;
 
member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
return number
is
begin
  returnValue := ltrim(self.total,',');
  return ODCIConst.Success;
end;
 
member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
return number
is
begin
  self.total := self.total || ctx2.total;
  return ODCIConst.Success;
end;
end;
/

CREATE or replace
 FUNCTION stragg(input varchar2 )
 RETURN varchar2
 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


Select B1.col1, B1.col2 B1.col3, A1.col5, stragg(A1.col4) as my_col
  From Table1 A1,
      Table2 B1
 Where A1.col1 = B1.col1
  And A1.col4 = B1.col4
  And B1.col6 = 'XXXX'
group by B1.col1, B1.col2 B1.col3, A1.col5
/

Give this a go and see how it does.