BigQuery History-based Optimizations Boost Query Performance

Google Cloud created BigQuery history-based optimizations, a novel query optimization method that uses past executions of related queries to find and implement further query execution enhancements

[{"selector":"#anim-a9a3596d-250b-4edd-8a31-d41cc01fd161 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate3d(12.514648210040342%, 0, 0)","translate3d(0%, 0, 0)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-52668e1f-1e06-4b63-b0da-4af258436d2f","keyframes":{"opacity":[0,1]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-96e3589f-19f6-45fd-9fdd-fbb7e629802b","keyframes":{"transform":["scale(0.3333333333333333)","scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}]

Google observed cases where query speed on customer workloads increased by up to 100x during the public preview of BigQuery history-based optimization

[{"selector":"#anim-ce66fd37-34d5-40e0-9d31-7a2c3fab6a4c [data-leaf-element=\"true\"]","keyframes":{"transform":["translate3d(12.514648210040342%, 0, 0)","translate3d(0%, 0, 0)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-bdb2b628-73f2-40bc-87ea-8ed0bf8c1b79","keyframes":{"opacity":[1,1]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-33765872-ed5f-47e6-86da-77be3cc024b9","keyframes":{"transform":["scale(3)","scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}]

Google observed cases where query speed on customer workloads increased by up to 100x during the public preview of BigQuery history-based optimization

[{"selector":"#anim-245f9efa-f365-4dc9-bf53-e7f4de45b7e8 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate(0%, 0%) scale(1.5)","translate(0%, 0%) scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}] [{"selector":"#anim-121354c4-eb73-484f-a060-8478c45d1f25","keyframes":{"opacity":[1,1]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-74daa73d-4d0c-4456-8f86-72be4d3525ba","keyframes":{"transform":["scale(3)","scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}]

BigQuery history-based optimization is more than just a static collection of four new improvements; they are a platform for continuous investment in BigQuery’s optimization capabilities

[{"selector":"#anim-24b792d9-f409-456d-a2d1-f13766c81907 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate3d(12.514648210040342%, 0, 0)","translate3d(0%, 0, 0)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-c6554093-bbc7-4f10-a02a-75c7a41f102e","keyframes":{"transform":["translate3d(-115.2381%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-9b179de2-4072-4433-ae29-9082a73dd140","keyframes":{"opacity":[0,1]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"both"}] [{"selector":"#anim-342b7ba8-ca99-4743-a805-c8309a722b9d","keyframes":{"transform":["scale(0.15)","scale(1)"]},"delay":0,"duration":600,"easing":"cubic-bezier(0.4, 0.4, 0.0, 1)","fill":"forwards"}]

You have the ability to examine which BigQuery history-based optimization was used (if any) in INFORMATION_SCHEMA and comprehend how they affected your jobs

[{"selector":"#anim-12478bbd-16ea-404a-8ccc-f1037b33c851 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate(0%, 0%) scale(1.5)","translate(0%, 0%) scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}] [{"selector":"#anim-0971789a-31ce-473d-b1fe-b74c53323be6","keyframes":{"transform":["rotate(-540deg) scale(0.1)","none"],"opacity":[0,1]},"delay":0,"duration":1000,"fill":"both","iterations":1}]

In certain situations, BigQuery may determine that it could be more effective to switch the two sides of a join, lowering the resources used to do that join operation

[{"selector":"#anim-ce0065cf-4f63-45c2-ba31-17370f363f20 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate3d(-12.514648210040342%, 0, 0)","translate3d(0%, 0, 0)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-c870a1e6-34a7-4b76-892f-628014acd15a","keyframes":{"transform":["rotate(-540deg) scale(0.1)","none"],"opacity":[0,1]},"delay":0,"duration":1000,"fill":"both","iterations":1}]

BigQuery may decide to perform the selective join earlier if it comes after less selective operations like aggregations or other joins

[{"selector":"#anim-69c8b7d1-7504-4776-98c4-076880980757 [data-leaf-element=\"true\"]","keyframes":{"transform":["translate3d(12.514648210040342%, 0, 0)","translate3d(0%, 0, 0)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"both"}] [{"selector":"#anim-51aa7086-218b-4107-bdb2-a7d526e5103c","keyframes":{"transform":["translate3d(-115.55555%, 0px, 0)","translate3d(0px, 0px, 0)"]},"delay":0,"duration":1000,"easing":"cubic-bezier(.2, 0, .8, 1)","fill":"both"}] [{"selector":"#anim-0d060b01-4291-4607-ab88-f812231ff08d","keyframes":{"transform":["rotateZ(-180deg)","rotateZ(0deg)"]},"delay":0,"duration":1000,"easing":"cubic-bezier(.2, 0, .5, 1)","fill":"forwards"}]

For more details visit Govindhtech.com

[{"selector":"#anim-851ce15b-af21-40d4-8668-040c6e98a3bd [data-leaf-element=\"true\"]","keyframes":{"transform":["translate(0%, 0%) scale(1.5)","translate(0%, 0%) scale(1)"]},"delay":0,"duration":2000,"easing":"cubic-bezier(.3,0,.55,1)","fill":"forwards"}]