# Improving reflection performance

**URL:** <https://community.dremio.com/t/improving-reflection-performance/892>\
**Category:** Site Feedback\
**Created:** [March 20, 2018, 8:37am UTC](https://community.dremio.com/t/improving-reflection-performance/892 "2018-03-20T08:37:02Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![drajput](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@drajput](https://community.dremio.com/u/drajput)\
**Post date:** [March 20, 2018, 8:37am UTC](https://community.dremio.com/t/improving-reflection-performance/892/1 "2018-03-20T08:37:02Z")

</div>

Hi,

What parameters are available to fiddle with and improve reflection performance? Is there any concurrency and partition control to achieve that?

I am running a single node dremio server on a c5.x ec2. Was able to cut down latency on a big dataset(15 GB) from 25 to 8 secs on representative queries by changing query queue and concurrent reflection control from the admin panel.

Any other suggestions?

---

<div class="post-metadata">

**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\
**Post date:** [March 20, 2018, 9:22am UTC](https://community.dremio.com/t/improving-reflection-performance/892/2 "2018-03-20T09:22:25Z")

</div>

What is the data source and what is the performance without reflections?

Can you share your query profile?

> **[How To Share A Query Profile](https://www.dremio.com/tutorials/share-query-profile-dremio/)**
>
> Tutorial explaining how to share a query profile in Dremio.

Where are you storing your reflections? EBS?

---

<div class="post-metadata">

**Author:** ![drajput](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@drajput](https://community.dremio.com/u/drajput)\
**Post date:** [March 20, 2018, 10:41am UTC](https://community.dremio.com/t/improving-reflection-performance/892/3 "2018-03-20T10:41:21Z")

</div>

```
select "accounts"."STATE" AS accountsSTATE, MAX( "measures"."BALANCE") AS measuresBALANCE 

from measures as measures, accounts as accounts 

where 1=1 AND "measures"."ACCT_NO"="accounts"."ACCT_NO" AND "measures"."PERIOD_END" >= '1921-01-01' AND "measures"."PERIOD_END" < '2018-03-20' 

group by "accounts"."STATE" 

order by measuresBALANCE desc
limit 10 

```

00-00 Screen : rowType = RecordType(VARCHAR(65536) accountsSTATE, DOUBLE measuresBALANCE): rowcount = 10.0, cumulative cost = {6.258067439200002E7 rows, 4.73785018861381E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61902  
00-01 ComplexToJson : rowType = RecordType(VARCHAR(65536) accountsSTATE, DOUBLE measuresBALANCE): rowcount = 10.0, cumulative cost = {6.258067339200002E7 rows, 4.73785017861381E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61901  
00-02 Project(accountsSTATE=[$0], measuresBALANCE=[$1]) : rowType = RecordType(VARCHAR(65536) accountsSTATE, DOUBLE measuresBALANCE): rowcount = 10.0, cumulative cost = {6.258066339200002E7 rows, 4.73785007861381E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61900  
00-03 Limit(fetch=[10]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 10.0, cumulative cost = {6.258065339200002E7 rows, 4.73784987861381E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61899  
00-04 TopN(limit=[10]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 25001.37599999999, cumulative cost = {6.258064339200002E7 rows, 4.73784947861381E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61898  
00-05 RoundRobinExchange : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 25001.37599999999, cumulative cost = {6.255564201600002E7 rows, 4.73452736768E8 cpu, 3.0017523200000003E7 io, 6.453442842111998E9 network, 1.8503958528000003E8 memory}, id = 61897  
01-01 HashAgg(group=[{0}], measuresBALANCE=[MAX($1)]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 25001.37599999999, cumulative cost = {6.2530640640000015E7 rows, 4.7325272576E8 cpu, 3.0017523200000003E7 io, 6.248631569919998E9 network, 1.8503958528000003E8 memory}, id = 61896  
01-02 Project(STATE=[$0], measuresBALANCE=[$1]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 500027.5199999999, cumulative cost = {6.203061312000001E7 rows, 4.6325217536E8 cpu, 3.0017523200000003E7 io, 6.248631569919998E9 network, 1.7623910092800003E8 memory}, id = 61895  
01-03 HashToRandomExchange(dist0=[[$0]]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE, ANY E\_X\_P\_R\_H\_A\_S\_H\_F\_I\_E\_L\_D): rowcount = 500027.5199999999, cumulative cost = {6.153058560000001E7 rows, 4.6225212032E8 cpu, 3.0017523200000003E7 io, 6.248631569919998E9 network, 1.7623910092800003E8 memory}, id = 61894  
02-01 UnorderedMuxExchange : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE, ANY E\_X\_P\_R\_H\_A\_S\_H\_F\_I\_E\_L\_D): rowcount = 500027.5199999999, cumulative cost = {6.1030558080000006E7 rows, 4.5425168E8 cpu, 3.0017523200000003E7 io, 1.0429340416E8 network, 1.7623910092800003E8 memory}, id = 61893  
03-01 Project(STATE=[$0], measuresBALANCE=[$1], E\_X\_P\_R\_H\_A\_S\_H\_F\_I\_E\_L\_D=[hash32($0)]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE, ANY E\_X\_P\_R\_H\_A\_S\_H\_F\_I\_E\_L\_D): rowcount = 500027.5199999999, cumulative cost = {6.053053056E7 rows, 4.5375165248E8 cpu, 3.0017523200000003E7 io, 1.0429340416E8 network, 1.7623910092800003E8 memory}, id = 61892  
03-02 HashAgg(group=[{0}], measuresBALANCE=[MAX($1)]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 500027.5199999999, cumulative cost = {6.003050304E7 rows, 4.5025145984000003E8 cpu, 3.0017523200000003E7 io, 1.0429340416E8 network, 1.7623910092800003E8 memory}, id = 61891  
03-03 Project(STATE=[$3], measuresBALANCE=[$1]) : rowType = RecordType(VARCHAR(65536) STATE, DOUBLE measuresBALANCE): rowcount = 1.00005504E7, cumulative cost = {5.002995264E7 rows, 2.5024045184000003E8 cpu, 3.0017523200000003E7 io, 1.0429340416E8 network, 229413.888 memory}, id = 61890  
03-04 HashJoin(condition=[=($0, $2)], joinType=[inner]) : rowType = RecordType(BIGINT ACCT\_NO, DOUBLE measuresBALANCE, BIGINT ACCT\_NO0, VARCHAR(65536) STATE): rowcount = 1.00005504E7, cumulative cost = {4.002940224E7 rows, 2.3023935104000002E8 cpu, 3.0017523200000003E7 io, 1.0429340416E8 network, 229413.888 memory}, id = 61889  
03-06 Project(ACCT\_NO=[$0], measuresBALANCE=[$1]) : rowType = RecordType(BIGINT ACCT\_NO, DOUBLE measuresBALANCE): rowcount = 1.00005504E7, cumulative cost = {3.0001651200000003E7 rows, 7.00038528E7 cpu, 3.0001651200000003E7 io, 3.0001651200000003E7 network, 0.0 memory}, id = 61883  
03-08 Project(ACCT\_NO=[$1], BALANCE=[$2]) : rowType = RecordType(BIGINT ACCT\_NO, DOUBLE BALANCE): rowcount = 1.00005504E7, cumulative cost = {2.00011008E7 rows, 5.0002752E7 cpu, 3.0001651200000003E7 io, 3.0001651200000003E7 network, 0.0 memory}, id = 61882  
03-09 ParquetScan(table=["\_\_accelerator".“258e9e3e-ee39-4dd3-959c-55f27b98cf46”.“f6f2dbb1-93ef-407f-8997-96cd7a4779ee”], columns=[`PERIOD_END`, `ACCT_NO`, `BALANCE`], splits=[2], filters=[[Filter on `PERIOD_END`: booleanAnd(greater\_than\_or\_equal\_to(`PERIOD_END`, cast( -1546300800000 as TIMESTAMP)) , less\_than(`PERIOD_END`, cast( 1521504000000 as TIMESTAMP)) )]]) : rowType = RecordType(TIMESTAMP(0) PERIOD\_END, BIGINT ACCT\_NO, DOUBLE BALANCE): rowcount = 1.00005504E7, cumulative cost = {1.00005504E7 rows, 3.0001651200000003E7 cpu, 3.0001651200000003E7 io, 3.0001651200000003E7 network, 0.0 memory}, id = 61881  
03-05 Project(ACCT\_NO0=[$0], STATE=[$1]) : rowType = RecordType(BIGINT ACCT\_NO0, VARCHAR(65536) STATE): rowcount = 1130.8799999999997, cumulative cost = {26069.760000000002 rows, 217644.80000000002 cpu, 15872.0 io, 7.429175296E7 network, 209510.40000000002 memory}, id = 61888  
03-07 BroadcastExchange : rowType = RecordType(BIGINT ACCT\_NO, VARCHAR(65536) STATE): rowcount = 1130.8799999999997, cumulative cost = {24938.88 rows, 215383.04 cpu, 15872.0 io, 7.429175296E7 network, 209510.40000000002 memory}, id = 61887  
04-01 HashAgg(group=[{0, 1}]) : rowType = RecordType(BIGINT ACCT\_NO, VARCHAR(65536) STATE): rowcount = 1130.8799999999997, cumulative cost = {23808.0 rows, 206336.0 cpu, 15872.0 io, 6.5027584E7 network, 209510.40000000002 memory}, id = 61886  
04-02 UnionExchange : rowType = RecordType(BIGINT ACCT\_NO, VARCHAR(65536) STATE): rowcount = 7936.0, cumulative cost = {15872.0 rows, 79360.0 cpu, 15872.0 io, 6.5027584E7 network, 0.0 memory}, id = 61885  
05-01 ParquetScan(table=["\_\_accelerator".“dd9d92c6-78de-4cbf-89e6-4d84001beb47”.“1482bfce-1dad-4a1b-b9bd-d8c16075fbdf”], columns=[`ACCT_NO`, `STATE`], splits=[1]) : rowType = RecordType(BIGINT ACCT\_NO, VARCHAR(65536) STATE): rowcount = 7936.0, cumulative cost = {7936.0 rows, 15872.0 cpu, 15872.0 io, 15872.0 network, 0.0 memory}, id = 61884

---

<div class="post-metadata">

**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\
**Post date:** [March 20, 2018, 1:27pm UTC](https://community.dremio.com/t/improving-reflection-performance/892/4 "2018-03-20T13:27:15Z")

</div>

Are you using a raw reflections or an aggregation reflection? For this query an aggregation reflection would be appropriate.

One of my colleagues may comment on your query plan.

---

<div class="post-metadata">

**Author:** ![drajput](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@drajput](https://community.dremio.com/u/drajput)\
**Post date:** [March 20, 2018, 2:07pm UTC](https://community.dremio.com/t/improving-reflection-performance/892/5 "2018-03-20T14:07:17Z")

</div>

I was trying to create aggregation reflection for that dataset. But somehow the server dies when I define it.

---

<div class="post-metadata">

**Author:** ![kelly](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/kelly/32/11_2.png) [@kelly](https://community.dremio.com/u/kelly)\
**Post date:** [March 20, 2018, 3:41pm UTC](https://community.dremio.com/t/improving-reflection-performance/892/6 "2018-03-20T15:41:25Z")

</div>

What is the error? You can go to jobs and filter on acceleration.

---

<div class="post-metadata">

**Author:** ![drajput](https://avatars.discourse-cdn.com/v4/letter/d/c6cbf5/32.png) [@drajput](https://community.dremio.com/u/drajput)\
**Post date:** [March 20, 2018, 5:07pm UTC](https://community.dremio.com/t/improving-reflection-performance/892/7 "2018-03-20T17:07:13Z")

</div>

Nothing in the job logs. Looks like I’ll have to enable detailed debug logging to see what is really happening/