Using SQL to Access Snapshot Tables

Performance: Accessing a database through its management tool is typically faster than getting the data through EnterWorks. It also doesn’t impact EnterWorks’s processing rate for other jobs. However, this may not be true, depending on the type of queries being made, the number of rows being processed, and if indexes are being used efficiently.

Simplicity: Accessing the snapshot table directly may be easier than having EnterWorks generate files that you later need to process to get to the data.

Ease of use: SQL queries allow you to pull data from multiple tables at once.

Familiar Tools: You may already have a third-party application that you use to query your databases.

Data duplication: Snapshot tables hold duplicate copies of the attributes stored in XML. When an attribute value changes, EnterWorks updates it in both the repository and in the repository’s snapshot table(s). This increases the amount of time needed to change the value.

Using SQL to update a snapshot table does not update EnterWorks’s database: Changes made to a snapshot table are not written back to EnterWorks’s database and they will be overwritten when the snapshot table is next updated. You should always use an EnterWorks UI, EnterWorks’s REST API, or import data to make changes to the database.

You must be careful when granting access to the snapshot tables: Depending on the access granted to users, SQL can also be used to read and write directly to EnterWorks’s system tables and database. This is extremely dangerous. It puts your data and system’s stability and validity at risk. Do not allow users access to system tables and the EnterWorks database.

No audit trail: If you pull data from EnterWorks’s snapshot tables directly, you have to keep track of when you got the data and what data you retrieved. Unlike when you have EnterWorks generate export files, there are no artifacts or audit trails, other than those you create and manage yourself.

You manage your data’s freshness: EnterWorks allows you to generate files according to a schedule. You can also construct a "delta" export, so each export file only includes the records that were added or changed since the last file was generated. If you use SQL to pull values from the snapshot tables directly, you have to keep track of when you last retrieved data and if desired, determine how to retrieve only records that have been added or changed.

Decreased performance: If you are properly indexing the snapshot tables and performing simple SELECT queries, your queries should have little impact on EnterWorks' performance. But if your users are making complex queries on large datasets or if their queries result in table scans that require the database server to access each record, the database server’s CPU can become overloaded, which in turn slows EnterWorks’s response time. If you find yourself in this situation, consider mirroring the snapshot tables in your database to another server that can be queried without affecting EnterWorks’s performance. Mirroring your database, however, means you need to manage keeping the duplicate database current.