Performance and Considerations
To increase efficiency of processing SQL queries against Windows Server services, emissary RT: Servers makes use of an in-memory caching system. This cache (if enabled in the Data Source options) creates a snapshot of the respective service, greatly increasing performance. This cache is initially built when the ODBC connection is established, and is maintained for the lifetime of the connection. If the snapshot is disabled, emissary RT: Servers will rescan and update its cache (if necessary) when it executes a SQL query. This ensures all data is 100% up-to-date.
When making use of emissary RT: Servers in custom applications, as the cache is built at connection time and is maintained for the lifetime of the connection, it is important to reuse the ODBC connection when possible. This can be more challenging in a web application environment, and may require changes to both the web server configuration and API used. An example includes PHP's odbc_pconnect function and a compatible Apache configuration (non-CGI mode), which creates a persistent connection across each request (for the session lifetime).
When executing queries containing related tables (either via JOIN or appropriate WHERE clauses), emissary RT: Servers is optimized for predicates comparing the equality of foreign keys to primary keys. E.g. "SELECT * FROM DHCPScope LEFT JOIN DHCPLease ON DHCPScope.ID = DHCPLease.ScopeID", "SELECT * FROM DHCPScope, DHCPLease WHERE DHCPScope.ID = DHCPLease.ScopeID", "SELECT * FROM DNSZone JOIN DNSDomain ON DNSZone.ID = DNSDomain.ZoneID JOIN DNSRecord ON DNSDomain.ID = DNSRecord.DomainID", etc. Predicates may contain additional expressions, as long as OR operators do not allow for potential additional matches in the join. Any non-optimized predicate with valid syntax may be used, but performance will degrade significantly, as the system must internally perform a full cross join.
Additionally, all tables are indexed against their ID column, and will perform significantly faster with WHERE clauses that select for specific IDs, via inline values and/or parameters. As with related table optimization above, WHERE clauses optimized for ID indices may contain additional expressions, as long as OR operators do not allow for potenital additional matches.
The execution time involved for a SQL query is dependent on the number of records in the service being queried, the speed of the underlying hardware hosting and querying the services, how many services are being queried, etc. Because it may be desired to execute a query that may take a significant time to process, emissary RT: Servers provides an interactive mode that shows both a progress meter, and allows the cancelation of a query. Note - canceling an INSERT, UPDATE or DELETE query is not ACID compliant - changes are made to a service in real-time, and are not automatically rolled back. Please construct a corresponding SELECT query for testing before executing any potentially destructive INSERT, UPDATE or DELETE queries.
