Hi Team wanted to understand the role of broker in...
# generala
Abhijeet Kushe
12/19/2023, 3:35 PMHi Team wanted to understand the role of broker in queries.I have read the broker does aggregation or merging.But can I know more details about it for eg.DistinctCount will that be done on broker or server.Is there a blog of video which talks more about it ?
m
Mayank
12/21/2023, 3:41 AM
Broker does the final gather of results. In cases of distinctcount, and other non-additive aggregation functions, servers can only send raw data back to broker to do compute those correctly (unlike sum/min/max/etc which can be computed on server side).
a
Abhijeet Kushe
12/21/2023, 1:01 PMThanks Mayank so this does happen on the broker side.Is there a way to tune broker to do it more in an optimized fashion ? As we noticed that distinctcount makes the query take 2.5 seconds and count only takes less than 200ms.This account does have 9million rows and there is groupby clause as well.Which probably means that grouped result is returned back to the broker
r
robert zych
12/21/2023, 2:42 PMWould an approximation be sufficient for your use case?
a
Abhijeet Kushe
12/21/2023, 2:43 PMNo we need accurate counts
Abhijeet Kushe
12/21/2023, 6:17 PMAnother question I would like to know is about query optimizations we have multiple group by fields is there an order in which the fields are supposed to be to perform better for eg low cardinality at the start and high cardinality at the end ?
Abhijeet Kushe
12/21/2023, 8:34 PMI also tried adding derived columns on the multiple group by columns and on the concatenated distinctcount field.It slightly improved but not as much.Is there a way in which we can give hints to dictionary encoded index which can speed up this. @Mayank any help would be appreciated
Copy code
SELECT distinctcount(txnId_contactId2)) as 'txnId,contactId_count', sum(totalOrders) as totalOrderNum_sum, sumprecision(totalOrderAmt, 19) as totalOrderAmt_sum, activityId, activityType, currency FROM events WHERE accountId = 1234 AND flowId = 'd13c223f-asdfa-asdf-77b89ab08f82' AND recordType = 'event' AND softDelete = 'null' group by currencyActivityTypeActivityId LIMIT 0, 1000 option(timeoutMs=20000)