3

I want to design my TFDQuery using the component editor, i.e. set the SQL string, options etc. during design-time and then use the query in a thread.

My problem is that each running instance of the thread needs its own instance of the query - otherwise it will not be thread-safe.

Should I clone the query when the thread starts running, i.e. in the Execute method of the thread, and then set its connection - if so, how? Or is there a better way to do this?

Thank You

Floris
  • 1,082
  • 10
  • 26

1 Answers1

3

As described in the documentation:

...after a thread opens a query and until its processing is finished, the application cannot use this query and the connection objects in another thread. Similarly, after a thread starts a transaction and until the transaction is finished, the application cannot use this transaction and the connection objects in another thread.

And also:

The standard simplification is to create and use for each thread a dedicated connection object working with the database.

So the easiest solution is to have a separate connection per worker thread. In case you use the FireDAC (connection, transaction and query) components all on the same datamodule then the easiest solution for you might be to simply create a separate instance of the whole datamodule for each of your worker threads.

Ondrej Kelle
  • 36,941
  • 2
  • 65
  • 128
  • Thank you for your answer! The data module has about 5 queries and will be called about 5 times a second on average during peak hours. I was a bit concerned about the time it would take to create the data module each time. Will do some tests and report back here. – Floris Jan 11 '16 at 13:48
  • Welcome. Instead of creating and destroying the data module for each request, you might consider using a connection pool to keep connections so they can be reused. – Ondrej Kelle Jan 11 '16 at 13:53
  • Yes. My idea was to use the connection pool provided by FireDac, but your proposal is probably only a bit of extra work and I can still use the FireDac connection pool with it. – Floris Jan 11 '16 at 13:57
  • From the linked documentation, it seems the only thing you need to do to have automatic connection pooling is set the connection's `Pooled` parameter to `True`. There's a demo called `Pooling` in the Samples directory which demonstrates this. – Ondrej Kelle Jan 11 '16 at 14:09
  • Thanks again TOndrej. I've used the connection pooling - it works 100%. Also tested creating the data module and freeing it each time the thread is created, i.e. no 'data module pooling' and in a loop of doing that 100 times it takes about 10ms for each create and free on my slow laptop so going to stick with that. – Floris Jan 11 '16 at 15:50
  • the link is broken – Márcio Rossato Mar 29 '22 at 13:57