Database Best Practices: Efficient Database Queries in eFORMz

The following are some guidelines for configuring database connections in eFORMz:

• Plan for switching between development and production.
• Define databases using Databases.xml. Defning databases within projects limits switching.
• Use different names for dev and prod makes switching difficult. Use a common name and change the active db defined name as shown below:

Based on the above screenshots, the ‘database’ defined in all the project actions will always be ‘h2Active’. This facilitates a change from a single location, otherwise you are needing to change every SQLLoad and SQLUpdate in the project.

• Be careful not over-ride the schema/library. 
• Add conditions to as many SQL calls as possible.
• Combine SQL calls. Limiting the number of calls improves performance.
• Use Stored procedures or views when possible.

Efficient Queries

When you use SQL Load, SQL Lookup, or Table Lookup to query a relational database, you can take steps to ensure efficient use of resources. Skipping this step can lead to excessive time to complete even a basic query.

• Ensure you pass valid values in your query. If you pass values that are empty or not valid, your query wastes time. One way to control what queries are sent is to use conditions on an SQL Load step in a procedure. For example, if you are looking up prices based on a SKU, set a condition that the SKU value is not an empty string.
• Ensure that the table or view you query is properly indexed so you do not risk running a full table scan against a large table.
• Using a view or calling a stored procedure can improve performance and improve security for some types of queries, especially if they include joins.
• Select only the data that you require. If a table has 40 columns, but you need only three, limit the query to those three columns.

Additional Resources

• Calling database functions
• Database URL and Driver Classes
• Where to Set Up Your Database Connection
• Calling database stored procedures
• Troubleshooting a Database Connection
• eFORMz Table Lookup
• SQL Update
• Loading multiple rows with SQL Load
• SQL ResultSet Preprocessor
• Create placeholder names for SQL columns
• TroubleShooting: SQL Error – The Statement did not return a result set
• H2 Database