Determining an Optimimum Fetch Size - spectrum_platform - 23.1

In the Read from DB stage, the optimum fetch size is calculated by taking execution time readings between a Read from DB stage and a Write to Null stage.

1. Create a job in the Spectrum Enterprise Designer.
2. Drag a Read from DB stage to the canvas.
3. Drag a Write to Null stage to the canvas.
4. Create a channel between the two stages.
5. Double-click on the Read from DB stage to configure it to read data from a table containing the test data.
   1. In the General tab, in the Connection field, select the database that contains the test data.
   2. Click Build SQL... to create the SQL query using selected schema and tables from which to read the data.
      Ensure the selected table has at least a 1000 records to allow optimum test values.
   3. In the Runtime tab, check the Fetch size checkbox.
   4. In the attached field, enter the number of records you want to read in one instance.
      The Spectrum Technology Platform has been tested to work optimally with a Fetch size of up to 1000.
   5. Click OK.
6. Save the job.
7. Run the job.
   The Execution Details window opens.
8. Click Refresh.
9. Note the Started and Finished times.
10. Repeat the steps 7 - 9, gradually increasing the fetch size to identify the optimal setting for your server.