limit of connections with database and number of java threads in an application

I am working to develop a JMS application(stand alone multithreaded java application) which can receive 100 messages at a time , they need to be processed and database procedures need to be called for inserting/updating data. Procedures are very heavy as validations are also performed in them. Each procedure is taking about 30 to 50 seconds of time to 开发者_StackOverflowexecute and they are capable to run concurrently. My concern is to execute 100 procedures for all 100 messages and also send reply within time limit of 90 seconds by jms application. No application server to be used(requirement) and database is Teradata (RDBMS)

I am using connection pool and thread pool in java code and testing code with 90 connections. Question is :

(1) What should be the limit on number of connections with database at a time? (2) How many threads at a time are recommended?

Thanks, Jyoti

90 seems like a lot. My recommendation is to benchmark this. Your criteria is uniques and you need to make sure you get the maximum throughput. I would make the code configurable with how many concurrent connections you use and run it with 10 ... 100 connections going up 10 at a time. This should not take long. When you start slowing down then you know you have exceeded the benefits of running concurrently.
Do it several times to make sure your results are predictable.

Another concern is your statement of 'procedure is taking about 30 to 50 seconds to run'. How much of this time is processing via Java and how much time is waiting for the database to process an SQL statement? Should both times really be added to determine the max number of connections you need?

Generally speaking, you should get a connection, use it, and close it as quickly as possible after processing your java logic if possible. If possible, you should avoid getting a connection, do a bunch of java side processing, call the database, do more java processing, then close the conection. There is probably no need to hold the connection open that long. A consideration to keep in mind when doing this approach is what processing (including database access) you need to keep in single transaction.

If for example, of the 50 seconds to run, only 1 second of database access is necessary, then you probably don't need such a high max number of connections.