Reduce connection to the database and increase request efficiency

Work system code optimization record:

When we look at the details of the work, we need to divide the individual hours into a project and show each month.

12 months a year, a person has multiple projects.

The initial code is just for functionality, so the looping operations in the code, that is, each item gets the hourly statistics for that user in the database each month.

This results in multiple requests to the database, the efficiency is very low. Almost a time to query to 2500ms. That's no way to be sure.

Optimization: Optimize the use of each project to establish only one connection to the database. Called with a stored procedure.

This greatly reduces the number of connections to the database.

The following code is not pre-optimized.

Work system: View hours details, (4 items for example) The original code requests the database 2*12*4+1=97 times. After the optimized data, request the database 5 times (first query all the items, then each item goes to query once. /* *  query items in the monthly submitted hours  *///  query each person's project idlist<workdetail> workdetaillist =  Workdetailmanager.getprojectidbyuser (userId);//First time list<project> projectlist = new  Arraylist<> ();for  (workdetail workdetail : workdetaillist)  {//Cycle 4 times integer  Projectid = workdetail.getprojectid (); Project project = projectmanager.get (ProjectID);//  monthly to inquire for  (int i = 0 ;  i < countmonth; i++)  {//Cycle 12 times double reportcount = new double ("0"); String workday = string.valueof (year)  + months[i]; Workdetailquery query = new workdetailquery (); query.setstate (query.setProjectId); ProjectID) Query.setuserid (userId);//  the day before the end of the month, the date of the survey will prevail. Integer befornowday = integer.valueof (DATESTR)  - 1;if  (Workday.equals (datestr.substring (0, 6)))  {query.setstartdate (workDay +  "01"); Query.setenddate (Befornowday.tostring ()); Query.setworktype ("FUL");integer fulcount1 =  Workdetailmanager.count (query);//queries are half-day or full-time    monthly cycle Query.setworktype ("PAR"); integer parcount1  = workdetailmanager.count (query);//queries are half-day or full-time    monthly cycle reportcount =  ( Parcount1.doublevalue ()  / 2)  + fulcount1.doublevalue ();}  else {query.setworkday (integer.valueof (WorkDay)); Query.setworktype ("FUL"); Integer fulcount  = workdetailmanager.count (query); Query.setworktype ("PAR");integer parcount =  Workdetailmanager.count (query);reportcount =  (Parcount.doublevalue ()  / 2)  +  Fulcount.doublevalue ();} if  (reportcount == 0.0)  {reportcount = null;}   saved to if  per month ("Months[i")  {project.setjancount (Reportcount);}  else if  ("" ". Equals (Months[i]))  {project.setfebcount (Reportcount);}  else if  ("Months[i")  {project.setmarcount (Reportcount);}  else if  ("Months[i")  {project.setaprcount (Reportcount);}  else if  ("". Equals (Months[i]))  {project.setmaycount (Reportcount);}  else if  ("". Equals (Months[i]))  {project.setjuncount (Reportcount);}  else if  ("". Equals (Months[i]))  {project.setjulcount (Reportcount);}  else if  ("". Equals (Months[i]))  {project.setaugcount (Reportcount);}  else if  ("Months[i")  {project.setseptcount (Reportcount);}  else if  ("Ten". Equals (Months[i])  {project.setoctcount (reportcount);}  else if  ("One". Equals (Months[i]))  {project.setnovcount (Reportcount);}  else if  ("n". Equals (Months[i])  {project.setdeccount (Reportcount);}} Projectlist.add (project);} 

After the optimization speed is greatly improved, the request time is generally 180ms, meet the actual demand.

Summary: In a report query, you must reduce the number of times the database is requested. Try to use multiple table queries or stored procedure calls.

This article is from the "Jianbo" blog, make sure to keep this source http://jianboli.blog.51cto.com/12075002/1925239

Reduce connection to the database and increase request efficiency