<@U01UL37KF28> Moving to troubleshooting: <https:/...
# troubleshootingm
Mayank
05/02/2021, 4:15 PM
@kauts shukla Moving to troubleshooting: https://apache-pinot.slack.com/archives/CDRCA57FC/p1619953930084100
Mayank
05/02/2021, 4:15 PM
Given that the query is inherently expensive, perhaps you can share a bit about the use case so we can see if the query itself can be modified
k
kauts shukla
05/02/2021, 4:17 PM@Mayank We have two types of queries
Copy code
select userid,eventlabel,sessionid, MIN(timestampist) as mint, MAX(timestampist) as maxt, (MAX(timestampist) - MIN(timestampist)) as diff_time from default.click_stream where sessionid !='null' and timestampist between 1615833000000 and 1616225312000 group by userid,eventlabel,sessionidkauts shukla
05/02/2021, 4:17 PMselect userid, v1, max(timestamp) as timestampist from clickstream where eventlabel=‘app_classroom_video_seen’ and k1 = ‘video_id’ and timestampist between 1608057000000 and 1615965662009 group by userid, v1
kauts shukla
05/02/2021, 4:19 PMThese queries are taking too much time process every time.. Its taking more than 60 Sec and got timed out Total docs as of now is : 310767668
m
Mayank
05/02/2021, 4:29 PM
When I say use case I mean what is the end goal, is this a dashboard, how it is going to be used etc
k
kauts shukla
05/02/2021, 4:32 PMIt’s going to be a dashboard and its like 500 around concurrent users may use it in parallel. Every time a users opens this dashboard it will fire these queries.
m
Mayank
05/02/2021, 4:33 PM
So if there are 500M records selected in query with 1minutr granularity there will be millions of groups (at least)
Mayank
05/02/2021, 4:34 PM
Is the dashboard going to show all that?
k
kauts shukla
05/02/2021, 4:34 PMAs you suggested i made it to 1 day level granuality. As i can live with 1 day granuality.
m
Mayank
05/02/2021, 4:35 PM
Can you do offline also?
Mayank
05/02/2021, 4:35 PM
For example real-time for 1 day and preaggregate in offline with star tree for 29’days
k
kauts shukla
05/02/2021, 4:36 PMHow its gonna help here ?
m
Mayank
05/02/2021, 4:37 PM
Offline preaggregation with star tree will be very fast
k
kauts shukla
05/02/2021, 4:38 PMI have to maintain 2 tables one is realtime with one day granulaity and 1 with 29 days ?
m
Mayank
05/02/2021, 4:38 PM
Nope
k
kauts shukla
05/02/2021, 4:38 PMoffline with 29
m
Mayank
05/02/2021, 4:38 PM
Single table
k
kauts shukla
05/02/2021, 4:39 PMOk, what are the changes i should made it ?
m
Mayank
05/02/2021, 4:40 PM
Single table with offline and real-time component. Generate star tree in offline and push
Mayank
05/02/2021, 5:00 PM
Do you have an estimate of how many unique sessions per day? And how many unique groups you expect?
Mayank
05/02/2021, 5:11 PM
If we can somehow control the number of unique groups a query has to process that would help
k
kauts shukla
05/02/2021, 5:57 PMI’ll check and let you know