Very slow query

[Johan Alpne]

Hello team,

We are running a query for one of our customers and it takes more than 25 minutes for a single day. We have a deadline of 25 minutes so we're not sure how long it would take to finish:

SELECT customer.descriptive_name, customer.time_zone, customer.tracking_url_template, campaign.advertising_channel_type, campaign.advertising_channel_sub_type, campaign.id, campaign.name, campaign.tracking_url_template, campaign.url_custom_parameters, campaign.final_url_suffix, ad_group.name, ad_group.id, ad_group.type, ad_group.tracking_url_template, ad_group.final_url_suffix, ad_group_ad.ad.id, ad_group_ad.ad.type, ad_group_ad.ad.text_ad.headline, ad_group_ad.ad.expanded_text_ad.headline_part1, ad_group_ad.ad.expanded_text_ad.headline_part2, ad_group_ad.ad.expanded_text_ad.headline_part3, ad_group_ad.ad.expanded_text_ad.description, ad_group_ad.ad.text_ad.description1, ad_group_ad.ad.text_ad.description2, ad_group_ad.ad.expanded_text_ad.description2, ad_group_ad.ad.final_urls, ad_group_ad.ad.final_mobile_urls, ad_group_ad.ad.tracking_url_template, ad_group_ad.ad.final_url_suffix, ad_group_ad.ad.name, ad_group_ad.ad.responsive_search_ad.headlines, ad_group_ad.ad.app_ad.headlines, ad_group_ad.ad.responsive_display_ad.headlines, ad_group_ad.ad.image_ad.image_url, ad_group_ad.ad.image_ad.pixel_height, ad_group_ad.ad.image_ad.pixel_width, ad_group_ad.ad.image_ad.preview_image_url, ad_group_ad.ad.url_custom_parameters, ad_group_ad.ad.expanded_dynamic_search_ad.description, ad_group_ad.ad.expanded_dynamic_search_ad.description2, ad_group_ad.ad.display_url, ad_group_ad.ad.expanded_text_ad.path1, ad_group_ad.ad.expanded_text_ad.path2, ad_group_ad.ad.responsive_search_ad.descriptions, ad_group_ad.ad.responsive_search_ad.path1, ad_group_ad.ad.responsive_search_ad.path2, ad_group_ad.ad.responsive_display_ad.long_headline, ad_group_ad.ad.responsive_display_ad.descriptions, ad_group_ad.ad.responsive_display_ad.call_to_action_text, ad_group_ad.ad.responsive_display_ad.control_spec.enable_asset_enhancements, ad_group_ad.ad.responsive_display_ad.control_spec.enable_autogen_video, ad_group_ad.ad.display_upload_ad.display_upload_product_type, segments.date, segments.ad_network_type, metrics.impressions, metrics.video_quartile_p25_rate, metrics.video_quartile_p50_rate, metrics.video_quartile_p75_rate, metrics.video_quartile_p100_rate, campaign.labels, ad_group.labels, metrics.active_view_impressions, metrics.active_view_measurable_cost_micros, metrics.active_view_measurable_impressions, metrics.clicks, metrics.cost_micros, metrics.engagements, metrics.gmail_forwards, metrics.gmail_saves, metrics.gmail_secondary_clicks, metrics.impressions, metrics.impressions, metrics.interactions, metrics.video_views, metrics.top_impression_percentage, metrics.absolute_top_impression_percentage, metrics.interaction_event_types, customer.currency_code FROM ad_group_ad WHERE segments.date BETWEEN '2023-09-20' AND '2023-09-20'

We scaled the query down a bit by removing Video Quartile Rates, Labels and Network type. I would think the only meaningful speedup would be to remove the segmenting field (ad_network_type) but it did not help to only remove that. So we have this:

SELECT customer.descriptive_name, customer.time_zone, customer.tracking_url_template, campaign.advertising_channel_type, campaign.advertising_channel_sub_type, campaign.id, campaign.name, campaign.tracking_url_template, campaign.url_custom_parameters, campaign.final_url_suffix, ad_group.name, ad_group.id, ad_group.type, ad_group.tracking_url_template, ad_group.final_url_suffix, ad_group_ad.ad.id, ad_group_ad.ad.type, ad_group_ad.ad.text_ad.headline, ad_group_ad.ad.expanded_text_ad.headline_part1, ad_group_ad.ad.expanded_text_ad.headline_part2, ad_group_ad.ad.expanded_text_ad.headline_part3, ad_group_ad.ad.expanded_text_ad.description, ad_group_ad.ad.text_ad.description1, ad_group_ad.ad.text_ad.description2, ad_group_ad.ad.expanded_text_ad.description2, ad_group_ad.ad.final_urls, ad_group_ad.ad.final_mobile_urls, ad_group_ad.ad.tracking_url_template, ad_group_ad.ad.final_url_suffix, ad_group_ad.ad.name, ad_group_ad.ad.responsive_search_ad.headlines, ad_group_ad.ad.app_ad.headlines, ad_group_ad.ad.responsive_display_ad.headlines, ad_group_ad.ad.image_ad.image_url, ad_group_ad.ad.image_ad.pixel_height, ad_group_ad.ad.image_ad.pixel_width, ad_group_ad.ad.image_ad.preview_image_url, ad_group_ad.ad.url_custom_parameters, ad_group_ad.ad.expanded_dynamic_search_ad.description, ad_group_ad.ad.expanded_dynamic_search_ad.description2, ad_group_ad.ad.display_url, ad_group_ad.ad.expanded_text_ad.path1, ad_group_ad.ad.expanded_text_ad.path2, ad_group_ad.ad.responsive_search_ad.descriptions, ad_group_ad.ad.responsive_search_ad.path1, ad_group_ad.ad.responsive_search_ad.path2, ad_group_ad.ad.responsive_display_ad.long_headline, ad_group_ad.ad.responsive_display_ad.descriptions, ad_group_ad.ad.responsive_display_ad.call_to_action_text, ad_group_ad.ad.responsive_display_ad.control_spec.enable_asset_enhancements, ad_group_ad.ad.responsive_display_ad.control_spec.enable_autogen_video, ad_group_ad.ad.display_upload_ad.display_upload_product_type, segments.date, metrics.active_view_impressions, metrics.active_view_measurable_cost_micros, metrics.active_view_measurable_impressions, metrics.clicks, metrics.cost_micros, metrics.engagements, metrics.gmail_forwards, metrics.gmail_saves, metrics.gmail_secondary_clicks, metrics.impressions, metrics.impressions, metrics.interactions, metrics.video_views, metrics.top_impression_percentage, metrics.absolute_top_impression_percentage, metrics.interaction_event_types, customer.currency_code FROM ad_group_ad WHERE segments.date BETWEEN '2023-09-20' AND '2023-09-20'

Which at least finishes but it takes 10+ minutes to fetch ~9000 rows which seems really slow as we can download over 1 million rows for some queries in less than 3 minutes. Are there any known slowdowns on certain fields or resources? If not, we would like help to understand why it's so slow for this customer.

We are using SEARCH STREAM to get the data.

Best regards,

Johan Alpne