Explanation: When a database call is made from within
a message flow node, the flow constructs the appropriate SQL, and
this 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 runtime. 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 runtime. This 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.