N1QL Query Performance Issue - Declines on volume of data fetch

SQL++
7

You can use partition index, max_prallelsim

Based on the output it looks like A.PatternDate, A.ExternalBookingId, A.SnapshotDate combination has single document. Is that true. If yes you can avoid ARRAY_AGG() and later UNNEST.

Make sure all the fields in PARTITION are immutable.

CREATE INDEX ix1 ON group360all(BookingStatus, PatternDate, ExternalBookingId, SnapshotDate,
BlendedEventRevenueTotal, FBRevenueTotal, AVRevTotal, OtherRevTotal, BlendedRoomnightsTotal,
BookedSqft, BlendedGuestroomRevenueTotal) PARTITION BY HASH(BookingStatus, PatternDate, ExternalBookingId, SnapshotDate);

SELECT v.PatternDate patterndate,
       ROUND(SUM(v.BlendedEventRevenueTotal),2) funcroomrev,
       ROUND(SUM(v.FBRevenueTotal),2) fnbrev,
       ROUND(SUM(v.AVRevTotal),2) avrev ,
       ROUND(SUM(v.OtherRevTotal),2) otherrev ,
       ROUND(SUM(v.BlendedEventRevenueTotal) / SUM(v.BlendedRoomnightsTotal),2) funcrevpergrprn,
       ROUND(SUM(v.BlendedEventRevenueTotal) / SUM(v.BookedSqft),2) funcrevperbkdsqft,
       ROUND(SUM(v.BlendedEventRevenueTotal) / 1000,2) funcrevpersqft,
       SUM(v.BlendedRoomnightsTotal) grprnleadvolume,
       ROUND(SUM(CASE WHEN v.BookingStatus = 'Definite' THEN v.BlendedRoomnightsTotal ELSE 0 END) / SUM(v.BlendedRoomnightsTotal),3) conversion,
       SUM(v.BlendedRoomnightsTotal) grprn,
       ROUND(SUM(v.BlendedGuestroomRevenueTotal),2) grproomrev,
       ROUND(SUM(v.BlendedGuestroomRevenueTotal) / SUM(v.BlendedRoomnightsTotal),0) grpadr
FROM ( SELECT RAW MAX ([A.SnapshotDate, {A.PatternDate, A.ExternalBookingId, A.SnapshotDate ,
                                A.BlendedEventRevenueTotal, A.FBRevenueTotal, A.AVRevTotal,
                                A.OtherRevTotal, A.BlendedRoomnightsTotal, A.BookedSqft,
                                A.BlendedGuestroomRevenueTotal, A.BookingStatus}])[1]
       FROM group360all A
       WHERE A.PatternDate BETWEEN '2013-01-01' AND '2020-12-31'
             AND A.BookingStatus IN ['Definite']
       GROUP BY A.PatternDate, A.ExternalBookingId, A.SnapshotDate ) AS v
GROUP BY v.PatternDate
ORDER BY v.PatternDate;
1 Like