How to use JDBC to quickly process large data

Source: Internet
Author: User
Keywords NBSP; 1000 JDBC how to use large data

In the internship, to deal with a table with 2.04 million records, because the record is taken from the Internet, there are some less ideal words, such as some words mixed with special characters, punctuation, or some words are simply punctuation and so on. I write this program is to find out these unsatisfactory words, can be modified on the modified, no need to modify the direct deletion.

[java]&http://www.aliyun.com/zixun/aggregation/37954.html ">nbsp;view plaincopy for (int i=0;i<205;i++) {                           String sql= "SELECT * from Cat_keyword the ORDER by ID limit" +i*10001+ ", 10000";                  String best= "SELECT * from Cat_keyword where id>= (select ID from Cat_keyword ORDER by ID Limit" +i*10001+ ", 1) limit 10000";                  Rs=stmt.executequery (best);              Go.filt (RS); } 

The efficiency of the first SQL statement is obviously less than that of the second one.

String best= "SELECT * from Cat_keyword where id>= (select ID from Cat_keyword ORDER by ID Limit" +i*10001+ ", 1) limit 10000";

This SQL statement is especially useful in situations where the offset is particularly large, because the 200多万条 record, in the middle and late, this offset is very large, using an optimized SQL statement to the late is getting slower.

In addition, the value of limit is also very elegant, I tried 1000,10000,70000,100000, and finally found that limit take 10000 of the time is the fastest, and the size of the data, computer and database memory allocation has a certain relationship, It can be discussed in detail according to the specific situation. I'm here just to mention the point that needs attention and can be optimized.

What I really want to say is another solution that I want to use JDBC

Let me say a few words about the construction of this program, first of all, is an implementation of the need to delete and modify the word inserted into another table method

[Java] view plaincopy //will find the offending words into the cat_garbage table      public void insert ( RESULTSET RS)  throws SQLException     {         connection  conn=null;          statement stmt=null;         try{                        conn=good.getconnection ();                  Stmt = conn.createstatement (Resultset.type_scroll_ insensitive                     ,  resultset.concur_updatable );                  string query= "Select * from cat_ Garbage where id= ' "+rs.getint (" id ") +" ";                  Resultset rst=stmt.executequery (query);                  if (!rst.next ())                   {                      string sql1= "Insert into cat_garbage (Id,cid, Name,keyword)  values (' "+rs.getint (" id ") +" ', ' "+rs.getint (" CID ") +" ', ' "+rs.getstring (" name ") +" ', ' "+ rs.getstring ("keyword") + "')";                      Stmt.executeupdate (SQL1);                 }          }          catch (sqlexception| classnotfoundexception| Ioexception e)           {              E.printstacktrace ( );         }          finally          {              if (stmt!= null)                    Stmt.close ();                     if (conn!=  null)                    conn.close ();          }     } 

And then a way to fix the word

[Java] view plaincopy public void Modify (ResultSet rs,string str,string reg) throws SQLException {
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.