JDBC Large query optimisation

Hi,
We recover a large amount of data from an external database (SQLServer, about 1 million rows in JDBC).
However, we have treatment time issue.
This process takes more than 30 minutes whereas on a "classic" SQL Server Management Studio type request takes less than a minute.

While searching on the internet, I came across this article: http://makejavafaster.blogspot.com/2015/06/jdbc-fetch-size-performance.html
It explains how in Java, we can tweak the FetchSize parameter of the JDBC driver to optimize this kind of process.

Is there a way to access this JDBC parameter through the EnsLib.SQL.OutboundAdapter adapter?

If not, how do you deal with this kind of situation of large volumetry?

Code used in the EnsLib.SQL.OutboundAdapter :

Method GetResultSetView(pRequest As Ens.StringRequest, Output pResponse As Ens.StringResponse) As %Status
{
    
    set tStatus = $$$OK

    //Set ..Adapter.StatementAttrs = "fetchSize:200"  <-- Make no difference
    //Set ..Adapter.StatementAttrs = "Type=TYPE_FORWARD_ONLY" <-- Make no difference

    $$$ThrowOnError(..Adapter.SetAutoCommit(0))

    
    try{
        
        set pResponse = ##class(Ens.StringResponse).%New()
        set sqlGetView = "SELECT * from TableOfMillionsLignes"
        
        
        Set pSnap = ##class(EnsLib.SQL.Snapshot).%New()
        
        //dimension du snapshot
        // -1 = Max
        set pSnap.MaxRowsToGet = -1
        
        $$$ThrowOnError(..Adapter.ExecuteQuery(.pRS,sqlGetView))    
        
        //Most of the time is spend here
        $$$ThrowOnError(pSnap.ImportFromResultSet(pRS,0))

        
        $$$ThrowOnError(pSnap.%Save())
        
    
        set pResponse.StringValue = pSnap.%GblRef   
    }
    catch exp
    {
        Set tStatus = exp.AsStatus()
    }
    Quit tStatus
}

  • 0
  • 1
  • 206
  • 6
  • 1

Answers

You can set this parameter in JDBC connection.
Add to the Properties of the SQL Server gateway connection:

FetchSize=1000; 

Comments

In addition to setting FetchSize, we can also improve the speed with an optimization to how EnsLib.SQL.GatewayResultSet fetches rows for large result sets.

This optimization is planned to be included in a future product version, but it is possible to do this in current versions with custom code. What version of Ensemble are you using?

Guillaume, do you know who the InterSystems Sales Engineer is for your company? We should discuss this by email and we can get you some sample code.

Hi Marc,

I'm intereded by your sample code for this kind of optimisation even if we have to use a different EnsLib.SQL.GatewayResultSet.
Furthermore, we are currently looking for the same kind of optimisation in the other way, insert in JDBC batch mode. (https://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm)
I get in touch with our Sales Engineer and we will continu to discuss this by email.

When we will have improvement, i'll update this theard.

Any word on this? I too would be very curious to see how batch processing via JDBC could be implemented in an Ensemble operation. I have one where I am calling a number of stored procedures and it crawls. Having a way to push this off onto the DB end to be done faster would be awesome.

Thanks!