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

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

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!