Reducing the number of requests to the database using stored procedures

To achieve better performance, we should reduce the number of requests made to the database, especially if those requests have to be routed to a network. There are many strategies to reduce these requests. In this recipe, we discuss the use of stored procedures and packages for achieving this goal.

In this recipe, we execute a simple query in the SH schema. In the first script, we will use SQL*Plus to test the SQL statement and the corresponding stored procedure execution. In the Java program, we will use the same query and stored procedure. For each of these tests, record the execution time.

How to do it...

Th e following steps will demonstrate how to reduce the number of requests to the database:

1. Open your preferred text editor and copy the following script, and save it as StoredProcedure.SQL:

   SET ECHO OFF
   SET FEEDBACK OFF
   SET PAGESIZE 80
   CREATE OR REPLACE PROCEDURE SH.SALES_BY_PRODUCT(P OUT SYS_REFCURSOR) IS
   BEGIN
    ...