java - JDBC with MySQL really slow, why -


i have problem slow connection between java code , mysql remote database when use multiple query.

this code

arraylist<server_log> ar =server_log_utilities.getby2dates(cmb_date.getselecteditem() + "", cmb_date2.getselecteditem() + "");   (int c = 0; c < ar.size(); c++) {      server_log sl = ar.get(c);       string username = user_utilities.getusername(sl.getuser() + "");      string row[] = {sl.getdate(), sl.gettime(), username, sl.getreff(), sl.getdescription()};    } 

but user code data load fast

arraylist<server_log> ar =server_log_utilities.getby2dates(cmb_date.getselecteditem() + "", cmb_date2.getselecteditem() + "");   (int c = 0; c < ar.size(); c++) {      server_log sl = ar.get(c);      string row[] = {sl.getdate(), sl.gettime(),  sl.getreff(), sl.getdescription()};   } 

this user_utilities.getusername(sl.getuser() + ""); method

 public static string getusername(string id) {          string username="";         try {             connection con = new dbcon().getconnection();                 resultset rst = dbhandle.getdata(con, "select username user  id='" + id + "'");                 while (rst.next()) {                     username =rst.getstring(1);                  }            con.close();         } catch (exception ex) {             logger.getlogger(user_utilities.class.getname()).log(level.severe, null, ex);          }         return username;     } 

server_log_utilities.getby2dates(cmb_date.getselecteditem() + "", cmb_date2.getselecteditem() + ""); method

 public static arraylist getby2dates(string date1, string date2) {          try {             ar = new arraylist<>();             connection con = new dbcon().getconnection();             resultset rst = dbhandle.getdata(con, "select * server_log date between '" + date1 + "' , '" + date2 + "' order  `date`");             while (rst.next()) {                 server_log ci = new server_log();                 ci.setid(rst.getint(1));                 ci.setdate(rst.getstring(2));                 ci.settime(rst.getstring(3));                 ci.setreff(rst.getstring(4));                 ci.setdescription(rst.getstring(5));                 ci.setuser(rst.getint(6));                 ar.add(ci);             }             con.close();          } catch (exception ex) {             logger.getlogger(student_utilities.class.getname()).log(level.severe, null, ex);          }         return ar;     } 

when accessing remote database, on slow link, number of sql statements executed very important.

this why jdbc api support concepts statement batching.

in case, you're calling getusername every record in ar. consider ways reduce number of calls.

example 1: if user same, or few users generating log entries, caching user names eliminate redundant lookup.

example 2: rather looking user in client, modify server_log_utilities.getby2dates add join user table. way, no turn-arounds database needed.

example 3: instead of calling getusername individually in loop, collect user ids, , lookup names in batch. use either jdbc batch of multiple select statements, or use single statement userid in (?,?,?,?,...).


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -