About the timing of executing repetitive query statements

Follow

HelloK

• July 09, 2023 16:18

After executing a query statement for the first time, I noticed that if I immediately repeat the execution of the same statement, it executes very quickly, even for complex query statements. I believe this is not related to the query statement itself because the final execution speed remains the same, regardless of whether the query is complex or simple. So, I assume Heavy.AI is caching the results. If that's the case, how can I disable this option?

0

• 

  Candido Dessanti

  • July 18, 2023 06:36

  HI HelloK,

  HeavyDB didn't cache the results, except you explicitly ask to do via a query hint, but the first time you run a query some structures are cached. This is a brief list:

  1. The optimized plan of the query and the associated LLVM is cached, and can be used for the next execution, even if you change the value you filter in a query (just like other databases like Oracle).
  2. The data of the columns needed by the query is cached in the CPU and (if present) GPU memory, and also this can be reused in the next execution of the query.
  3. If a join occur, a hash-table is generated for the outer table, and also this is cached, so assuming that data isn't changed in subsequent queries, can be recycled for a faster execution.

  If you the result of your query cached, you need to add the hint keep_result in your select statement in this form

  SELECT /*+ keep_result */ ...
  FROM your_table
  
  WHERE ...
  GROUP BY ...

  So each subsequent run will return immediatly the cached result without use any resource of the system.
  
  We had a discussion about this in github.

  About the timing of executing repetitive query statements

  Regards,
  Candido

   

  0

  Comment actions Permalink

Please sign in to leave a comment.