How JDBC quickly fetches large amounts of data from PostgreSQL without bursting into memory

Objective:

Recently do data synchronization, need to get data from PostgreSQL, found that once the data is more, then the speed of reading is very slow, and memory consumption is very much &GC not fall.

Code Sample:

In order to facilitate the explanation, the following writing the case code, from B2c_order to obtain data, this data table about 6G.

 PackageCom.synchro;ImportJava.sql.*;/*** Created by Qiu.li on 2015/10/16.*/ Public classTest { Public Static voidMain (string[] args) {Connection conn=NULL; Try{class.forname ("Org.postgresql.Driver"); Conn= Drivermanager.getconnection ("Jdbc:postgresql://***.qunar.com:5432/database", "username", "password");String sql = "SELECT * FROM Mirror.b2c_order"; PreparedStatement PS=conn.preparestatement (SQL);Ps.setmaxrows (1000); ResultSet RS=Ps.executequery (); inti = 0;  while(Rs.next ()) {i++; if(i% 100 = = 0) {System.out.println (i); }            }        } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }    }}

Phenomenon:

Execute code in idea, find the card is dead and consume a lot of memory

Solution:

Then I decided to start debugging, tracking code:

The first step, I found it was stuck in the execution of the ExecuteQuery method.

The second step, which is stuck in the execution of the Abstractjdbc2statement.executewithflags method

The third step, continue tracking, and view on the network may cause is related to setting the Fetchsize parameter, so continue tracking

The fourth step, Sendquery,sendonequery method, found the problem here, fortunately the code is not too much, I have all posted out:

        BooleanUseportal = (Flags & queryexecutor.query_forward_cursor)! = 0 &&!noresults &&!nometa && fetc Hsize > 0 &&!describeonly;//Work out how many rows to the fetch in the this pass.        introws; if(noresults) {rows= 1;//We ' re discarding any results anyway, so limit data transfer to a minimum        }        Else if(!useportal) {Rows= MaxRows;//Not using a portal--fetchsize is irrelevant        }        Else if(MaxRows! = 0 && fetchsize >maxRows) {Rows= MaxRows;//fetchsize > MaxRows, use MaxRows (Nb:fetchsize cannot is 0 if useportal = True)        }        Else{rows= Fetchsize;//maxRows > Fetchsize}

Visible is the Useportal parameter in the number of rows that affect JDBC to fetch data from the database, for a specific reason reference: http://m.blog.csdn.net/blog/itjin45/42004447#

BooleanUseportal = (Flags & queryexecutor.query_forward_cursor)! = 0 &&!noresults &&!nometa && fetc Hsize > 0 &&!describeonly; So many conditions, as long as one is established, the fetchsize fails:!noresults indicates that this SQL does not need to return any results, which is definitely equal to true, because all select will require a return result!Nometa indicates that this SQL does not need to return metadata, which is definitely equal to true, because select requires the return of metadata for subsequent resultset.get use!Fetchsize is more than 0, this does not say, nature is true!Describeonly, this is only a statement such as DESC table, it will be false, for Select, is True Then, the only test can cause useportal false is the reason that flags&queryexecutor.query_forward_cursor this value equals 0. Keep going up and see when the flags won't be executed .= Flags |queryexecutor.query_forward_cursor This code, because only this code has not been executed, Causes the above condition to be false and then navigates the code to the Execute method of the Abstractjdbc2statement class://Enable cursor-based resultset if possible.        if(Fetchsize > 0 &&!wantsscrollableresultset () &&!connection.getautocommit () &&!Wantsholdableresultset ()) Flags|=Queryexecutor.query_forward_cursor; Where: the Wantsholdableresultset () code returns the false directly, so, regardless of this, then, either Wantsscrollableresultset () returns True, or Connection.getautocommit () returns true to cause flags to not contain queryexecutor.query_forward_ CURSOR, which causes Fetchsize to fail Wantsscrollableresultset () The code for this method is:protected BooleanWantsscrollableresultset () {returnResultsetType! =resultset.type_forward_only; At this point, the problem has been finally fixed to:1, If the connection is automatically committed, then Fetchsize will fail .2, if the statement is not type_forward_only, then, Fetchsize will also expire

Conclusion

= false, and, statement is forward_only: Conn.setautocommit (false); Final Statement Statement = conn.createstatement (resultset.type_forward_only, Resultset.fetch_forward); Conn.createstatement () with no parameters, the default is Type_forward_only so, in general, if you want fetchsize to take effect, you must set Autocommit to Flase, that is, you need to manually manage the transaction. 

Then modify the code as follows:

 PackageCom.synchro;ImportJava.sql.*;/*** Created by Qiu.li on 2015/10/16.*/ Public classTest { Public Static voidMain (string[] args) {Connection conn=NULL; Try{class.forname ("Org.postgresql.Driver"); Conn= Drivermanager.getconnection ("Jdbc:postgresql://l-tdata2.tkt.cn6.qunar.com:5432/log_analysis", "Tkt_data_dev", " 23eadc16-a4e4-418c-b18a-ccb2a6b9d587 "); Conn.setautocommit ( false); Not all databases are applicable, such as hive is not supported, Orcle does not needString sql = "SELECT * FROM Mirror.b2c_order"; PreparedStatement PS=conn.preparestatement (SQL); ps.setfetchsize ( 1000); Get 10,000 records per entry//ps.setmaxrows (+);ResultSet rs =Ps.executequery (); inti = 0;  while(Rs.next ()) {i++; if(i% 100 = = 0) {System.out.println (i); }            }        } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }    }}

This time again, we found that there was no card at all.

Reference documents

Https://jdbc.postgresql.org/documentation/head/query.html

http://m.blog.csdn.net/blog/itjin45/42004447#

How JDBC quickly fetches large amounts of data from PostgreSQL without bursting into memory