Search code examples
sql-serverjdbcsybase

How to handle JDBC SQL Server, Sybase for long running and high memory consumption process

I have a batch job querying Sybase and SQL Server databases. This batch job can run for up to 1 day or more. We are running this on a small set of data and no error so far in terms of connection timeout. My questions are

  1. How to handle this long running process? Should I configure a reconnect period so that the connection gets closed and reopened?

  2. How to handle the resultset when it can return back to the client with 1 million records?

EDIT #1:

This sounds like a general question for jdbc but it's not because each database provider has their own options such as fetching size. It's very much up to each provider to support this or not. If Sybase does not support this, it means it will load all results into memory at once.

Solution

  • According to https://docs.oracle.com/cd/E13222_01/wls/docs90/jdbc_drivers/sybase.html.

    We can setFetchSize() to determine the maximum of records to be kept in the memory at one time. If you have enough memory, you can set it to 0. Hence, we can limit the memory allowance for each fetching so that it doesn't blow up our memory.