How to minimize number of connections to DB in SoapUI load tests?

Question

I've been using SoapUI to test WS the next way (common structure of TestCase):

1. Groovy script to generate data (for request compilation & response checking)
2. SOAP Request with assertions

Groovy script code example (pseudo code, just to show idea):

// variables declaration
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.driver.OracleDriver")
sql = Sql.newInstance("jdbc:oracle:thin:@${scriptDenServer25}:${scriptDBport}:${scriptSid}", "${scriptUsername}", "${scriptPassword}", "oracle.jdbc.OracleDriver") 
// request to DB (get one random row)
// assign data to variables
sql.close()

This works pretty well in cycle "one request after one", but when I use this TestCase in Load Test scenario there is a problem of quantitiy of connections to DB. There are as many connections as number of threads.

Is there any way to avoid this "spoiling of connections"?

UPDATE: the database open/get data/close is NOT the part of that performance measurement. It is just test/check data preparation. So idealy there should not be any time wasting for DB connection, just requests. But I need unique data & real data, so there is no possibility to avoid working with DB, so this part should be as less time/resources consuming as possible.

There are two (as I see now) possible scenario to solve the problem (Don't know whether it is possible in SoapUI):

1. To get N rows at one DB connection and then go through them untill they are over, then repeat this once again. (We reduce DB connection N times, maybe...) (There should be possibility in SoapUI to call SOAP request from Groovy script - in a loop for each row)
2. To organize one stack (FILO) of test/check data for all threads, would be the best solution (only one connection), but still do not know whether it is possible in SoapIU.

Answer 1

Re-written based on updated question:

Create a singleton pattern with the DB connection information and then call all the SOAPUI tests passing in the data connection parameters as utilized. This allows each test to get the database connection as currently defined, but it only returns the first connection opened to all tests calling for it.

It's hard to write the code for you though as we don't have the soapui data call part or the groovy part.

Here are some references to how to write this:

• Groovy Singleton Pattern
• SoupUI and Groovy

Answer 2

I have solved that very same issue that you're having by creating a special "Data" repository test suite within soapUI. Each test case within that suite is a separate database connection/connections that fetch the necessary test data required in requests.

It works because by preparing all the necessary test data up front you minimize the data connections that are run up against the database in the load test.

What you do is run the data pool once and then run the load test for as long as you please.

The only problem that I foresee is that you need additional database data for system integration testing as you mentioned response checking? But why would you have that as a part of your load test in the first place? Because if you need to do that, then you're out of luck. But I'm not quite sure you really do need to :)

Also,

other option is to do it all within your load test and loop around the test case performing requests with a counter and then fetching more data every now and then from the database.