Home  >  Article  >  Database  >  What should I do if there is too much data in SQL query and memory overflows?

What should I do if there is too much data in SQL query and memory overflows?

(*-*)浩
(*-*)浩Original
2019-10-28 09:53:497068browse

Normally speaking, this situation generally does not occur, but there is no guarantee. This situation may occasionally occur. The solution is as follows:

What should I do if there is too much data in SQL query and memory overflows?

Use paging query statements. (Recommended learning: mysql learning)

Because paging query only queries a small amount of data each time, it does not take up too much memory, and the amount of data is large. Sometimes, paging queries will save some time.

String sql = " SELECT uid,uname  FROM t_user LIMIT ?,? " ;
        PreparedStatement  ps = con.prepareStatement(sql) ;
        int pageSize = 10000; 
        int pageId = 0; 
        do { 
            pst.setInt(1, pageId * pageSize); 
            pst.setInt(2, pageSize); 
            ResultSet rs = pst.executeQuery();

            boolean isEmpty = true; 
            while (rs.next()) { 
                 isEmpty = false; 
                 id = rs.getInt(1); 
                 name = rs.getString(2); 
            } 
            if (isEmpty) { 
                break; 
            } 
            pageId++; 
        } while (true); 
        con.close(); 
    } catch (SQLException e) { 
        e.printStackTrace(); 
    }

Add url parameter configuration

Add two parameters to the jdbc URL and it will be OK, successfully solving the memory overflow problem.

"jdbc:mysql://localhost:3306/db3?useCursorFetch=true&defaultFetchSize=100";

(Explain Fetch, when we execute a SQL query statement, we need to open a cursor on both the client and the server, and apply for a piece of memory space respectively to store the query data. A buffer. The number of pieces of data stored in this memory area is determined by fetchsize. At the same time, each network packet will transmit fetchsize records to the client)

The above is the detailed content of What should I do if there is too much data in SQL query and memory overflows?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article:db2 modify field lengthNext article:db2 modify field length