Explanation: When a database call is made from within
a message flow node, the flow constructs the appropriate SQL, which
is sent using ODBC to the database manager. As part of this process,
the SQL statement is prepared using the SQLPrepare function, and a
statement handle is acquired so that the SQL statement can be executed. For performance reasons, after the statement is prepared,
the statement and handle are saved in a cache to reduce the number
of calls to the SQLPrepare function. If the statement is already in
the cache, the statement handle is returned so that it can be re-executed
with newly bound parameters.
The statement string is used
to perform the cache lookup. By using hardcoded SQL strings that differ
slightly for each message, the statement is not found in the cache,
and an SQLPrepare function is always performed (and a new ODBC cursor
is opened). When using PASSTHRU statements, use parameter markers
so that the same SQL prepared statement can be used for each message
processed, with the parameters being bound at run time. This approach
is more efficient in terms of database resources and, for statements
that are executed repeatedly, it is faster.
However,
it is not always possible to use parameter markers, or you might want
to dynamically build the SQL statement strings at run time. This situation
potentially leads to many unique SQL statements being cached. The
cache itself does not grow that large, because these statements themselves
are generally not big, but many small memory allocations can lead
to memory fragmentation.