# Databases - Best Practices

The following are guidelines to consider when building Databases for your project:

- For tables, create an index to speed up queries.

  When creating an index, use only a single index per table, and the index should contain each column that will be used in a WHERE or ORDER BY statement. The order of columns in the index matters; columns used in WHERE statements should occur before columns that only appear in ORDER BY statements.

  The larger the database, the greater the impact on insert performance. This issue is more likely to be noticed when using the default mode (leaving the Enable Unsafe Write (Fast Mode) checkbox unchecked).

  Note: For more information, see the Creating a SQLite Database topic.

- Limit the number of rows returned by a query.

  Before creating a table, consider how many rows will actually need to be displayed to the user; less rows equals faster query times. In general, the user does not need to know the entire row history, every time. Instead, for example, consider limiting it to the last seven days, or provide a better filter mechanism.

- The first column of a table should be the row's unique ID.

  The first column of a table should be used to uniquely identify each row. This is standard practice for databases, and allows the {TableName}.Update method to be used correctly.

  The {TableName}.Update method is a helper function that is used to update values on a specific row, e.g. $Databases.FastDatabase.Results.Update(value1, value2, value3), which translates to a SQL Update statement of: UPDATE Results Set Col2=value2, Col3=value3 WHERE Col1=value1.

- Backing up and restoring a database.

  Currently, Designer does not support backing up and restoring a database, this must be implemented by the user.

  Note: To restore a database, Designer must be closed before replacing the .db3 file.

- Do not save image data in a database.

  Instead of saving image data in a database, save a relative link to the image file. Store the root image folder path as a tag, so that when the project is deployed or the Designer application is moved, you can easily continue to work with the database.

  In addition, you can use %Environment_Variables% in the string input for the RootDirectory pin on an Image Recorder block. If using this option, use %PROJECT_PATH% in the string to so it is relative to the project folder (e.g. $ImageSavePath = "%PROJECT_PATH%\\Images";). This ensures that when the project is being developed, it will be saved into the project folder, and when the project is deployed, it will use the automatically use the Deployed Folder path.

- Default "Safe" Mode or Enable Unsafe Write (Fast Mode)

  Cognex recommends using the default "safe" mode, unless attempting to address a specific performance issue. There is no difference in the execution time for queries that read from a database (e.g. SELECT); it only affects statements that write to a database (e.g. INSERT, UPDATE, DELETE, etc.).

  When Enable Unsafe Write (Fast Mode) is enabled, Designer will offload the writing to the database to an asynchronous buffer, which means that there should not be a direct Disk I/O performance hit when altering a database. However, this option does present some risks:

  - If a hard reset/power cycle happens to the PC at an inopportune time, the database can become corrupted. For example, if the PC is not properly shutdown before an operator power cycles the entire line, or if the main power is cut at the end of a shift.
  - Stopping Designer via a Windows Task Manager End Process command can possibly corrupt the project.
  - The only option to recover from such a scenario is to manually revert to a backup copy of the database.
  - If the database is missing at startup, a new one will be automatically created, without an error prompt or warning.
  Note: If using Enable Unsafe Write (Fast Mode), the following SQLite settings will be set automatically: Journal Mode = In Memory and Synchronous Mode = Off.

  The following graphic of a Task with two different Database Writer blocks, one using the default, "safe" mode, and the other with Enable Unsafe Write (Fast Mode) enabled, illustrates the difference between the two modes:

- Enable the Publish Query Results as Tags checkbox.

  This allows properties of Page elements to be easily bound to a small set of results from a database.

  It is also highly recommended that rows NOT be processed via a script. Instead, see the Iterate Through Rows of a Query topic for an alternative approach.

  Note: For more information, refer to the Advanced Query Options topic.

- The Reverse Row Index Parameter

  While this option exists in the Advanced Query Options dialog, Cognex recommends that this checkbox not be enabled. This option performs an in-memory reverse of the query results, and should only be used if a large performance hit is being encountered by using the ORDER BY statement. However, before implementing this option, first consider reexamining how the index is being used.