Efficient Database Queries in eFORMz

Question

How do I set up database queries so they are efficient?

Answer

Write Efficient Queries

• Be careful not to over-ride the schema/library.
• Combine SQL statements. Limiting the number of calls improves performance.
• 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 is properly indexed so you do not risk running a full table scan against a large table.
• Use a view or call a stored procedure for better performance and improved 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. Use conditions, as described above, to limit the number of rows returned.

Assistance

If any further assistance is needed, please contact our Support team.