MYSQLJDBC quick query and response method and quick return mechanism implementation _ MySQL

MYSQLJDBC quick query and response method and quick return mechanism implementation bitsCN.com

Oracle's fast return mechanism has been very tangled. although there are many result sets, it can quickly display the first result. although it can be done through the MYSQl client, it cannot be achieved through JDBC.

It took more than an hour today to solve this problem. I hope to have a reference for a wide range of Java friends when dealing with databases.

Reason:

By adding the-q parameter to the command line client, you can quickly respond to a query.
For example, if the result set is select * from t1 of tens of millions, it takes 20 seconds to complete the result set. the-q parameter shows that the first row only takes less than 1 second.
However, the above results cannot be achieved through jdbc query, no matter how you adjust the URL parameters.

Process:
The explanation of the-q parameter is as follows:
If you have problems due to insufficient memory for large result sets,
Use the -- quick option. This forces mysql to retrieve results
From the server a row at a time rather than retrieving the entire result set
And buffering it in memory before displaying it. This is done by returning
The result set using the mysql_use_result () c api function in the client/server
Library rather than mysql_store_result ().

Visible to achieve rapid response.

View the mysql_use_result () function. this is the c api. if it is developed through C, you can use this function.

What about JAVA?

There is no benefit in finding functions in the JDBC specification. SetFetchSize () does not appear to be effective. in actual tests, there is no performance improvement.

Searching for JDBC mysql_use_result has an unexpected result.

The following content is found in the JDBC, com. MYSQL. jdbc. Statement interface of mysql:
Abstract public void disableStreamingResults () throws SQLException

Resets this statements fetch size and result set type to the values they
Had before enableStreamingResults () was called.

Abstract public void enableStreamingResults () throws SQLException

Workaround for containers that 'check' for sane values of Statement. setFetchSize ()
So that applications can use the Java variant of libmysql's mysql_use_result () behavior.


MySQL provided its own quick response implementation. Adjust test code

Stmt = (com. mysql. jdbc. Statement) con. createStatement ();
Stmt. setFetchSize (1 );
//
// Stream opening method return mechanism
Stmt. enableStreamingResults ();

The expected results have appeared. The first line of data is quickly realized, and the time is less than 1 second.

Conclusion:
MySQL provides unique functions in its own JDBC driver to achieve fast query response,
In particular, the result set is very large or long, and the user wants to see the first result as soon as possible.

From: Laizhu's column

BitsCN.com