0

Currently I'm writing a REST server using a framework of a well-known Delphi component developing company. This framework provides the ability to simply write a class whose methods are named like the REST API endpoints.

The REST server fetches data from a MS SQL Server, I use dbGo components for that (what is ADO behind the scenes). There is also available an ORM framework of the same company mentioned above, but I don't want to use it for reasons.

Some of the REST calls work on a bunch of databases at once. In order to increase performance I implemented a connection pool, holding objects that encapsulate TADOConnection instances. When a REST call gets executed, it queries the connection pool if there is already an existing connection for the database(s) used by the call. If not, the required ADO database connection objects are created and handed over to the connection pool in order to be used again for subsequent connections to the same database(s).

I've noticed that the class with the methods named like the REST API endpoints gets instantiated by the framework for every single REST call. When the call is done the instance is freed. Because object instantiation and method calls are executed in the context of the REST server's thread (not in the main thread), I need to call CoInitializeEx in the constructor of the class and CoUninitialize in its destructor to be able to use dbGo's TADOxxx classes. Please note: There is no way to hook the server's thread creation or termination (I know, these would be the correct places to call CoInitializeEx and CoUninitialize, respectively).

The above concluded means that I store TADOConnection objects (encapsulating an ADO ActiveX class) during a certain cycle of CoInitializeEx/CoUninitialize and reuse them in a later cycle, i.e. COM has been uninitialized and gets initialized again between two usages of the same TADOConnection object.

Now my question is: Could that lead to any problems in a production scenario (for example crashes, memory or handle leaks or similar unwanted things) if multiple clients access the REST server? I'm quite unexperienced in dealing with COM and ActiveX respectively, so I need some help.

DinkumOil
  • 21
  • 5
  • 1
    ADO Connection objects should not be stored, you should create one when you need it and release it when you don't need anymore (same for other objects like commands, etc.). SQL Server driver connection pooling should be enabled and will reuse SQL connections across objects, SQL connections is what's really costly. And you should call CoInitialize(Ex) only once per thread (you can store that info in the thread local storage) (although it's really not recommended, you can somewhat forget about CoUnitialize if you can't do differently). – Simon Mourier Sep 02 '22 at 08:13
  • @SimonMourier How can I enable driver connection pooling? I'm using _SQLNCLI11.1_ as provider (the REST server talks to a MS SQL Server 2014, migration to a newer version is not planned in the near future). Unlike connection strings of the _.NET Framework Data Provider for SQL Server_ the connection string of _ADODBConnection_ objects does not support the _Pooling_ attribute. – DinkumOil Sep 02 '22 at 09:25
  • Connection pooling is a driver (not server) side feature implemented for more than 25 years by Microsoft drivers. If you don't need NativeClient features you should get rid of it as it's now deprecated and use OLEDB or ODBC drivers https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client which have native cnx pooling. Anyway, if you want to keep NativeClient with ADO (Provider=SQLNCLI11.1) you have connection pooling for free. – Simon Mourier Sep 02 '22 at 10:42
  • see this: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/updating-an-application-to-sql-server-native-client-from-mdac?view=sql-server-ver15 *"User-accessible functionality supplied by MDAC components is available when using SQL Server Native Client. This includes, but is not limited to, the following: connection pooling,..."* To disable it, you must add "OLE DB Services=-4" to the connection string (although OLEDB is not in the picture, it works) – Simon Mourier Sep 02 '22 at 10:42
  • Thank you for providing help so far. Unfortunately your suggestions didn't solve my problem. I installed _Microsoft OLE DB Driver for SQL Server_ and changed my connection string's attribute _Provider_ to _MSOLEDBSQL_. Then I deactivated my own connection pooling. As a result, repeated queries to the same bunch of databases are lasting 5 seconds each. **With** my own connection pooling the first query lasts (of course) 5 seconds too, but subsequent queries last only half of a second. So, what am I doing wrong? – DinkumOil Sep 02 '22 at 11:52
  • 1
    I don't know what test you're doing exactly or what connection string you use, but connection pooling works fine by default with Microsoft provided drivers. Here is a small vbscript I use to check that https://pastebin.com/raw/8Z6sKLkf – Simon Mourier Sep 02 '22 at 12:32
  • Thanks for the script, it was useful for testing. The results: When running your script, the connection stayed open (approx. for 60 s) and was visible in the output of _exec sp_who2_ and of console command _netstat -ba_. If I do the same test with my application (own connection pooling **on**), it behaves the same (24 open cnx). However, if I turn **off** own connection pooling (i.e. after a REST call is done the _TADOConnection_ Delphi object and the underlying _ADOConnection_ ActiveX object are freed) the connections immediately disappear in the output of _exec sp_who2_ and _netstat -ba_. – DinkumOil Sep 02 '22 at 14:33
  • So it is evident, that driver connection pooling only works as long as the _ADOConnection_ objects are not freed. That's the reason why I store them in my own connection pool. So, the question still is: Is it possible to reuse these objects across multiple cycles of _CoInitializeEx/CoUninitialze_ without getting unwanted side effects? – DinkumOil Sep 02 '22 at 14:39
  • 1
    Actually, it's a bit more subtle. Connection pooling works as long as there's at least *one* instance of a Connection object alive (not released). See here: https://learn.microsoft.com/en-us/previous-versions/ms810829(v=msdn.10)?redirectedfrom=MSDN#enabling-ole-db-resource-pooling . You can check that with a C++ code like this: https://pastebin.com/raw/QQuESh37 I persist in the idea that you shouldn't need to wrap anything about pooling. – Simon Mourier Sep 03 '22 at 14:31
  • I changed my own connection pool in a way, that it holds at least one connection to every DB ever used during the live cylcle of the program. The pool creates this connection, opens it and closes it immediately, but it is never actually used to query data from SQL Server. Instead the pool creates special "working connection objects" which are freed after processing a query. With _exec sp_who2_ I can see 24 open connections (to my 24 DBs) which are closed after approx. 60 s. I can also see a performance gain. Querying all DBs for the first time lasts 6 s, subsequent queries last 3 s. – DinkumOil Sep 05 '22 at 17:13
  • Conclusion: There IS a performance gain using this method but it is not as high as using my own connection pooling (querying all DBs for the first time lasts 5 s, subsequent queries last 0.5 s). Additionaly, the DB driver closes the connections in its pool after ~60 s, but my own pool still holds these never-used dummy connection objects that now are not backed anymore by a real connection, i.e. the connections for all subsequent queries are freed after use. I would have to find a way to synchronize the content of my own connection pool with the driver's pool, which I think is not possible. – DinkumOil Sep 05 '22 at 17:13
  • So, my current solution is to use Thread Local Storage (as you suggested) to remember if _CoInitializeEx_ has already been called for a certain thread. The server class of the framework used by my REST server spawns a thread for each REST API call and uses a thread pool to improve performance. That means the _ADOConnection_ objects stored in my connection pool may be used by different threads. So I use _COINIT_MULTITHREADED_ when calling _CoInitializeEx_. That solves my initial problem of reusing ADO objects because there are no multiple _CoInitializeEx/CoUninitialize_ cycles anymore. – DinkumOil Sep 05 '22 at 17:13

0 Answers0