Hi Team,
We are querying the compressed json files. These files have simple json
records (1 record / row, no complex json).
Total files - 287
Average file size - 17,100 KB
Drill version - 1.20.1
Drill mode - embedded
The query text is
SELECT col1, col2, col3 from hugedata.TestTable
WHERE ID='01U00033' and SECTOR='01'
AND TO_DATE(exam_Date,'yyyy-MM-dd') >= TO_DATE('2018-01-01','yyyy-MM-dd')
AND TO_DATE(exam_Date,'yyyy-MM-dd') <= TO_DATE('2018-05-30','yyyy-MM-dd')
AND FLG <> 'Y' ORDER BY exam_Date,col1, col2 ASC
The query took 35 mins to give output. Below is the profile snapshot.
Please advise on how query performance can be improved
Fragment Profiles
Overview
<http://bl4ul296:8047/profiles/1d38154a-8544-03fd-9bf5-296aab01e1ec#fragment-overview>
Major FragmentMinor Fragments ReportingFirst StartLast StartFirst EndLast
EndMin RuntimeAvg RuntimeMax Runtime% BusyLast UpdateLast ProgressMax Peak
Memory
00-xx-xx 1 / 1 5.774s 5.774s 35m35s 35m35s 35m29s 35m29s 35m29s 86.59%
2.570s 35m28s 9MB
01-xx-xx 1 / 1 5.808s 5.808s 35m35s 35m35s 35m29s 35m29s 35m29s 0.15% 2.570s
34m43s 2MB
02-xx-xx 3 / 3 5.810s 5.818s 35m35s 35m35s 35m29s 35m29s 35m29s 99.71%
2.570s 2.571s 7MB
Operator ProfilesShow/Hide Estimated Rows
Overview
<http://bl4ul296:8047/profiles/1d38154a-8544-03fd-9bf5-296aab01e1ec#operator-overview>
WARNING: Query result was automatically limited to 1,000 rows
Operator IDTypeAvg Setup TimeMax Setup TimeAvg Process TimeMax Process TimeMin
Wait TimeAvg Wait TimeMax Wait Time% Fragment Time% Query TimeRowsAvg Peak
MemoryMax Peak Memory
00-xx-00 SCREEN 0.000s 0.000s 0.039s 0.039s 0.001s 0.001s 0.001s 54.33%
0.00% 0 - -
00-xx-01 PROJECT 0.003s 0.003s 0.001s 0.001s 0.000s 0.000s 0.000s 0.91%
0.00% 0 1MB 1MB
00-xx-02 SELECTION_VECTOR_REMOVER 0.000s 0.000s 0.001s 0.001s 0.000s 0.000s
0.000s 1.74% 0.00% 0 1MB 1MB
00-xx-03 LIMIT 0.000s 0.000s 0.001s 0.001s 0.000s 0.000s 0.000s 1.92% 0.00%
0 1MB 1MB
00-xx-04 LIMIT 0.001s 0.001s 0.000s 0.000s 0.000s 0.000s 0.000s 0.37% 0.00%
0 1MB 1MB
00-xx-05 SELECTION_VECTOR_REMOVER 0.008s 0.008s 0.002s 0.002s 0.000s 0.000s
0.000s 2.43% 0.00% 0 1MB 1MB
00-xx-06 TOP_N_SORT 0.000s 0.000s 0.024s 0.024s 0.000s 0.000s 0.000s 33.84%
0.00% 0 1MB 1MB
00-xx-07 PROJECT 0.004s 0.004s 0.002s 0.002s 0.000s 0.000s 0.000s 2.37%
0.00% 0 1MB 1MB
00-xx-08 UNORDERED_RECEIVER 0.000s 0.000s 0.001s 0.001s 0.009s 0.009s 0.009s
2.10% 0.00% 0 - -
01-xx-00 HASH_PARTITION_SENDER 0.299s 0.299s 0.009s 0.009s 0.103s 0.103s
0.103s 12.93% 0.00% 2 202KB 202KB
01-xx-01 UNORDERED_RECEIVER 0.000s 0.000s 0.060s 0.060s 44.424s 44.424s
44.424s 87.07% 0.00% 2 4KB 4KB
02-xx-00 JSON_SUB_SCAN 0.000s 0.000s 17m37s schedule35m15s 0.071s 3.052s
schedule6.257s 99.70% 99.70% 264,412,354 1MB 2MB
02-xx-01 PROJECT 0.070s 0.081s 0.009s 0.013s 0.000s 0.000s 0.000s 0.00%
0.00% 2 1MB 1MB
02-xx-02 PROJECT 0.040s 0.044s 0.020s 0.042s 0.000s 0.000s 0.000s 0.00%
0.00% 2 1MB 1MB
02-xx-03 SELECTION_VECTOR_REMOVER 0.010s 0.015s 1.151s 1.166s 0.000s 0.000s
0.000s 0.05% 0.05% 2 1MB 1MB
02-xx-04 FILTER 0.937s 0.956s 5.084s 5.190s 0.000s 0.000s 0.000s 0.24% 0.24%
264,412,352 2MB 2MB
Regards
Prabhakar
