The following requirements must be satisfied for a table view to function properly in virtual mode:
- Only bound tables are allowed. Unbound and SQL-based tables cannot be used in virtual mode. See Bound, SQL-Based and Unbound Tables.
- The table must have a primary key, see Table Properties.
- An appropriate index must exist in the database for the specified sort order. More exactly, this is not a necessary condition, but virtual mode can have very poor performance if the index does not exist. An index is usually necessary for the database SQL optimizer to produce an efficient execution plan.
- The database must have sufficiently advanced query optimization capacity to interpret SQL statements issued by DataObjects for .NET in the optimal way, the way they intended to be interpreted. Microsoft SQL Server and Oracle satisfy this requirement, whereas Microsoft Access, being a low-end desktop database does not qualify. DataObjects for .NET virtual mode works correctly with Microsoft Access, but queries fetching data may take a long time to execute on the server. SQL statements generated by DataObjects for .NET in virtual mode look approximately like this:
SELECT TOP 400 FROM T WHERE K1=? AND K2>? ...ORDER BY K1, K2,…
where K1, K2 – primary keys. You can see the actual generated SQL statements in the AfterGenerateSql event.
- The database must support the TOP n keyword in SQL limiting the resultset to the first n rows. An exception to this is Oracle, which does not support TOP n, but works with DataObjects for .NET virtual mode, because DataObjects for .NET uses a different construct with Oracle: the hint /*+ FIRST_ROWS */. To distinguish Oracle from other databases, the Syntax enumerated property is used, available in the Connection Editor in the Schema Designer.
- Primary key types must be ordered and comparable, that is, the > comparison operation must be applicable to them. All common SQL data types satisfy this requirement.
- If you use filter conditions with virtual table views, the conditions added to the WHERE clause of the generated SQL statement should not break query optimization. Basically, it means that if you restrict the sort order key values by filter conditions, try to restrict only an initial segment of the sort order key sequence. Avoid situations where a key is restricted and one of its predecessors in the sort order key sequence is not. This is not a mandatory requirement. Virtual mode will work correctly regardless of filter conditions. However, with very large rowsets, filter conditions breaking optimization can significantly affect performance.