HELP - improving performance

Hi there:

I am upgrading an existing Oracle application that does all of its queries using method 4 (pure dynamic) with no bind variables. Thus, the embedded SQL code looks like:

...

    EXEC SQL PREPARE CMD1 FROM :DynamicCommand;
...

    EXEC SQL DECLARE CURSOR1 CURSOR FOR CMD1;
...

    EXEC SQL DESCRIBE BIND VARIABLES FOR CMD1 INTO sqlbind;
... (a check is made to make sure there are NO bind variables)
    EXEC SQL OPEN CURSOR1 USING DESCRIPTOR sqlbind;
...

    EXEC SQL DESCRIBE SELECT LIST FOR CMD1 INTO sqlda;
...

    loop:

      EXEC SQL FETCH CURSOR1 USING DESCRIPTOR sqlda;
      send results to client until no more results

Every query is run thru this one procedure. In looking at the queries the application produces, there are only a few (10 or so) distinct patterns. Furthermore, most of these queries will only ever return a single row (the select is against a single unique key).

By converting the queries to bind form, I would guess that the caching of queries in Oracle 7.x could improve performance. What other changes must be made to handle a set of these standard queries and take advantage of Oracle caching? Can I use the sequence shown above if every one of the queries is simply expressed in bind form with a single bind variable that is a VARCHAR? Or must I declare a separate cursor for each of the queries? I'm not looking for the best performance (at least not yet), but rather improved performance with minimal code changes.  

Thanks

Fritz Sieker Received on Thu Apr 18 1996 - 00:00:00 CEST