Shulou(Shulou.com)06/01 Report--

Man-hour system code optimization record:

When looking at the details of working hours, we need to show individual working hours on a monthly basis.

There are 12 months in a year, and one person has multiple projects.

The initial code is only for the purpose of implementing the function, so the loop operation is performed in the code, that is, each project goes to the database every month to get the user's man-hour statistics.

This results in multiple requests to the database, which is very inefficient. It takes almost once to query the time it takes to 2500ms. This is definitely not going to work.

Optimization: optimization uses each project to establish a connection to the database only once. The stored procedure is used for the call.

This greatly reduces the number of database connections.

The following is the code without optimization.

Man-hour system: check the details of man-hours (4 projects as examples). The original code requests the database 2 "12" 4 "1" 97 times. For the optimized data, request the database 5 times (first query all items, and then query each project once. ) / * * query project hours submitted each month * / / query everyone's project id List workDetailList = workDetailManager.getProjectIdByUser (userId); / / first time List projectList = new ArrayList () For (WorkDetail workDetail: workDetailList) {/ / cycle 4 times Integer projectId = workDetail.getProjectId (); Project project = projectManager.get (projectId); / / query for every month (int I = 0; I < countMonth Double reportCount = new Double ("0"); String workDay = String.valueOf (year) + months [I]; WorkDetailQuery query = new WorkDetailQuery (); query.setState (10) Query.setProjectId (projectId); query.setUserId (userId); / / if the current month has not reached the end of the month, the count to the day before that day shall 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); / / whether the query is half-day or full-day monthly cycle query.setWorkType ("PAR") Integer parCount1 = workDetailManager.count (query); / / whether the query is half-day or full-day 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 } / / Save to monthly if ("01" .equals (months[ I]) {project.setJanCount (reportCount) } else if ("02" .equals (months[ I]) {project.setFebCount (reportCount);} else if ("03" .equals (months[ I])) {project.setMarCount (reportCount) } else if ("04" .equals (months[ I]) {project.setAprCount (reportCount);} else if ("05" .equals (months[ I])) {project.setMayCount (reportCount) } else if ("06" .equals (months[ I]) {project.setJunCount (reportCount);} else if ("07" .equals (months[ I])) {project.setJulCount (reportCount) } else if ("08" .equals (Months[ I]) {project.setAugCount (reportCount);} else if ("09" .equals (Months[ I])) {project.setSeptCount (reportCount) } else if ("10" .equals (months [I])) {project.setOctCount (reportCount);} else if ("11" .equals (months[ I])) {project.setNovCount (reportCount) } else if ("12" .equals (months[ I])) {project.setDecCount (reportCount);}} projectList.add (project);}

After optimization, the speed is greatly improved, and the request time is generally 180ms, which meets the actual needs.

Summary: the number of requests to the database must be reduced in the report query. Try to use multi-table queries or stored procedure calls.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.