The main focus of version 6 is improving the performance and usability of VistaDB through changes to the low-level engine. These improvements will help out mobile and desktop apps but we looked in particular at the challenges of mobile development to identify the most important items for us to focus on.
When you take all of the changes we’ve put in to VistaDB 6 together we’ve cut execution time by nearly 40% for some common query patterns.
Asynchronous Database Operations
VistaDB 6 will natively support async/await asynchronous database operations, making it much easier to create responsive UI applications that interact with databases. Instead of writing your own code to put operations that could block on a background thread so your UI stays responsive you can just await the call to VistaDB. While the engine is working on your request your thread can go back to other UI actions.
As an embedded database, VistaDB is usually processor bound. To make async operations effective we’ve pushed all the hard work the engine does off to a background thread (and more, see below).
Multithreaded Query Processing
The VistaDB engine is being updated to use multiple threads to process queries where the overall query can be broken into multiple stages that can be processed independently. For example, subqueries will be run on their own threads and then merged with the outer query to significantly improve subquery performance. Even queries that don’t break down into stages will be run on a separate query from the calling application to support async processing and enable a modest amount of concurrent work with Data Readers and ORM’s.
We’re incorporating a new query governor to make sure the engine won’t use too many threads and overwhelm systems with few cores or a large number of concurrent operations in the same process.
Better Scalability with Multiple Simultaneous Queries
We’ve changed the core implementation of VistaDB to significantly reduce the number of objects created (and destroyed) when doing large queries over a large number of rows. Previously, these ‘firehose’ queries tended to tax the .NET garbage collector, leading to performance falloff if two or more were run concurrently that effectively wiped out the advantage of running parallel queries.
With the new approach several firehose queries (up to the number of cores available) can be run at the same time producing a higher number of total rows per second retrieved. This change and our new locking improvements later in the post are the “scalability” changes to VistaDB - aimed at improving the common scenarios of multiple operations happening at the same time. The exact improvement depends on the number of columns in the table(s) involved in the queries and some other factors but it’s a notable improvement from prior versions.
Faster Locks for Single Application Usage
Previously, VistaDB used file locks when running in any mode that allowed multiple writers. For version 6 VistaDB will use in-memory locks whenever possible and fall back to file locks only when multiple processes are accessing the same database (and can modify it). This is dramatically faster and will narrow the performance gap between exclusive mode and non-exclusive read/write mode, particularly for the very common scenario of a single process accessing a database.
VistaDB will automatically detect if a second process is attempting to access the same database and switch back to file system locks if required, so this feature doesn’t require any changes to the application.
We’re working hard to ship VistaDB 6 as soon as feasible - but not until we’re sure we’ve hit the performance and feature goals above and the engine has demonstrated it’s up to the same reliability standard as VistaDB 5. This has already taken longer than we’ve intended, so if you have active Upgrade Assurance for VistaDB you’ll be receiving a special thank you gift from us, a sign of our appreciation for your ongoing support.
VistaDB 5.5 has just shipped with support for Android and iOS applications via Xamarin plus the latest defect fixes and SQL language enhancements. Read more
VistaDB 5.2 is now available with new SQL Scripting methods to support enhanced SQL Server migration and source code control scenarios. We've also added a built-in export to Excel and CSV to make it easier to get ad-hoc data out of VistaDB. Read more
We’ve published a new release of VistaDB with our biggest performance improvements ever! We set out a year ago to make a big improvement in the optimizer for VistaDB and ended up not just doing that but also dramatically improving the speed of the underlying storage engine! Read more