I seeking opinions regarding FB transactions in an application. My current apps call some complex lookup modules, usually a DLL. When I use the sqls (which are all read only selects) under the application main transaction (which allows for db updates) the lookup modules can take some time. I did experiment with a separate transaction for the lookup modules however some customer sites which use Windows 2003 servers upwards cause the modules to take some time to startup which defeats the purpose of the lookup.
What is the correct way to approach this? Is it a matter of multiple transactions specific to the sql tasks (i.e. read only, retain commit)? or should it be one transaction per application?
You opinions please would be appreciated.