How to track progress of a large SQL Server query with JDBC?-CodePudding

My JavaFX application has a process that loads a very large amount of data from a single SQL Server query at startup. This can often take several minutes to complete.

My goal is to include a ProgressBar to show the current/total rows that have already been retrieved, but I can not find a way to retrieve the progress of a SELECT statement with JDBC.

Right now, I am just executing the one query to get a single ResultSet in return when all data has been returned by the server.

Is it possible to "listen" to the progress of a query in this way? Or some way to limit the query (ie: SELECT TOP 100) in a loop, picking up where the last iteration left off, until all results have been retrieved?

CodePudding user response：

The anwser given by @Gatusko is good but does not solve your problem...

Actually you will see everywhere that progress bar is less and less used, because it is not adapted for SOA architectures (distant calls of processes).

The trick that replace the progress bar is a circle thats turns eternally until the result appear ! Which such a component, there is no needs to requests the state of progress of the process, that you really never can have in a RDBMS !

CodePudding user response：

With JDBC there is no way to see what is the status of the query because the query is being executed by the database. So, the JDBC driver sends the query to database and waits for a response. JDBC is the one that get the results, it doesn't track the complexity of the query or the status of one. My suggestion for solving this problem is to get the total count of documents of being processed, then make a batch of queries. That's the way I made for a large set of results. Divide and conquer.

CodePudding user response：

First do a select count(*) to know the total number of rows.

Instead of getting a single ResultSet, invoke the use of a cursor, updating your progress meter as you process rows:

conn.setAutoCommit(false); // you must turn auto commit off
Statement stmt = conn.createStatement();

stmt.setFetchSize(1000); // Turn use of the cursor on
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
int count = 0;
while (rs.next()) {
    // process row
    // update progress meter every 10000 rows
    if (  count % 10000 == 0) {
        float progress = 100f * count / totalRows;
        // display progress
    } 
}