using a lookup table in a SQL query with group by ...
# troubleshootingx
xtrntr
09/27/2021, 9:28 AMusing a lookup table in a SQL query with group by + having clause is extremely slow, it looks like this:
Copy code
# old (<1s)
SELECT user, count(*) FROM events
WHERE time BETWEEN 0 AND 31 AND location BETWEEN 1000 AND 1005
GROUP BY user HAVING count(*) > 10
# new (>10s)
SELECT user, count(*) FROM events
WHERE time BETWEEN 0 AND 31 AND location BETWEEN 1000 AND 1005 AND lookUp(...)=0
GROUP BY user HAVING count(*) > 10xtrntr
09/27/2021, 9:36 AMwithout the HAVING clause, query time is ok
xtrntr
09/27/2021, 9:41 AMwhich makes me think putting the fields in the lookup dimension table in the fact table will be better performance wise
xtrntr
09/27/2021, 9:41 AMeven if it means much more duplicated data
xtrntr
09/27/2021, 9:41 AMat least with the type of queries i’ll like to perform
r
Richard Startin
09/27/2021, 9:42 AMdenormalising the table for the sake of a common query is usually a good idea
x
xtrntr
09/27/2021, 9:42 AMim curious what are the use cases of the lookup table
r
Richard Startin
09/27/2021, 9:43 AMin any case, it would be good to see a profile of the slow query
x
xtrntr
09/27/2021, 9:44 AMbtw, the google doc links in https://docs.google.com/document/d/1InWmxbRqwcqIakzvoEWHLxtX4XR9H5L01256EbAUHV8/edit# are locked to the public (am assuming they were meant to be open)
xtrntr
09/27/2021, 9:44 AM@Richard Startin profiling as mentioned here right? https://apache-pinot.slack.com/archives/C011C9JHN7R/p1631110646161500?thread_ts=1631109529.161000&cid=C011C9JHN7R
r
Richard Startin
09/27/2021, 9:46 AMim curious what are the use cases of the lookup tableit's not always possible to denormalise, e.g. if the stream filling the lookup table can lag behind the stream populating the fact table. It may be the case that the reference data updates more frequently than the facts (e.g. fx rates vs transactions) and so on. If the lookup table is static, I would just denormalise and have fast queries.
Richard Startin
09/27/2021, 9:48 AMyes, often perf issues like this can be resolved by configuration, but getting profiles for slow queries is generally useful because it helps discover and address "unknown unknowns" - there may be low hanging fruit here, and the profile will probably find it if it's there.
Richard Startin
09/27/2021, 9:57 AM@Mayank or @Jackie might have some advice on tuning the HAVING clause later
x
xtrntr
09/27/2021, 10:18 AMthanks richard!
j
Jackie
09/27/2021, 6:48 PM@xtrntr
Does this query have small latency?
Copy code
SELECT user, count(*) FROM events
WHERE time BETWEEN 0 AND 31 AND location BETWEEN 1000 AND 1005 AND lookUp(...)=0
GROUP BY userJackie
09/27/2021, 6:52 PMUsing
lookUpJackie
09/27/2021, 6:54 PMFYI, if you have more than 10 users matching the filter, you should add an
ORDER BY count(*) DESC3 Views