Efficient Usage of DB Resources
To improve efficiency and decrease usage of hardware or network resources, you will typically want to avoid calling the same database multiple times in the same mapping unnecessarily. There may still be situations where you simply cannot avoid calling a database multiple times because of the nature of the mapping, but here are some general considerations:
•If you need only one database call, avoid placing the database component in a parent context that would demand calling the database multiple times. This could happen, for example, if you add a database component inside a user-defined function that receives a sequence of values as input and thus gets called for each item in the sequence. See also User-Defined Functions. Variables are typically helpful to gather data into the same context before you pass it on to the target component.
•If you need to aggregate values from a database (for example, to count the number of records using the count function), it is recommended to connect the output of the aggregate function to a variable where compute-when=once. This prevents repetitive calls to the database, as described in Example: Counting Database Table Rows.
•Try to extract all database data in one call (e.g., a SQL-SELECT statement or a stored procedure), as opposed to adding the same database component multiple times in the mapping.
•If you need to extract data from multiple tables or views from the same database, it is advisable to use a Join component (in SQL mode) or an SQL SELECT statement. The latter is more convenient if you prefer to write the SQL SELECT statement yourself. If you need to join database data to some non-database data or data from different databases, use non-SQL joins. To optimize execution of non-SQL joins in data-intensive mappings, run mappings with MapForce Server Advanced Edition.
•If you need to filter data from a database, it is more efficient to use an SQL-WHERE component instead of a standard filter, since the former component is optimized for working with databases specifically, in the grammar of the corresponding database.
For information about context and general rules and strategies, see Basic Rules and Strategies.