Hello Prabhakar, Since the 1.20, the web UI provided a View button in the Profile page, It can be re-play the execution phase, better for the diagnostic.
So, could you please post this profile json file? please add it as the attachment of email. > On Jul 8, 2022, at 18:12, Prabhakar Bhosale <[email protected]> wrote: > > 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 >
