Determining an Optimimum Fetch Size - spectrum_platform - 23.1

spectrum-inline

Product type
Software
Portfolio
Enrich
Integrate
Locate
Verify
Product family
Spectrum
Product
Spectrum > Geocoding > Enterprise Tax
Spectrum > GeoEnrichment
Spectrum > Web Services
Spectrum > Spatial > Spectrum Spatial
Spectrum > Spectrum Platform
Spectrum > Data Integration
Spectrum > Quality > Addressing
Spectrum > Quality > Spectrum Quality
Spectrum > Discovery
Spectrum > Dataflow Designer
Spectrum > Quality > Context Graph
Version
23.1
Language
English
Product name
Precisely Spectrum
Title
spectrum-inline
First publish date
2007
ft:lastEdition
2023-06-02
ft:lastPublication
2023-06-02T09:54:39.526000

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.

Ensure you test execution times of the test job with the different fetch size values using your own application.
  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.

You now have identified the fetch size setting that provides the optimal performance for your environment.

.