Performance and Considerations
To increase efficiency of processing SQL queries against the file system, emissary RT: Files makes use of an in-memory caching system. This cache (if enabled in the Data Source options) monitors the file system and is updated in real-time should files be changed. This cache is initially built when the ODBC connection is established, and is maintained for the lifetime of the connection. If real-time updates are disabled, emissary RT: Files will rescan and update its cache (if necessary) when it executes a SQL query. Due to being more resource intensive, image and audio information is only cached/updated if the SQL query involves those tables, respectively.
When making use of emissary RT: Files 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: Files is optimized for predicates comparing the equality of Image.ID and/or Audio.ID against Standard.ID. E.g. "SELECT * FROM Standard INNER JOIN Audio ON Standard.ID = Audio.ID", "SELECT * FROM Image, Standard WHERE Image.ID = Standard.ID", "SELECT * FROM Standard, Image, Audio WHERE Standard.ID = Image.ID AND Standard.ID = Audio.ID", etc. Comparisons of Image.ID directly to Audio.ID are not optimized. 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 size of the file system (especially if recursion is enabled), the speed of the underlying hardware in querying the file system, if image or audio data is being queried, etc. Because it may be desired to execute a query that may take a significant time to process, emissary RT: Files 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 the file system 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.