Multithread database access (.NET)

Question

Hi,

I'm currently building an app that generates quotes. The total time to generate all quotes is about 10 secs, and at present provides an unacceptable user experience.

What I want to do is multi-thread each quote so the user can see results as they come in. The problem is that the app I've inherited does a lot of bus logic on the db.

What I'm wondering is if it's ok to multithread each quote item, or would this cause too much load on the db server (e.g. 5 quotes = 5 threads, or threadqueueworkeritems)?

Answer 1

On most RDBMS, 5 concurrent connections shouldn't be an issue, so I think you can easily have five threads, each one using its own connection

Answer 2

Databases are typically designed to handle multiple concurrent connections. So nothing to worry about.

Answer 3

The only thing I would worry about is what "generating a quote" entails on the DB. This may involve writing to the database and if you've got a lot of transactional activity in there, the different quotes may compete with each other and could end up with quotes coming in series due to blocking or worse, deadlocking.

This is worst-case scenario stuff though, going from the scant details given in the question.

Answer 4

There shouldn't be a problem with database load. However, you'll want to check the code for deadlock conditions, which you're more likely to find when you start running things in parallel. Also, your transaction boundaries are going to have to be well defined, but if this is generating a quote, I wouldn't imagine that ReadUncommitted would be a problem