Efficient Database Queries in eFORMz
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.