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

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

     [ https://issues.apache.org/jira/browse/NIFI-5727?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Valentin Nikotin updated NIFI-5727:
-----------------------------------
    External issue URL:   (was: https://github.com/apache/nifi/pull/3094)

> 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
>            Priority: Minor
>
> 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)