VistaDB 6
VistaDB / What's New - Version 6.3 / Getting Started - What's New - Version 5.0
In This Topic
    Getting Started - What's New - Version 5.0
    In This Topic

    New Features In This Release

    Improved Query Optimization And Other Performance Improvements

    We have done a deep dive into the way VistaDB processes queries and implemented a new approach to query optimization.  The new optimization emphasizes:

    Common simple queries are frequently 25% faster than VistaDB 4.3 with other queries improving up to 75%.  In most cases, no changes to client applications are necessary to take advantage of the performance improvements.

    Multi-Column Indexes

    Previously, VistaDB used multi-column indexes only for uniqueness constraints, it couldn't employ them as part of a filter or join operation.  Now VistaDB will examine the WHERE clause and JOIN conditions and attempt to find a matching index which covers all of the necessary columns in the table. 

    ORDER BY Improvements

    Queries which specify an ORDER BY that can be supported by an available index and do not require other filtering can now take advantage of that index to return results in that order as a "live" query rather than having to spool all results and sort them before returning any results (as a non-live query). This allows such queries, when limited by TOP (or OFFSET/FETCH) to, process fewer rows (instead of all of them) and complete efficiently even for very large tables. When other filtering (e.g. column comparisons in WHERE or ON clauses) is specified such filtering will typically take priority when selecting the "active" index order and require spooling for a final sort if the ORDER BY is not consistent with the same "active" index.

    This improvement primarily affects queries such as SELECT TOP 100 * FROM [BigTable] ORDER BY [IndexedColumn]; which have previously completed in time scaling linearly with table size (getting much slower for very large tables). Applicable queries should now perform in time scaling approximately linearly with the number of TOP rows (or OFFSET + FETCH rows) and logarithmically (or so) with table size (due to depth of index) making it now practical to perform such queries on tables with millions of rows.  Only the forward order of an index is currently supported for a “live” ORDER BY query; the reverse ordering will still require a non-live query (spool and sort) as before.

    Composite Memory Index

    The binary in-memory index form VistaDB has historically used for filtering data when there are multiple constraints or multiple columns.  This comes in particularly handy in the cases where there are multiple logical clauses being OR'd together that don't optimize out at runtime. 

    In the case where the Composite Memory Index is being used additional optimizations have been created specifically for sparse results (where most of the table rows are not being included) that can skip to the next block with the first row included instead of checking each item against the bitmap.  This can dramatically improve the performance for large tables.

    The Composite Memory Index has been integrated with the new ORDER BY capability to prevent it from spooling results into memory first and then sorting them for the return value when that would take significant memory.

    Multiple Temporary Indexes per Table

    VistaDB has been able to generate a temporary index in cases where there is a filter clause on a column and no index.  This process has been optimized by allowing it to make multiple single-column indexes on the same table (useful for the OR cases associated with Composite Memory Indexes) and allowed it to keep these indexes through an entire expression tree which allows a subquery and parent query to reuse the same temporary indexes.

    Improved Sub-query Performance

    Compared to prior versions of VistaDB subqueries can now reuse a significant amount of their work between top-level rows compared to prior versions of VistaDB.  Depending on the type of work done in the subqueries this can have a dramatic effect on overall query performance.

    Reduced Engine Overhead

    The amount of memory used by VistaDB has been optimized to let it both take advantage of higher memory systems and scale back to smaller systems even with large tables or very large columns.  The impact on the .NET Garbage Collector has been notably reduced for wide tables (with many columns) to reduce the potential for application impact and improve scaling across multiple cores.

    CLR Procedure Performance

    Internal changes to how CLR Procedures are invoked reduce the call overhead by 80%.  For simple CLR Procedures this can translate directly to a similar improvement in overall performance.

    New SQL Features Supported

    For VistaDB 5 several new TSQL language features are now supported:

    New Data Types have also been added:

    Improved Compatibility with T-SQL Scripting

    Proper Implementation of Rand()

    Previously, Rand() returned a new pseudo-random value for each row and each reference within a SELECT. Rand(int) would return a single fixed value based on the next pseudo-random value after the given seed but would not affect other usage of Rand(), so Rand could not produce repeatable pseudo-random data such as for testing and debugging purposes. SQL Server does not vary the result by row within a SELECT but does produce separate values for each reference within the SELECT list (and WHERE clause, etc). Use of Rand(int) in SQL Server would set a new pseudo-random seed for all usage of Rand() by the current connection and would return the first pseudo-random value after the new seed is set. With this correction, each reference to Rand() will return the next pseudo-random value but will then hold that value over all rows of the SELECT, and use of Rand(int) will correctly re-seed the current connection. Note that exact random sequence from a given seed value may not match SQL Server.

    Visual Studio & Entity Framework Support

    New FIPS-Compliant Encryption Support

    The previous database encryption which used a managed implementation of Blowfish has been replaced with the FIPS-compliant AES implementation built into the .NET runtime.  When an encrypted database is upgraded it will be converted from Blowfish to AES.  For .NET 4.0 and later the FIPS-compliant unmanaged implementation built into the .NET runtime (which leverages the Microsoft Crypto API underneath) is used.  For .NET 2.0 the managed implementation is used since the FIPS-complaint implementation isn't available.

    The new encryption support is now database-wide instead of requiring individual columns to enable encryption.  This protects the entire database including schema.  This mode is also faster than the previous implementation.

    New Diagnostic Logging Support

    The VistaDB provider now exposes a Logging facility that lets you capture information about each SQL batch and other important operations.  You can implement your own logger or use the Loupe Agent for VistaDB for a great experience with no coding required.

    Breaking Changes In This Release

    Maintaining compatibility with SQL Server and with previous releases of VistaDB is a high priority for the development team.  Like SQL Server, each major release is an opportunity to address longstanding issues even when they can't be done in a backwards compatible fashion.  The following items are changes to how VistaDB works that may affect existing code being brought forward:

    Visual Studio & Entity Framework Support

    Defects Fixed In This Release

    Engine

    Data Builder

    Data Migration Wizard

    Visual Studio Designer

    Known Issues

    Compared to VistaDB 4, there is the following issue still open:

    See Also