Databases
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 below. 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 the Example: Counting Database Table Rows.
•Try to extract all database data in one call (for example, a SQL-SELECT statement, or a stored procedure), as opposed to adding the same database component multiple times on the mapping.
•If you need to extract data from multiple tables or views from the same database, it's advisable to use either a Join component (in SQL mode), or a 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's more efficient to use a SQL-WHERE component instead of a standard filter, since the former component is optimized for working with databases specifically, in the grammar of the respective database.