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

[Jyoti Sharma]

Hi,

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 execute 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

[ganesh roti]

i guess you need to take the validation logic out of stored procedures so that it can be then deal by the threads seperately and concurrently. But if you are not touching the SPs then i guess since your SP is heavy only 2 threads are sufficient.

[Joe Ess]

Jyoti Sharma wrote:
(1) What should be the limit on number of connections with database at a time?

The limit should be the number you need to get your work done.

Jyoti Sharma wrote:
(2) How many threads at a time are recommended?

Again, the number you need to get your work done.
There are no hard and fast rules about this kind of thing because there are too many variables. Your best bet is to test a lot and monitor your production code so you can be sure your testing assumptions are correct.
Check out our EnterprisePerformance FAQ for more