Odd Athena Query Performance Issue
Hello, I often use CTE's or views to create faux parameters as a way to improve reusability of code. For example, if I had a bunch of CTE's or subqueries (or different queries/views altogether) that all used a common value in a where clause, I would simply create a view/CTE containing the values and select the values from a subquery wherever needed. This typically has no meaningful impact on performance; however, it seems to degrade execution speed quite a bit in Athena and I'm not sure why this would be the case. What is the difference between: 'select * from table where foo = 'bar' versus 'with param as (select 'bar' as p) select * from table where foo = (select p from param)'? When querying a substantial amount of data, the second pattern makes the query speed untenable. These tables are partitioned to the hour also.
Technically it should not effect the performance. Can you please provide Athena query ID's and AWS region for us to check the query plan and see where the delay is ? Feel free to open a support ticket with Athena support team with query ID's and we can provide you more insights.
Can I use the ID of my saved query to start query execution in Athena SDK?asked a month ago
In-place query of S3 data without provisioning DB or creating tablesasked 4 months ago
Save / backup Athena viewAccepted Answerasked 2 years ago
Odd Athena Query Performance Issueasked 2 months ago
Athena views can't include Athena table partitionsasked 5 days ago
Best way to overcome HIVE_PARTITION_SCHEMA_MISMATCH error in Athena while preserving structure of structs?asked 3 months ago
Athena - Query exhausted resources at this scale factorasked 21 days ago
Create a Glue table (Presto/Athena view) for analyzing ALB logs using CloudFormation or Terraformasked 6 months ago
I cannot use current_date + interval in Athena boto3 query in LambdaAccepted Answerasked 4 months ago
Quicksight - Create data set (Athena) - Table query times out when trying to create data setasked a month ago