Database Server Side versus Client Side

Performance is a critical aspect of any application. Because network round trips are very costly in a typical client–server environment,minimizing the number of round trips is key to ensuring good performance.

 

You should decide whether your PL/SQL procedures reside on the server or on the client based on whichever results in the fewest number of network round trips. Here are some guidelines:

 

*Procedures that call Oracle Forms built–ins (more generally,client built–ins) must reside on the client.

 

*Procedures that reference fields directly, either as :block.field or via NAME_IN/COPY, must reside on the client. You can avoid referencing fields directly by accepting field values or names as parameters to your PL/SQL procedures, which also improves your code’s modularity.

 

*If a procedure contains three or more SQL statements, or becomes very complicated, the procedure usually belongs on the server.

 

*Procedures that perform no SQL and that need no database access should reside wherever they are needed.

 

If a procedure is called from the server, it must reside on the server. If a procedure is called from both client and server, it should be defined in both places, unless the procedure is very complicated and double maintenance is too costly. In the latter case, the procedure should reside on the server.