How to optimize query performance for counting rows in a materialized source in a streaming engine?
I have created a materialized source and a materialized view in a streaming engine. When I perform a count on the materialized view, it takes 2 minutes to complete for a table with about 600,000 rows. How can I optimize the query performance for counting rows in a materialized source?
jie
Asked on May 22, 2022
-
Define the materialized view directly as
create materialized view source_mv1 as select count(*) from s1;
to fully leverage the capability of the streaming engine. -
Consider using non-materialized sources for testing the performance of
count(*)
queries. -
Try the release mode to speed up the query by turning off some traces and logs.
-
Ensure that the materialized source is optimized for performance, as it should run faster than the reported 5000 row/s for a small table of 600,000 rows.