tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

FbNetExternalEngine 3.0 released – execution of SQL statements inside context and performance

3 Oct 2019 .NET, Firebird

FbNetExternalEngine version 3.0 is another step towards more useful .NET execution environment inside Firebird. Version 3.0 contains two significant pieces. The first, new, is ability to execute SQL statements inside the same context (aka transaction) as the currently executing code. The other is performance; there’s always room for performance improvements.

The SQL statement execution works by adding last parameter of type IExecutionContext (from FbNetExternalEngineIntegration.dll) to the procedure or function and using Execute method. This method allows you to execute either statements returning result sets (i.e. selects) or statements not returning anything. The statement is executed inside the same context (aka transaction) as the currently executing code. More information and examples can be found in documentation.

In performance area, by careful optimization using my knowledge of how .NET runtime works and avoiding some expensive calls in Firebird, I was able to squeeze about 16% speedup on function and about 12% speedup on procedures. I still have some ideas to try, that might turn out to shave additional time from the execution time. Also, I’m planning to compare not pure execution speed, but also code execution between PSQL and FbNetExternalEngine/.NET, because in real world some significant code is usually in the body. Stay tuned.

Documentation, examples and performance numbers are available on this page.

Last but not least big thanks to SMS-Timing for sponsoring my work on the plugin.

Profile Picture Jiří Činčura is an independent developer focusing on data and business layers, language constructs, parallelism and databases. Specifically Entity Framework, asynchronous and parallel programming, cloud and Azure. He's Microsoft Most Valuable Professional and you can read his articles, guides, tips and tricks at www.tabsoverspaces.com.