ScriptRunner DatabaseUtil.withSql extream slow

Question

We using Jira Server with Scriptrunner. When I fire a sql request with groovy it takes sometimes 10x longer than direct as sql request. But we also have extreme fluctuations.

The database is Oracle. In jira the max pool size is 20 connections.

My example SQL (~3500 rows)

SELECT * FROM CWD_USER_ATTRIBUTES

With groovy/scriptrunner:

DatabaseUtil.withSql("MY_POOL") { StrictSql sql ->
    sql.rows(sqlString)
}

Some test calls in milliseconds:

* 2063
* 369
* 2069
* 1035
* 2676
* 1849
* 1624

With a sql client without scriptrunner (milliseconds):

* 84
* 70
* 75
* 70
* 71

Smaller SQL request with only one row needs <20ms. No problems.

Anybody an idea or a tip for better analyse?

Thank you!

Jamie Echlin _ScriptRunner - The Adaptavist Group_ · Answer

> But now we will try a Database connection in resources.

That's worth a try. Let us know if that improves things.

Steffen Stamprath_vkmbkq · Answer

I mean a other client (DataGrip).

We configured it in the resources from scriptrunner. But currently we use the "Local Instance". I hope this mean it use the same settings from the dbconfig.xml.

But now we will try a Database connection in resources. I hope I can setup better connection with the additional additional properties.

Maybe you have a good tipp :-)

Jamie Echlin _ScriptRunner - The Adaptavist Group_ · Answer

When you say "With a sql client without scriptrunner" do you mean with a native client like sqlplus? Or a other jdbc client?

I can't really explain the variance, something doesn't really add up there.

Where/how is the pool configured to 20 connections?