[jira] [Created] (NIFI-5727) Optimize GenerateTableFetch to remove unnecessary COUNT(*)

["Valentin Nikotin (JIRA)" <ji...@apache.org>]

Valentin Nikotin created NIFI-5727:
--------------------------------------

             Summary: Optimize GenerateTableFetch to remove unnecessary COUNT(*)
                 Key: NIFI-5727
                 URL: https://issues.apache.org/jira/browse/NIFI-5727
             Project: Apache NiFi
          Issue Type: Improvement
          Components: Core Framework
    Affects Versions: 1.7.1
            Reporter: Valentin Nikotin


When processor is configured to use *"Column for Value Partitioning"* processor property it still [calculates|https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/GenerateTableFetch.java#L293] {{COUNT(*)}} while it's not used further in code.

At line 292 code adds an explicit {{SELECT COUNT(*)}} to the query that calculates the bounds of the partitions:
{code:java}
            List<String> maxValueSelectColumns = new ArrayList<>(numMaxValueColumns + 1);
            maxValueSelectColumns.add("COUNT(*)");
{code}
At [line 414|https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/GenerateTableFetch.java#L414] we see that the count is only used if no columns have been set for paging:
{code:java}
                if (useColumnValsForPaging) {
                    final long valueRangeSize = maxValueForPartitioning == null ? 0 : (maxValueForPartitioning - minValueForPartitioning + 1);
                    numberOfFetches = (partitionSize == 0) ? 1 : (valueRangeSize / partitionSize) + (valueRangeSize % partitionSize == 0 ? 0 : 1);
                } else {
                    numberOfFetches = (partitionSize == 0) ? 1 : (rowCount / partitionSize) + (rowCount % partitionSize == 0 ? 0 : 1);
                }
{code}
Since the {{SELECT COUNT(*)}} sometimes takes too long to complete, we want to optimize this code and perform the {{SELECT COUNT(*)}} only when property *"Column for Value Partitioning"* has not been set.

The idea is to use *-1* value to minimize code changes:
{code:java}
            if (useColumnValsForPaging) {
                maxValueSelectColumns.add("-1");
            } else {
                maxValueSelectColumns.add("COUNT(*)");
            }
{code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)