Is there any performance impact between CREATE VIEW vs CREATE TABLE vs query the parquet file directly

[sahuguet]

Is there any value (in terms of performance) in bringing the data to the database using a CREATE TABLE transaction vs just using a CREATE VIEW or running the query directly on the parquet file?

[Mytherin]

The performance should be relatively comparable, especially if PRAGMA enable_object_cache is enabled which allows Parquet metadata to be cached in between reads of the same Parquet file.

[sahuguet]

Based on my very limited experiments, from fastest to slowest:

• query with empty read-only DB on raw parquet files
• query with in-memory DB on raw parquet files
• query against view pointing to parquet files

[pankajp]

I obtained significant (2x) performance gains in a recursive cte on multiple parquet files replacing views over the parquet files with materialized tables for some of the views involved. I suspect it might be because of multiple scans over the views.