{"id":10796,"date":"2023-02-16T16:06:15","date_gmt":"2023-02-16T09:06:15","guid":{"rendered":"https:\/\/gcloudvn.com\/?p=10796"},"modified":"2023-03-20T16:58:36","modified_gmt":"2023-03-20T09:58:36","slug":"understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph","status":"publish","type":"post","link":"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/","title":{"rendered":"Understand and optimize your BigQuery analytics queries using the query execution graph"},"content":{"rendered":"<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\"><a href=\"https:\/\/gcloudvn.com\/en\/bigquery\/\">BigQuery<\/a> provides strong query performance, but it is also a complex distributed system with many internal and external factors that can affect query speed. When your queries are running slower than expected or slower than previous runs, understanding what went wrong can be challenging.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">The query execution graph provides an intuitive interface for inspecting query execution details. By using it, you can review the query plan information in graphical format for any query, whether running or completed.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">You can also use the query execution graph to get performance insights for queries. Performance insights provide best-effort suggestions to help you improve query performance. Since query performance is multi-faceted, performance insights might only provide a partial picture of the overall query performance.<\/span><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Do_thi_thuc_hien_Execution_Graph\" >Execution Graph<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Thong_tin_chi_tiet_ve_hieu_suat_truy_van\" >Query performance insights<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Slot_contention\" >Slot contention<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Insufficient_shuffle_quota\" >Insufficient shuffle quota<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Thay_doi_quy_mo_dau_vao_du_lieu\" >Data input scale change<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/gcloudvn.com\/en\/kienthuc\/understand-and-optimize-your-bigquery-analytics-queries-using-the-query-execution-graph\/#Buoc_tiep_theo\" >What\u2019s next?<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Do_thi_thuc_hien_Execution_Graph\"><\/span><b>Execution Graph<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">When <\/span><span style=\"font-weight: 400;\">BigQuery<\/span><span style=\"font-weight: 400;\"> executes a query job, it converts the declarative SQL statement into a graph of execution, broken up into a series of query stages, which themselves are composed of more granular sets of execution steps. The query execution graph provides a visual representation of the execution stages and shows the corresponding metrics. Not all stages are made equal. Some are more expensive and time consuming than others. The execution graph provides toggles for highlighting critical stages, which makes it easier to spot the potential performance bottlenecks in the query.<\/span><\/p>\n<p style=\"text-align: justify;\"><b><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-10860 size-full\" src=\"https:\/\/gcloudvn.com\/wp-content\/uploads\/2023\/02\/Screenshot_67.png\" alt=\"How to understand and optimize Google BigQuery analytics queries through query execution graph 1\" width=\"600\" height=\"346\" srcset=\"https:\/\/gcloudvn.com\/wp-content\/uploads\/2023\/02\/Screenshot_67.png 600w, https:\/\/gcloudvn.com\/wp-content\/uploads\/2023\/02\/Screenshot_67-300x173.png 300w, https:\/\/gcloudvn.com\/wp-content\/uploads\/2023\/02\/Screenshot_67-18x10.png 18w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><\/b><\/p>\n<h2 style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Thong_tin_chi_tiet_ve_hieu_suat_truy_van\"><\/span><b>Query performance insights<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">In addition to the detailed execution graph BigQuery also provides specific insights on possible factors that might be slowing query performance.<\/span><\/p>\n<h2 class=\"MnozTc-Bz112c MnozTc-Bz112c-OWXEXe-c5RTEf\" style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Slot_contention\"><\/span>Slot contention<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">When you run a query, BigQuery attempts to break up the work needed by your query into tasks. A task is a single slice of data that is input into and output from a stage. A single slot picks up a task and executes that slice of data for the stage. Ideally, BigQuery slots execute tasks in parallel to achieve high performance. Slot contention occurs when your query has many tasks ready for slots to start executing, but BigQuery can't get enough available slots to execute them.<\/span><\/p>\n<h2 class=\"MnozTc-Bz112c MnozTc-Bz112c-OWXEXe-II5mzb\" style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Insufficient_shuffle_quota\"><\/span>Insufficient shuffle quota<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">Before running your query, BigQuery breaks up your query's logic into stages. BigQuery slots execute the tasks for each stage. When a slot completes the execution of a stage's tasks, it stores the intermediate results in shuffle. Subsequent stages in your query read data from shuffle to continue your query's execution. Insufficient shuffle quota occurs when you have more data that needs to get written to shuffle than you have shuffle capacity.<\/span><\/p>\n<h2 style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Thay_doi_quy_mo_dau_vao_du_lieu\"><\/span><b>Data input scale change<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">Getting this performance insight indicates that your query is reading at least <\/span><span style=\"font-weight: 400;\">50% more<\/span><span style=\"font-weight: 400;\"> data for a given input table than the last time you ran the query and hence experiencing query slowness. You can use table change history to see if the size of any of the tables used in the query has recently increased.<\/span><\/p>\n<h2 style=\"text-align: justify;\"><span class=\"ez-toc-section\" id=\"Buoc_tiep_theo\"><\/span><b>What\u2019s next?<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400;\">Google will continue to work on improving chart visibility. They are currently working on adding additional metrics for each step, and adding more performance insights will make query diagnostics significantly easier. Google is just getting started!<\/span><\/p>\n<p style=\"text-align: justify;\"><em>Contact Gimasys for advice on a transformation strategy that is right for your business situation and to experience the free Google Cloud Platform service:<\/em><\/p>\n<ul style=\"text-align: justify;\">\n<li><strong>Hotline: Hanoi: 0987 682 505 \u2013 Ho Chi Minh: 0974 417 099<\/strong><\/li>\n<li><strong>Email: gcp@gimasys.com<\/strong><\/li>\n<\/ul>\n<p style=\"text-align: right;\"><strong>Source: <\/strong>Gimasys<\/p>","protected":false},"excerpt":{"rendered":"<p>BigQuery provides strong query performance, but it is also a complex distributed system with many internal and external factors that can affect query speed. When the\u2026<\/p>","protected":false},"author":2,"featured_media":10861,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1,135],"tags":[],"class_list":["post-10796","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-kienthuc","category-google-cloud-platform","entry","has-media"],"_links":{"self":[{"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/posts\/10796","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/comments?post=10796"}],"version-history":[{"count":0,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/posts\/10796\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/media\/10861"}],"wp:attachment":[{"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/media?parent=10796"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/categories?post=10796"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gcloudvn.com\/en\/wp-json\/wp\/v2\/tags?post=10796"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}