Step 2: Optimizing Network Use
The goal of network optimization techniques is to reduce network traffic for a given SQL execution. Changes with respect to the standard methods (as described in DB Link Basic Use) are needed on two different occasions, when:
Deferred execution can also be used to execute the same query several times in only one execute call. This reduces the number of queries sent to the server.
Similarly, several rows can be requested at a time when retrieving results from the RDBMS. This also reduces network use. These techniques use the notion of an Array of parameters and an Array of columns.
A Query is Sent to the RDBMS
First, look at the input side, that is, when queries are sent to the RDBMS.
The same insertion as in previous steps is run, but in only one execution. To do so, you need to request an array of n parameters from DB Link. Then, values are set for each set of parameters. Finally, an execute statement is run for the entire set of parameters.
The setParamArraySize method is used to specify the size of the parameter array:
static const IldUShort nbParam = 5 ;
if (!request->setParamArraySize(nbParam)) {
IldDisplayError("Could not set parameter array size : ", request) ;
Ending(dbms) ;
exit(1) ;
}
The parse and parameter binding steps are done as described in Step 1.
The parameter values are now set. This is done as in Step 1, except that the third argument of setParamValue is used to specify which parameter to set:
static const IldUShort strLen = 20 ;
IldInt i, j, nbRows ;
char strBuf[strLen + 1] ;
for (i = 0 ; i < nbParam ; i++) {
nVal = i ;
// Build a new string value for this set of parameters.
for (j = 0 ; j < strLen ; ++j)
strBuf[j] = 'a' + i ;
// Set parameter values.
if (!request->setParamValue(nVal, 0, i) ||
!request->setParamValue(strBuf, 1, i)) {
IldDisplayError("Set parameter value failed :", request) ;
Ending(dbms) ;
exit(1) ;
}
}
Now, the query is run and the number of times it will be run is specified.
When deferred execution is used, by default the parameter array size is used to specify the number of times the query is to be executed. Consequently, the second argument of IldRequest::execute(IldInt*, IldInt) is not required (the default value is used).
The first argument of the execute method is set to the number of rows updated by the query (5 in this case).
if (!request->execute(&nbRows, nbParam)) {
IldDisplayError("Insertion failed : ", request) ;
Ending(dbms) ;
exit(1) ;
}
else cout << "\t" << nbRows << " row inserted." << endl ;
A Results Set is Retrieved for the RDBMS
Optimization by deferred execution can also be used when retrieving a results set from the RDBMS.
The setColArraySize method is used to specify the number of rows to retrieve in one fetch. After calling this method, the other steps of the application will be exactly the same as they would be without this optimization.
The bigger the array size, the less the server has to be contacted, and the fewer network resources are used. However, more memory is needed in this case. DB Link fetches all the rows in memory. Then, from the application point of view, the process is the same as it would be to get only one row.
The fetch method checks to see whether there is a row available in memory. If there is, the row is made available to the application. Otherwise, the method automatically gets the next block of rows from the RDBMS.
The runDisplay method can be seen in the complete source code for this step. This method is the same as in Step 1 (except the call to IldRequest::setColArraySize()).
Conclusion
This step demonstrated how several operations can be executed in only one step to reduce network use.
See source code.
