• Where clause optimization : filter from another partitioned table

  Where clause optimization : filter from another partitioned table

  Posted by calvin639 on August 21, 2024 at 3:36 pm

  Hello all,

  I noticed a huge performance hit when trying to select from a large partitioned table if I have a filter that takes input from another partitioned table. But if broken up it is still very fast.

  So the result from tbl2 is being fed into where clause of tbl1

  \ts select from tbl1 where date within(2022.04.02;2022.07.20), sym=1, side in12, broker_reason<>test, not order_id in  (exec order_id from select order_id from tbl2 where date within(2022.04.02;2022.07.20), sym=1, trans_type in23, status in1)
  

  142754 6247984

  Individually these select statements are very quick and when I save the IDs from tbl2 and then run the select statement. Same result with both.

  \ts {ids:(exec order_id from select order_id from tbl2 where date within(2022.04.02;2022.07.20), sym=1, trans_type in23, status in1);select from tbl1 where date within(2022.04.02;2022.07.20), sym=1, side in12, broker_reason<>test, not order_id in ids}[]"
  

  3894 6248176

  Is it because the tbl1 select gets executed up until the order_id filter, then because it is holding a lot of memory while executing the second select statement it comes to a slowdown?

  Any other theories?

  kdb version = 4 (.z.K=4f)

  replied 8 months, 3 weeks ago 0 Member · 0 Replies
• 0 Replies

Log In to Reply