Skip to end of metadata
Go to start of metadata

Purpose

Ways to improve performance issues with jConnect driver included in the SDK for SAP ASE.

Overview

ASE options and jConnect connection string options you can try to improve performance with jconn4.jar.

ASE options to modify

 

On the ASE , run this command and you will see this:

1 > select * from spt_mda where mdinfo='MAXBATCHPARAMS'
2 > go
mdinfo querytype query mdaver_start mdaver_end srvver_start srvver_end
------------ ---------- ------------ -----------
MAXBATCHPARAMS 5 255 6 9 0 12000
MAXBATCHPARAMS 5 2048 6 9 12500 1570019 
MAXBATCHPARAMS 5 32767 6 9 1570020 -1

To fix the issue, run the following commands:
delete from spt_mda where mdinfo = 'MAXBATCHPARAMS' and srvver_start = 1570020 and srvver_end = -1
go
update spt_mda set srvver_end = -1 where mdinfo = 'MAXBATCHPARAMS' and srvver_start = 12500 and srvver_end = 1570019
go

These are the values from my current ASE that I have modified.

1 > select * from spt_mda where mdinfo='MAXBATCHPARAMS'
2 > go
mdinfo querytype query mdaver_start mdaver_end srvver_start srvver_end
------------ ---------- ------------ -----------
MAXBATCHPARAMS 5 255 6 9 0 12000
MAXBATCHPARAMS 5 2048 6 9 12500 -1

Another way to get around the batch execution:

use master
go
update spt_mda set query='1' where mdinfo='EXECPARAMETERIZEDBATCHINLOOP‘
go

jConnect connection strings options to modify

DYNAMIC_PREPARE=false (default true)


EARLY_BATCH_READ_THRESHOLD=1000 or some other number (default -1)

Reduce the size of the prepared statement batch in the code
Turn off "done in proc" tokens on the ASE using sp_configure "send doneinproc tokens", 0  or set the connection property IGNORE_DONE_IN_PROC=true

SEND_BATCH_IMMEDIATE= true (default false)

Performance issues with jConnect connection string options to try with known issues:
IS_CLOSED_TEST=Internal (default null)


LITERAL_PARAMS= (default false)

Related Documents

jConnect connection string options

Related SAP Notes/KBAs

SAP Note 2215962: jConnect and ASE: When using executeBatch on large CallableStatement batch, processing of the command and results is taking a long time

SAP KBA 2040686: SAP SDK jConnect Insert Text using PreparedStatement when DYNAMIC_PREPARE false and LITERAL_PARAMS true give slow performance

SAP Note 2240693: Jconnect 7 - application hanging in executeBatch

SAP Note 2243469: Large batches of JDBC prepared statements may appear to hang in SAP ASE

__________________________________________________________________________________________________________

  • No labels