<!--
    Licensed to the Apache Software Foundation (ASF) under one or more 
    contributor license agreements.  See the NOTICE file distributed with
    this work for additional information regarding copyright ownership. 
    The ASF licenses this file to you under the Apache License, Version 2.0
    (the "License"); you may not use this file except in compliance with 
    the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0
    
    Unless required by applicable law or agreed to in writing, software 
    distributed under the License is distributed on an "AS IS" BASIS, 
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and 
    limitations under the License.
-->

# Query with spark-sql performacne tuning
  This tutorial guides you to create CarbonData Tables and optimize performance.
  The following sections will elaborate on the below topics :

* [The influence of spark.sql.codegen.wholeStage configuration on query](#the-influence-of-codegen-configuration-on-query) 

## The influence of codegen configuration on query 

In practice, we found that when the number of counters(columns applied SUM operator) reaches a certain threshold, the query time increases dramatically. 

As shown in the figure below(spark 2.1):

![File Directory Structure](../docs/images/codegen.png?raw=true)

The horizontal axis is the number of counter, and the vertical axis is the time consumed in seconds.

It can be seen from the figure that when the number of counter exceeds 85, the query time is significantly increased.

After analysis, this problem is related to [`spark.sql.codegen.wholeStage`](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-whole-stage-codegen.html), which is enabled by default since spark 2.0. and it will do all the *internal optimization possible from the spark catalyst side*. 

**Whole-Stage Java Code Generation** (aka *Whole-Stage CodeGen*) is a physical query optimization in Spark SQL that fuses multiple physical operators (as a subtree of plans that [support code generation](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-CodegenSupport.html)) together into a single Java function.

Whole-Stage Java Code Generation improves the execution performance of a query by collapsing a query tree into a single optimized function that eliminates virtual function calls and leverages CPU registers for intermediate data.

As the number of counter grows, generated codes for data process become larger. There are nearly 3000 lines of code when 34 counters. Java does not recommend huge methods, which can not make use of JIT and the code will run in interpretation mode. So the performance drops sharply when the counter in query grows.

But unfortunately, spark 2.1 only provides switching capability. User can only choose to turn the function on or off. This leads to a sharp drop in performance when the aggregation operator is too large. If the query scenario has many counters, user can configure "spark.sql.codegen.wholeStage" = false. This depends on the query scenario.

Fortunately, spark 2.3 provide more configuration. Users can better configure this parameter. 

User can check more configuration [here](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-properties.html#spark.sql.codegen.wholeStage).

Recommended configuration for spark2.3：

| Parameter name                    | value | describe                                                     |
| --------------------------------- | ----- | ------------------------------------------------------------ |
| spark.sql.codegen.wholeStage      | true  | (internal) Whether the whole stage (of multiple physical operators) will be compiled into a single Java method (true) or not (false).   <br/> Default: true<br/>Use SQLConf.wholeStageEnabled method to access the current value. |
| spark.sql.codegen.hugeMethodLimit | 8000  | (internal) The maximum bytecode size of a single compiled Java function generated by whole-stage codegen.<br/>Default: 65535<br/>The default value 65535 is the largest bytecode size possible for a valid Java method. When running on HotSpot, it may be preferable to set the value to 8000 (which is the value of HugeMethodLimit in the OpenJDK JVM settings)<br/>Use SQLConf.hugeMethodLimit method to access the current value. |



