Table of Contents

How to process large SQL resultsets


A developer asks:

How can our application process huge SQL resultsets?

Is there a way to implement 'cursor-style' processing (iterating through a result set and processing one row at a time) without returning the results to a Page List?


Suggested Approach

The recommended approach is to get only a list of keys to the records in the resultset, and then iterate over the keys, opening one record at a time. After the first record is processed, the clipboard pages it occupied can be deleted (or reused for the second record).

In the absence of a key, or a natural combination of values that constitute the equivalent of a primary key, you can use cursor-style processing by writing Java steps. You can use Java steps to declare the cursor, return one row using the cursor, and then using the Java step or a wrapper activity to call the activity on that one row - and then repeat the process. You will need to manage the database connection directly to hold onto it.

In some situations, case match rules may be applicable. Review Rule-Declare-CaseMatch documentation to see whether this rule type is applicable to your problem.

Suggest Edit

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.