Best strategy for use in a multi-threaded application

[Chrispy]

Hello,

I have one quick question related to the best way to use the MyDAC components within a multi-threaded environment. To explain, the system I'm writing is based upon TCP/IP sockets, where many client applications (approx. 100 - 200) connect to the server and maintain a persistent socket connection. This is performed using the Indy sockets library, and therefore each of those clients is handled via a separate thread within the server - all running at the same time.

How would you recommend I handle database access from those threads on the server side to a) ensure thread-safety, and b) get best performance. Should I maintain a single, global TMyConnection object and then instantiate TMyQuery/TMyCommand components within each thread as required (attaching them to the global TMyConnection) then free them as soon as possible? I only suggest this approach because that's how ADO works best - creating and destroying the objects as soon as possible to benefit from connection pooling, rather than creating one per thread and holding it for the lifetime of the thread (which causes problems with the connection pooling). Is it the same with MyDAC? Or should I be creating separate TMyConnection objects per thread? Or should I be creating and holdng the objects for the lifetime of the threads? There's so many possibilities! Also, how can I then affect the connection pooling to find the optimum settings?

I'm also looking to migrate some of the functionality using the excellent RemObjects framework (but not their Data Access product unfortunately). Here the situation is that server 'objects' are being created/destroyed to service client requests on-demand. Again, should I create a global TMyConnection object than exists for the lifetime of the server, then as each service object is created instantiate MyQuery objects as required, connecting them to the global connection and destroying them as soon as possible?

Sorry, if this doesn't make much sense - I'm having problems explaining the scenarios. I'm just really trying to determine the best strategy for using the MyConnection/MyQuery components to ensure best throughput in a multi-threaded application while benefiting from connection pooling, etc.

Many thanks in advance, and thanks for a great product!

[Ikar]

In the specified case it would be correct to create own TMyConnection and TMyQuery in each thread and delete them on completion of the thread. To optimize a number of connections with the server use MyConnection.Pooling := True. You can read details about it in MyDAC help.