Too much data to be queried. Slow page response. cache solution (Redis, H2), redish2

Too much data to be queried. Slow page response. cache solution (Redis, H2), redish2
 Problem: The original system query interface does not support paging and cannot be added to paging support. As a result, too much data is queried by Ajax. When more than records are returned, the response is extremely slow. The query function does not require real-time data, the page response speed is extremely slow and the experience is poor. After investigation, the response time is slow because the data volume is too large, so the Servlet output stream is directly written to the page (output. write (buffer, 0, B ));Demand change:To speed up the corresponding page speed, the page must have a paging function, you can change the original interface in this case there are two solutions: 1. Front-end js paging 2. Add third-party cache components (memory database, etc)Disadvantages of the two solutions:The first solution solves the paging problem, but because the queried data volume is large, the response time for querying hundreds of thousands of data records that contain dozens of megabytes or even megabytes is still slow. If you use js to pagination on the front end, the query response is too slow and cannot be solved, the page load is too heavy, and the experience is still poor. The second solution is relatively more complicated than the first solution, and the encoding volume increases. If the Real-Time query processing is complicated, it is not impossible, you must consider clearing the cached data to ensure the validity of the data. After weighing the pros and cons, we decided to discard the first solution and introduce the memory database to solve the problem. Currently, there are many types of memory databases, such as redis, H2, and HSQLDB. Among the three memory data types, redis is different from the other two, redis is a lightweight key-value-based nosql database implemented in C language, while H2 and HSQLDB are lightweight relational databases implemented in java; therefore, I want to select one from nosql and relational databases. To achieve this function, the performance determines which database to use. H2 has a web operation interface than HSQLDB, and H2 is updated more frequently than HSQLDB. Therefore, select H2 and redis here.I. redis caches data to implement the paging function.Redis implementation is not very friendly. Because redis is implemented in C language, redis cannot be embedded into Java code to start the redis server (I did not find a solution). In addition, we need to start the redis server, redis needs to be restarted to implement paging;Ideas:Use the zset (Sorted-Sets) sequence set and Data Type of redis to store data. SeeesionID + data type is treated as key, score is the order of interface query, and value is the assembled json data, you can clear data based on the Session failure or the expiration time of the redis key. The implementation code is as follows:/*** Process the page, find out that the data is filled with redis, and then query by page from redis, each query overwrites data in redis * @ param rtList * @ param callback * @ param response * @ throws IOException * @ linx * @ Date 2014-11-10 */public void processPager (List <CSTRTData> rtList, string callback, HttpServletRequest request, HttpServletResponse response, String top, String skip) throws IOException {Jedis jedis = new Jedis ("127.0.0.1"); OutputStream output = null; Set <Strin G> setValues = new HashSet <String> (); String dataList = ""; String keyName = request. getSession (). getId () + "troubleCode"; if (top = null) {top = "1";} if (skip = null) {skip = "0 ";} if (rtList. size ()> 0) {for (int x = 0; x <rtList. size (); x ++) {JSONObject temp = JSONObject. fromObject (rtList. get (x); long recvTime = temp. getLong ("recvTime"); long obdTime = temp. getLong ("obdTime"); temp. remove ("recvTime "); Temp. put ("recvTime", DateUtil. formatYYYYMMDDHHMMSS (recvTime); temp. remove ("obdTime"); temp. put ("obdTime", DateUtil. formatYYYYMMDDHHMMSS (obdTime); jedis. zadd (keyName, x, temp. toString ();} int start = Integer. valueOf (skip); // start line number. the start line number on the first page is 0, and the start line on the second page is the previous page skip + page size int end = Integer. valueOf (top) + Integer. valueOf (skip)-1; // end row top = page size, end row number = skip + top-1 // read the data from redis from the start and end numbers. setValues = jedis. Zrange (keyName, start, end); // Set <String> setValues2 = jedis. zrevrange ("hackers", 0,-1); // traverses the assembled json for (Iterator iter = setValues. iterator (); iter. hasNext ();) {if (iter. hasNext () {dataList + = (String) iter. next () + ",";} else {dataList + = (String) iter. next () ;}} else {jedis. del (keyName) ;}// json string dataList = callback + "({" + "\" d \": {"+" \ "results \": ["+ dataList +"], "+ "\" _ Count \ ": \" "+ rtList. size () + "\" "+"} "+"}) "; // convert it to the input stream InputStream input = new ByteArrayInputStream (dataList. getBytes (); output = response. getOutputStream (); int B = 0; byte [] buffer = new byte [1024]; // write to the page while (B = input. read (buffer ))! =-1) {output. write (buffer, 0, B); output. flush ();} if (output! = Null) output. close (); if (input! = Null) input. close ();} 2. H2Cache data to implement the paging function.The advantage of H2 is that it can be embedded in Java code to start the H2 database. Since it is also a relational database, it is no different in use from our commonly used database and uses its memory mode, you don't have to worry about its performance.Ideas:Add a table to H2, convert each piece of data to JSON, and store the data in the added table in order. Then, you can use paging SQL statements that we often use to query, on the first page every day, call the original interface to re-overwrite the data to the H2 table. You can clear table data based on Session failures. The implementation code is as follows: public class H2Opertion {public static void createTable (Connection conn) throws Exception {Statement stmt = conn. createStatement (); stmt.exe cuteUpdate ("create table temp (id int primary key, name varchar (2000);") ;}public static Connection open () throws Exception {Class. forName ("org. h2.Driver "); Connection conn = DriverManager. getConnection ("jdbc: h2 :. /h2db/demo "," sa "," "); return conn;} public static void insertData (Connection conn, List <String> list) throws Exception {Statement stmt = conn. createStatement (); for (int I = 0; I <list. size (); I ++) {stmt.exe cuteUpdate ("insert into temp VALUES (" + I + ", '" + list. get (I) + "');") ;}} public static List query (Connection conn, int start, int end) throws Exception {Statement stmt = conn. createStatement (); // query ResultSet rs = stmt by page. executeQuery ("select t2. * from (select rownum r, t1. * from temp t1 where rownum <=" + end + ") t2 where t2.r>" + start); while (rs. next () {System. out. println (rs. getInt ("ID") + "," + rs. getString ("NAME");} return null;} public static void deleteTable (Connection conn) throws Exception {Statement stmt = conn. createStatement (); stmt.exe cuteUpdate ("delete temp");} public static void main (String [] args) {H2Server server = new H2Server (); server. startServer (); Connection conn = null; try {conn = H2Opertion. open (); try {H2Opertion. createTable (conn); // create table} catch (Exception e) {e. printStackTrace (); H2Opertion. log. error ("table exists", e);} H2Opertion.log.info ("Delete table data"); H2Opertion. deleteTable (conn); // Delete table data // List of data queried by the simulation interface <String> listData = new ArrayList <String> (); for (int I = 0; I <30; I ++) {listData. add ("content" + I) ;}// insert data to the H2Opertion table in H2. insertData (conn, listData );

// Query the data in H2 table by page, with 10-20 records

H2Opertion. query (conn, 10, 20);} catch (Exception e) {H2Opertion. log. error (e);} finally {try {conn. close ();} catch (SQLException e) {H2Opertion. log. error (e );}}}}

First Article address: Solinx

Http://www.solinx.co/archives/67