ScriptRunner DatabaseUtil.withSql extream slow

Steffen Stamprath June 23, 2023

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!

1 answer

1 vote
Jamie Echlin _ScriptRunner - The Adaptavist Group_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 23, 2023

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? 

Steffen Stamprath June 25, 2023

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_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 26, 2023

But now we will try a Database connection in resources.

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

Suggest an answer

Log in or Sign up to answer