CODEWITHSUNDEEP
sqlviewquery-optimizationsnowflake-cloud-data-platform
Manoj_katambli
Manoj_katambli

Reputation: 13

Why a query on VIEW is executing faster than a query on MATERIALIZED VIEW

I have created two views of a table in snowflake database with same select statement, one is a normal view and the other is a materialized view as below,

create view view1
as ( select *
     from customer
     where name ilike 'a%')

create materialized view view2
as ( select *
     from customer
     where name ilike 'a%')

Then queried the views as below,

Select *
from view1   ----normal view

Select *
from view2   -----materialized view

(suspended warehouse and resumed to remove any cache before executing above queries individually. I have repeated execution many times in same manner.)

But against expectation, Materialized view is always taking longer than normal view.

Why is this?

Upvotes: 0

Views: 1155

Answers (1)

Clark Perucho
Clark Perucho

Reputation: 466

It could be a number of things. What I would suggest is here:

  1. Ensure that the result cache is turned off
ALTER SESSION SET USE_CACHED_RESULT = FALSE
  1. Run them in a warehouse that's been turned off for hours - In my experience, restarting the virtual warehouse does not completely delete cached data. Do not run the query while the VW is off, manually turn them on first before running the query to avoid query delays to provision the warehouse.
  2. Run them and check the ff. in QUERY_HISTORY View to get a better idea of what have happened
  • PERCENTAGE_SCANNED_FROM_CACHE
  • COMPILATION_TIME
  • QUEUED_REPAIR_TIME
  • TRANSACTION_BLOCKED_TIME
  • EXECUTION_TIME - I believe this holds the actual execution time which excludes the time spent in compilation as opposed to TOTAL_ELAPSED_TIME
  • QUEUED_OVERLOAD_TIME

Here the QUERY_HISTORY documentation to get more details

You might also want to check the Query Profile, though I think the query using an MV would show a straightforward single step retrieve but would still be worth checking to compare and understand both queries.

Upvotes: 1

Related Questions