VistaDB 5
Gibraltar VistaDB > Developer's Guide > SQL Reference > SQL Reference - Optimizing SQL Performance in VistaDB
SQL Reference - Optimizing SQL Performance in VistaDB

Enable Connection Pooling

Avoid the overhead of opening and closing the database file on every operation by enabling connection pooling.  See How To - Use Connection Pooling for more information.

VistaDB SQL is Not Compiled

T-SQL Procedures, Functions and Operators are not native operations in VistaDB. Internally VistaDB interprets SQL Code into a command tree that is executed in .NET CLR code.  This is fine for most simple cases, but large SQL scripts may perform much more slowly on VistaDB than SQL Server.  The usual cause is complex logic that calls many SQL functions, or chaining SQL functions to call other SQL Procs as a part of their logic.

VistaDB Doesn't Rewrite Queries

SQL Server will often rewrite your SQL code internally. For example, when code paths are never executed they are optimized out by the compiler.  VistaDB can't do that, so it will have to parse, validate, and prepare each statement in your script even if it is never executed.  This process happens each time the statement is prepared.  Internally SQL Server compiles SQL Script almost like a virtual machine.  So you could say that SQL Server includes a VM of SQL commands that are being executed. These compiled commands are opcodes very similar to how CLR IL code is interpreted by the .Net runtime.

Join Order is Critical to Performance

With VistaDB it's important to order the tables in the FROM section to help VistaDB process them in an optimal order.  As a general rule, ordering tables from the one with the fewest rows to the most rows will provide the best performance.  For example, given tables A, B, and C where (once filters in the WHERE clause are applied) A will only have 10 rows, B 100, and C 1000 it's best to express the query as

    INNER JOIN B ...
    INNER JOIN C ...

Instead of

    INNER JOIN B ...
    INNER JOIN A ...

The reason is VistaDB will apply the constraints and work down in the order you specify the tables.  Every time it advances an outer table it has to re-evaluate the inner tables.  If it starts with a table that has many rows it ends up doing a great deal of re-evaluation work.

SQL Server will do this for you automatically but VistaDB doesn't have a query re-writer to do this for you.  VistaDB will optimize the application of WHERE clause elements so ordering is irrelevant with them.

With VistaDB CLR Procs are Faster than Stored Procedures

CLR Procs are generally faster for complicated logic. They are actually compiled by the CLR JIT and run as native code (and are promoted to 64 bit if the processor is 64 bit). In addition, they also work within the .Net framework and can be debugged externally.

PRINT Performance Considerations

Many users will want to use PRINT as a way to debug their SQL Scripts. This is fine for getting diagnostic messages out to the app to tell it what has executed. But do not leave these statements in working production code.

All calls to your application print event handler are run as the line is being executed in the SQL script. Any long running commands as a part of the callback will slow down overall execution of the SQL. Just the event chain for calling the host application is quite a bit of overhead if you put the PRINT message in a loop for example, or include a lot of PRINT statements. Remove the PRINTS before you put your application into production.

See Also