Updating your Codebase with FBQL

For the third year in a row, Acumatica held a virtual developer conference. Here are my notes from the Updating your Codebase with FBQL session.

I created separate blog posts for each session.

Day 1

09:00am – Welcome & Kick-off – Ajoy & Mark

09:10am – xRP Framework Fundamentals & Best Practices – Sergey Marenich

10:20am – Application Solution Architecture – Dmitry Naumov

10:50am – Source Control & Automation for Acumatica Developers – Brendan Hennelly

11:30am – Reporting Tools, Tips, and Best Practices – Mikhail Reysev

12:40pm – Implementing Multi-Currency Support for Custom Solutions – Joshua van Hoesen

01:10pm – Unit Testing Framework – Andrew Boulanov

Day 2

09:00am – Database Schema Overview – Dmitry Naumov

09:30am – Application Certification – Ruslan Devyatko

10:10am – Modern Developer Practices – Stan Lesin

11:20am – Updating your Codebase with FBQL – Sharif Ahammad (this post)

11:50am – What to Expect from Acumatica’s Developer Community – Joe Jacob & Mark Franks

12:00pm – Developer Roadmap – Mike Chtchelkonogov

Now starting "Updating your Codebase with FBQL" with Yuriy Zaletskyy @zaletskiy from https://t.co/fjiP5QPsIg #CloudxRPSummit pic.twitter.com/kp5XldJNyN
— Tim Rodman (@TimRodman) June 20, 2019

FBQL is awesome, but it is not backward compatible #CloudxRPSummit pic.twitter.com/K54zAjSJPd
— Tim Rodman (@TimRodman) June 20, 2019

BQL vs. FBQL #CloudxRPSummit pic.twitter.com/VyihXtZmhM
— Tim Rodman (@TimRodman) June 20, 2019

Less than 1% of the code files in Acumatica are using FBQL. But it does exist in the code, showing that it is gradually being introduced, like slowly modernizing a bridge. #CloudxRPSummit pic.twitter.com/ifnVKVk9Dn
— Tim Rodman (@TimRodman) June 20, 2019

Do this before proceeding with FBQL #CloudxRPSummit pic.twitter.com/exhNwUPgtp
— Tim Rodman (@TimRodman) June 20, 2019

Thankfully, FBQL doesn't have Where2, but you have to get used to it and retrain your brain #CloudxRPSummit pic.twitter.com/KnjOOGxuTx
— Tim Rodman (@TimRodman) June 20, 2019

Junior developers don't have to "unlearn" BQL. Experienced developers like @zaletskiy have to unlearn. #CloudxRPSummit pic.twitter.com/JsZRseFqZx
— Tim Rodman (@TimRodman) June 20, 2019

FBQL supports HAVING and aggregations #CloudxRPSummit pic.twitter.com/NqZi4ItMsM
— Tim Rodman (@TimRodman) June 20, 2019

Combination of "Use" and "AsString" allows you to mix BQL with FBQL. This code will still work when Student1 upgraded to FBQL so no code changes needed. #CloudxRPSummit pic.twitter.com/0WMH6Hwz8w
— Tim Rodman (@TimRodman) June 20, 2019

This is what HAVING aggregate looks like in FBQL #CloudxRPSummit pic.twitter.com/SKj0Op07B0
— Tim Rodman (@TimRodman) June 20, 2019

2 types of Data Views used in FBQL #CloudxRPSummit pic.twitter.com/Xt2tj6vB26
— Tim Rodman (@TimRodman) June 20, 2019

Which way is more convenient? The Acumatica team always uses #2 #CloudxRPSummit pic.twitter.com/GRJGWg4K43
— Tim Rodman (@TimRodman) June 20, 2019

Pointing out SearchFor in FBQL #CloudxRPSummit pic.twitter.com/h6fY0YEgX6
— Tim Rodman (@TimRodman) June 20, 2019

You can use DAC fields declared in FBQL in traditional BQL queries #CloudxRPSummit pic.twitter.com/p7w0dsKgrM
— Tim Rodman (@TimRodman) June 20, 2019

#CloudxRPSummit pic.twitter.com/7ao2QsfKNJ
— Tim Rodman (@TimRodman) June 20, 2019

Notes about Parameters in FBQL #CloudxRPSummit pic.twitter.com/c3c0xmVF2n
— Tim Rodman (@TimRodman) June 20, 2019

Looking at how similar FBQL is to SQL. Lines almost correspond 1-1. #CloudxRPSummit pic.twitter.com/A9eAC9aLYz
— Tim Rodman (@TimRodman) June 20, 2019

Interesting question. Which one is faster? BQL or FBQL? #CloudxRPSummit pic.twitter.com/iZsVSb0EGo
— Tim Rodman (@TimRodman) June 20, 2019

BQL may be faster because every FBQL statement is converted into BQL so that conversion may slow it down. #CloudxRPSummit pic.twitter.com/7nOXEyumoZ
— Tim Rodman (@TimRodman) June 20, 2019

But @zaletskiy wasn't satisfied with theory and decided to do a real-world test. #CloudxRPSummit pic.twitter.com/EnKq8n2Jig
— Tim Rodman (@TimRodman) June 20, 2019

The results from the test. # of Executions on X Axis and Ratio on Y Axis. With caching, no clear winner. Without caching, BQL is about 3% faster than FBQL. But who cares about 3%? Microsoft sure didn't with Windows 🙂 #CloudxRPSummit pic.twitter.com/SVUgLSOhap
— Tim Rodman (@TimRodman) June 20, 2019

Proof that caching helps with performance for both BQL and FBQL #CloudxRPSummit pic.twitter.com/tNeW2HxyLR
— Tim Rodman (@TimRodman) June 20, 2019

Another test with searching #CloudxRPSummit pic.twitter.com/ZsoH5BEkcB
— Tim Rodman (@TimRodman) June 20, 2019

Uncached persist BQL is faster, but not on a small dataset. With cached persist, it's possible for FBQL to be faster. #CloudxRPSummit pic.twitter.com/3mAnRVTeKM
— Tim Rodman (@TimRodman) June 20, 2019

Summary of findings from the testing and wrap-up from the session. Thanks @zaletskiy, especially for all that testing data #CloudxRPSummit pic.twitter.com/YcOzQiA5Cx
— Tim Rodman (@TimRodman) June 20, 2019