I've always thought that storing handles for reuse at different times was a
bad thing, but I though I would bounce this idea off this group and see what
everyone else here thought.  I have a COM+ DLL I wrote in VS.NET 2K3 that
exposes itself as a COM object for use on an NT4 (SP6a) Database server
running SQL Server 2000.  I use this component in a couple database
triggers.  It's purpose is to validate a couple business rules and mirror a
copy of the data.  Whenever a user inserts or updates a record on the SQL
server it connects to a DB2 Database running on an AS400 and validates the
customer and unit data.  In addition to this validation, it also mirrors any
data changes (insert, update, delete) in a mirror DB stored on this AS400.
The problem I run into is that my component is very expensive in terms up
initiation time because it has to connect to the iSeries build the
connection and actually open it.  This instantiation time takes anywhere
between 5 and 10 seconds each time and becomes a serious burden on the app
due to heavy data modifications by end users.  So what I was considering was
creating a table in tempdb (a database that is destroyed and recreated each
time the server starts up) and store the object ID (COM object name), the
caller ID (proc id) and the COM handle to an instance for this combination
of Object and Caller ID's).  In other words, each trigger would have a
defined caller id and it's own instance of my object.  If an instance
doesn't exist for this combination, one is created, stored in this table
upon request and the handle returned to the caller.  However, if there is an
active instance, it's handle is returned,  Likewise, when a request is made
to destroy an instance, the instance is destroyed and removed from this
table.  And since the DLL runs in the same address space as the SQL server,
all active instances would be destroyed as well as the table in the tempdb
whenver the server was started.

Is this a BIG NO-NO to store these handles like this or should I look in a
different direction like obkect pooling?  If pooling is the way to go can
someone point me in the direction to some doc on how to code this technique.
Please advise if this is a question I should ask in the SQL server group
rather than here in interop.

Thanks

Barry Lance