https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
s
Hi @shikhar, what is "a lot of time"? One alternative is to do the lookup logic at ingestion time, such that you have a product_grp column in the base table. This will avoid the lookup altogether at query time and should make your queries faster.
j
@shikhar, can you try inverting the query, i.e. do the aggregation in the subquery, then do the lookup in the top level query. This way there are fewer lookup operations to do.
You could try reducing the number of lookup operations by first pulling out the distinct list of item_nbr values to look up, e.g. use these CTEs to create the "item_lookup" temp table to join into the main query.
Copy code
With item_nbr_list as (
  Select distinct item_nbr 
    from table name 
   where __time between '2022-04-01' and '2022-05-01'
), item_lookup as (
  Select item_nbr, 
         LOOKUP(CAST(item_nbr AS VARCHAR),'lookup_table')) as product_grp 
         from item_nbr_list
)
I don't know if a temp table is inherantly faster to access than a lookup though ...
v
why do you need the outer query? try
Copy code
select 
			day,
			
			LOOKUP(CAST(item_nbr AS VARCHAR),'lookup_table')) as product_grp,
            sum(sales)
			from 
			tablename
			where 
			__time between '2022-04-01' and '2022-05-01' group by 1,2
s
@Sergio Ferragut: i tried the ingest the same dataset with lookup logic at ingestion time and while querying the new dataset its giving response within two second’s but with using a lookup approach its taking approximately 50secs . But incase there is a change in the item product group mapping if one item moves from one product group to another then in that case i will need to do a complete historical ingestion of the data as product group would have been persisted in the dataset i want to avoid historical re-ingestion
@Vijay Narayanan without outer query the group by will group by item_nbr and not product_group
v
hmm… I tried
Copy code
select LOOKUP("Country",'store_sales_country_to_iso31661'),count(*) from "store-sales" group by 1
and see
I tried select
Copy code
select Country,count(*) from "store-sales" group by 1
and see
although I have not tried with many-one mapping in the lookup… I will check that out
confirmed this….the grouping is on the result of the lookup not the key
Copy code
select item,sum(qty) from itemtest group by 1
Copy code
select LOOKUP(item,'item_grp'),sum(qty) from itemtest group by 1
s
somehow for me when i am using below query with auto limit it is working correctly but when using without auto limit it is grouping on the item_nbr
Copy code
select
            LOOKUP(CAST(item_nbr AS VARCHAR),'lookup_table') as group
            from
            table_name
            where
            __time between '2022-04-01' and '2022-05-01' group by 1
s
Can you share the explain for each case?
Open in Slack
PreviousNext
Powered by