I have three mysql tables:
我有三个mysql表:
- Customer
- 客户
- Complaint
- 投诉
- Recovery
- 复苏
Problem statement: I have 50000+ customers, I want to search a customer against whom there's recovery request.
问题陈述:我有50000多个客户，我想搜索一个有恢复请求的客户。
The problem is pretty solved, and working correctly, but the problem is the query takes upto 30 minutes or long.
这个问题已经很好地解决了，并且可以正确地工作，但是问题是查询需要长达30分钟或更长时间。
Please guide me through how do I reduce the time that is taken by the query, or how can I fasten the search?
请指导我如何减少查询占用的时间，或者如何加强搜索?
Here's the quickhelp ERD of the mysql tables: 
下面是mysql表的quickhelp ERD:
Again I make it clear, the problem is solved & works fine, but the query takes too long upto 30 minutes due to large amount of customer and complaints in the database.
我再次明确表示，问题已经解决并正常工作，但是由于数据库中有大量的客户和投诉，查询时间太长，达30分钟。
Is there any technique through which to reduce the query time, or fasten the search?
有没有什么技术可以减少查询时间，或者是固定搜索?
JAVA CODE I have two methods:
我有两种方法:
- getMultiSelectionCustomersProfiles(....)
- getMultiSelectionCustomersProfiles(....)
- searchRecoveryRequest( String customerRegistrationCode )
- searchRecoveryRequest(字符串customerRegistrationCode)
Note: method2 is called in method1
注意:method1中调用method2
Method1:
Method1:
public ArrayList<CustomerRegistrationBean> getMultiSelectionCustomersProfiles(int selectedCountryId,
int selectedZondId, int selectedRegionId, int selectedCityId, int selectedAreaId,
int[] selectedMarkets, int selectedMOID, String searchStr, String userStatus,
int selectedCatID, int[] selectedSubCategoryIds, int[] selectedDeprtIds) {
//System.out.println("getSelectedCustomersProfiles calles :" + searchStr);
// System.out.print("getSelected Customers");
// System.out.print("Market ID Model :" + marketId);4
ConnectionHandler conHandler = ConnectionHandler.getConnectionHandler();
Connection con = conHandler.getConnection();
Statement stmt = null;
ArrayList<CustomerRegistrationBean> list = new ArrayList<CustomerRegistrationBean>();
try {
ResultSet rs = null;
stmt = con.createStatement();
String selectQry = "SELECT cs.*, m.`NAME` as marketName, m.ID as marketID, category.`CATEGORY` as category, "
+ " area.`NAME` as areaName, area.`ID` as areaID, c.`NAME` as cityName,"
+ " c.`ID` as cityID, r.`NAME` as regionName, r.`ID` as regionID, z.name as zoneName,"
+ " z.ID as zoneID, ctr.name as countryName, ctr.`ID` as countryID, "
+ " color.NAME color ,brand.NAME brand, \n"
+ " vehicletype.`NAME` vehicleType, \n"
+ " manufacturer.`NAME` as `manufacturer` ,\n"
+ " brand.`MANUFACTURER_ID` as manfct_id,\n"
+ " cs.`BRAND_ID` as brandID,\n"
+ " cs.`VEHICLE_TYPE_ID` as vhcltypeId,\n"
+ " cs.`COLOR_ID` as colorID,\n"
+ " ul.`NAME` as createdBy ";
if (selectedMOID > 0) {
selectQry += ", mmb.`MO_ID` as moID, mo.`NAME` AS moName ";
}
// if (selectedSurveyId > 0 && surveyStatus == 1) {
// selectQry += ", sr.* ";
// }
if (selectedDeprtIds.length > 0) {
selectQry += ", dpt.`NAME` as department ";
}
selectQry
+= " from customerprofile cs \n";
if (selectedMOID > 0) {
selectQry += " join mo_market_bridge mmb on mmb.`MARKET_ID` = cs.`MARKET_ID` \n"
+ " join marketofficer mo on mo.`MO_ID` = mmb.`MO_ID` ";
}
// if (selectedSurveyId > 0 && surveyStatus == 1) {
// selectQry += " LEFT OUTER JOIN survey_result sr on sr.`CUSTOMER_ID` = cs.`CUSTOMER_ID` ";
// }
selectQry
+= " join market m on cs.`MARKET_ID` = m.`ID`\n"
+ " join area area on area.`ID` = m.`AREA_ID`\n"
+ " join city c on c.`ID` = area.`CITY_ID`\n"
+ " join region r on r.`ID` = c.`REGION_ID`\n"
+ " join zone z on z.id = r.`ZONE_ID`\n"
+ " left outer join user_login ul on cs.`CREATED_BY` = ul.`USER_ID`\n"
+ " join country ctr on ctr.`ID` = z.country_id\n"
+ " LEFT OUTER JOIN category on cs.`SUB_CATEGORY_ID` = category.`CATEGORY_ID`\n"
+ " LEFT OUTER JOIN category_type on category.`CATEGORY_TYPE_ID` = category_type.`TYPE_ID`\n"
+ " LEFT OUTER JOIN color ON cs.COLOR_ID = color.COLOR_ID\n"
+ " LEFT OUTER JOIN brand ON cs.BRAND_ID = brand.BRAND_ID\n"
+ " LEFT OUTER JOIN manufacturer ON brand.`MANUFACTURER_ID` = manufacturer.`MANUFACTURER_ID`\n"
+ " LEFT OUTER JOIN vehicletype ON cs.`VEHICLE_TYPE_ID` = vehicletype.`TYPE_ID`\n";
if (selectedDeprtIds.length > 0) {
selectQry += "left join department_bridge dptb on dptb.`CUSTOMER_ID` = cs.`CUSTOMER_ID`\n"
+ "left join department dpt on dpt.`DEP_ID` = dptb.`DEPARTMENT_ID` ";
}
/*
String selectQry = "SELECT cs.*, m.`NAME` as marketName, m.ID as marketID, area.`NAME` as areaName, \n"
+ "area.`ID` as areaID, c.`NAME` as cityName, c.`ID` as cityID, r.`NAME` as regionName, \n"
+ "r.`ID` as regionID, z.name as zoneName, z.ID as zoneID, ctr.name as countryName, ctr.`ID` as countryID,\n"
+ "color.NAME color ,brand.NAME brand, vehicletype.`NAME` vehicleType, manufacturer.`NAME` as `manufacturer`, brand.`MANUFACTURER_ID` as manfct_id\n"
+ " ,cs.`BRAND_ID` as brandID\n"
+ " ,cs.`VEHICLE_TYPE_ID` as vhcltypeId\n"
+ " ,cs.`COLOR_ID` as colorID from customerprofile cs \n"
+ " join market m on cs.`MARKET_ID` = m.`ID`\n"
+ " join area area on area.`ID` = m.`AREA_ID`\n"
+ " join city c on c.`ID` = area.`CITY_ID`\n"
+ " join region r on r.`ID` = c.`REGION_ID`\n"
+ " join zone z on z.id = r.`ZONE_ID`\n"
+ " join country ctr on ctr.`ID` = z.country_id\n"
+ " LEFT OUTER JOIN color ON cs.COLOR_ID = color.COLOR_ID\n"
+ " LEFT OUTER JOIN brand ON cs.BRAND_ID = brand.BRAND_ID\n"
+ " LEFT OUTER JOIN manufacturer ON brand.`MANUFACTURER_ID` = manufacturer.`MANUFACTURER_ID`\n"
+ " LEFT OUTER JOIN vehicletype ON cs.`VEHICLE_TYPE_ID` = vehicletype.`TYPE_ID`";
*/
String whereQry = " where (cs.REG_CODE like '%" + searchStr + "%' "
+ " or cs.FULL_NAME like '%" + searchStr + "%' "
+ " or cs.CNIC like '%" + searchStr + "%' "
+ " or cs.CONTACT_NO_1 like '%" + searchStr + "%' "
+ " or cs.CONTACT_NO_2 like '%" + searchStr + "%' "
+ " or cs.SHOP_NAME like '%" + searchStr + "%') ";
if (userStatus == null || userStatus.trim().equals("")) {
userStatus = "0";
}
if (userStatus != null || !userStatus.trim().equals("")) {
whereQry += whereQry.trim().equals("") ? " Where " : " and ";
whereQry += " cs.status = '" + userStatus + "' ";
}
if (selectedCountryId > 0) {
whereQry += " and ctr.ID = " + selectedCountryId;
}
if (selectedZondId > 0) {
whereQry += " and z.ID = " + selectedZondId;
}
if (selectedRegionId > 0) {
whereQry += " and r.ID = " + selectedRegionId;
}
if (selectedCityId > 0) {
whereQry += " and c.ID = " + selectedCityId;
}
if (selectedAreaId > 0) {
whereQry += " and area.ID = " + selectedAreaId;
}
for (int i = 0; i < selectedMarkets.length; i++) {
System.out.println("selectedMarkets : " + selectedMarkets[i]);
if (i == 0) {
whereQry += " and (m.ID = " + selectedMarkets[0] + " ";
} else if (i > 0 && i < selectedMarkets.length) {
whereQry += " or m.ID = " + selectedMarkets[i];
} else if (i == selectedMarkets.length) {
whereQry += " or m.ID = " + selectedMarkets[selectedMarkets.length] + " ) ";
}
if (selectedMarkets.length - 1 == i) {
whereQry += " ) ";
}
}
//
// if (selectedMarketId > 0) {
// whereQry += " and m.ID = " + selectedMarketId;
// }
if (selectedMOID > 0) {
whereQry += " and mo.`MO_ID` = " + selectedMOID;
}
if (selectedCatID > 0) {
whereQry += " and category_type.`TYPE_ID` = " + selectedCatID;
}
// if (selectedSubCategory > 0) {
// whereQry += " and category.`CATEGORY_ID` = " + selectedSubCategory;
// }
for (int i = 0; i < selectedSubCategoryIds.length; i++) {
System.out.println(selectedSubCategoryIds[i]);
if (i == 0) {
whereQry += " and (category.`CATEGORY_ID` = " + selectedSubCategoryIds[0] + " ";
} else if (i > 0 && i < selectedSubCategoryIds.length) {
whereQry += " or category.`CATEGORY_ID` = " + selectedSubCategoryIds[i];
} else if (i == selectedSubCategoryIds.length) {
whereQry += " or category.`CATEGORY_ID` = " + selectedSubCategoryIds[selectedSubCategoryIds.length] + " ) ";
}
if (selectedSubCategoryIds.length - 1 == i) {
whereQry += " ) ";
}
}
// if (selectedDeprtIds.length > 0) {
for (int i = 0; i < selectedDeprtIds.length; i++) {
System.out.println(selectedDeprtIds[i]);
if (i == 0) {
whereQry += " and (dpt.`DEP_ID` = " + selectedDeprtIds[0] + " ";
} else if (i > 0 && i < selectedDeprtIds.length) {
whereQry += " or dpt.`DEP_ID` = " + selectedDeprtIds[i];
} else if (i == selectedDeprtIds.length) {
whereQry += " or dpt.`DEP_ID` = " + selectedDeprtIds[selectedDeprtIds.length] + " ) ";
}
if (selectedDeprtIds.length - 1 == i) {
whereQry += " ) ";
}
}
// }
// if (selectedDeptId > 0) {
// whereQry += "and dpt.`DEP_ID` = " + selectedDeptId;
// }
// if (selectedSurveyId > 0) {
//
// whereQry += " and m.`ID` = " + selectedMarketId;
//
// if (surveyStatus == 1) {
// whereQry += " and sr.`SURVEY_ID` = " + selectedSurveyId;
// } else {
// whereQry += " and cs.CUSTOMER_ID not in (SELECT CPPP.CUSTOMER_ID FROM (SELECT * FROM survey_result sr where sr.`SURVEY_ID` = " + selectedSurveyId + " ) CPPP)";
// }
// }
selectQry += whereQry;
// System.out.println("selected method in Model 2nd calles");
System.out.print(selectQry);
rs = stmt.executeQuery(selectQry);
CustomerRegistrationBean p;
while (rs.next()) {
p = new CustomerRegistrationBean();
//
p.setRegCode(rs.getString("REG_CODE"));
p.setFullName(rs.getString("FULL_NAME"));
p.setContactNo1(rs.getString("CONTACT_NO_1"));
p.setContactNo2(rs.getString("CONTACT_NO_2"));
p.setEmail(rs.getString("EMAIL"));
p.setShopAddress(rs.getString("SHOP_ADDRESS"));
p.setShopName(rs.getString("SHOP_NAME"));
p.setWhatsAppNo(rs.getString("WHATSAPP_NO"));
p.setRemarks(rs.getString("REMARKS"));
p.setLatitude(rs.getDouble("LATITUDE"));
p.setLongitude(rs.getDouble("LONGITUDE"));
p.setCnicNo(rs.getString("CNIC"));
p.setPassportNo(rs.getString("PASSPORT_NO"));
p.setEntryDate(rs.getDate("ENTRY_DATE"));
p.setWokringSince(rs.getDate("WORKING_SINCE"));
p.setDob(rs.getDate("DATE_OF_BIRTH"));
p.setMarketName(rs.getString("marketName"));
p.setCountryName(rs.getString("countryName"));
p.setCityName(rs.getString("cityName"));
p.setAreaName(rs.getString("areaName"));
p.setRegion(rs.getString("regionName"));
p.setZone(rs.getString("zoneName"));
p.setSelectedMarketId(rs.getInt("marketID"));
p.setSelectedCountryId(rs.getInt("countryID"));
p.setSelectedCityId(rs.getInt("cityID"));
p.setSelectedRegionId(rs.getInt("regionID"));
p.setSelectedAreaId(rs.getInt("areaID"));
p.setSelectedZondId(rs.getInt("zoneID"));
p.setRegNo(rs.getString("REG_NO"));
p.setEngineNo(rs.getString("ENGINE_NO"));
p.setChassisNo(rs.getString("CHASSIS_NO"));
p.setSaleRefNo(rs.getString("SALE_REF_NO"));
p.setModelYear(rs.getString("MODEL_YEAR"));
p.setManufacturerId(rs.getInt("manfct_id"));
p.setBrandId(rs.getInt("brandID"));
p.setColorID(rs.getInt("colorID"));
p.setVchlTypeID(rs.getInt("vhcltypeId"));
p.setCallFrequency(rs.getString("CALL_FREQUENCY"));
p.setUserStatus(rs.getInt("STATUS") + "");
p.setCategoryName(rs.getString("category"));
p.setSelectedCatID(rs.getInt("CATEGORY_TYPE_Id"));
p.setSelectedSubCategory(rs.getInt("SUB_CATEGORY_ID"));
// p.setSelectedMOID(rs.getInt("moID"));
// p.setMoName(rs.getString("moName"));
p.setCustomerId(rs.getInt("CUSTOMER_ID"));
p.setCreatedBy(rs.getString("createdBy"));
double testAmount = searchRecoveryRequest(p.getRegCode());
System.out.println("testAmount : " + testAmount);
if (selectedMOID > 0) {
p.setSelectedMOID(rs.getInt("moID"));
p.setMoName(rs.getString("moName"));
}
if (selectedDeprtIds.length > 0) {
p.setDepartment(rs.getString("department"));
}
// System.out.println("p.getDepartment()" + p.getDepartment());
list.add(p);
p = null;
}
} catch (Exception e) {
System.out.println(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
System.out.println(e);
}
conHandler.freeConnection(con);
}
return list;
}
Method2
Method2
public double searchRecoveryRequest(String custRegCode) {
double recoveryAmount = 0;
ConnectionHandler conHandler = ConnectionHandler.getConnectionHandler();
Connection con = conHandler.getConnection();
Statement stmt = null;
try {
ResultSet rs = null;
stmt = con.createStatement();
String selectQry = "select re.`AMOUNT` as amount \n"
+ "from complain cmp\n"
+ "join customerprofile cp on cp.`REG_CODE` = cmp.`CUST_REG_NO`\n"
+ "JOIN `recovery` re ON re.`COMPLAINT_ID` = cmp.`CODE`\n"
+ "where cmp.`CUST_REG_NO` = '" + custRegCode + "' and cmp.`STATUS_CODE` <> 'CLOSED'\n";
System.out.println(selectQry);
rs = stmt.executeQuery(selectQry);
if(rs.next()){
recoveryAmount = rs.getDouble("amount");
}
} catch (Exception e) {
System.out.println(e);
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
System.out.println(e);
}
conHandler.freeConnection(con);
}
return recoveryAmount;
}
Thanks.
谢谢。
1 个解决方案
#1
4
It is not easy to find a proper solution to your problem without having a running test system to play around.
如果没有运行的测试系统来解决问题，要找到合适的解决方案并不容易。
What I find suspicious is the fact that your 2nd method is called for every single result row of your first query, and it seems to open and close database connections every single time. (Your ConnectionHandler might implement proper connection pooling, but I cannot judge this without knowing the code. If the connection is really opened and closed every time, this is by far the most expensive thing in your code).
我发现值得怀疑的是，对于第一个查询的每一个结果行，都会调用第二个方法，并且每次都要打开和关闭数据库连接。(您的ConnectionHandler可能实现适当的连接池，但是我不能在不了解代码的情况下判断它。如果连接每次都被打开和关闭，那么这是到目前为止代码中最昂贵的东西)。
But even with proper connection pooling, your 2nd method creates and executes a new SQL statement for every call. This is called the N+1 Select Query Issue.
但是，即使使用适当的连接池，您的第二个方法也会为每次调用创建并执行一个新的SQL语句。这被称为N+1选择查询问题。
So I suggest the following improvements:
所以我建议如下改进:
-
Try to merge both SQL statements into a single one (maybe using nested SELECTs, etc.). Then you must not execute 1+N queries, which will greatly reduce execution time.
尝试将这两个SQL语句合并为一个语句(可能使用嵌套选择等等)。然后，您不能执行1+N查询，这将极大地减少执行时间。
-
If that's not possible, use at least a
PreparedStatementfor the 2nd method. Make sure to create the prepared statement just once before entering the loop. So you'll have to create and prepare it in the first method. Therefore, you need a second database connection. Open and close it before respectively after the loop too.如果不可能，至少对第二种方法使用PreparedStatement。在进入循环之前，确保只创建一次准备好的语句。所以你必须在第一个方法中创建并准备它。因此，您需要第二个数据库连接。在循环之后分别打开和关闭它。
-
Another strategy: Run the first query, iterate the result set and put all
regCodesin a list. After closing the query, use batch-processing viaPreparedStatementto work off all theregCodes. Another advantage: Therefore only one database connection is required.另一个策略是:运行第一个查询，迭代结果集，并将所有regcode放在列表中。在关闭查询之后，通过PreparedStatement使用批处理来处理所有的regcode。另一个优点是:因此只需要一个数据库连接。
Btw.: Mature object-relational mapping frameworks like Hibernate already provide these strategies out of the box to avoid or at least extenuate the 1+N select problem.
顺便说一句。:像Hibernate这样成熟的对象关系映射框架已经提供了这些现成的策略，以避免或至少减轻1+N选择问题。
#1
4
It is not easy to find a proper solution to your problem without having a running test system to play around.
如果没有运行的测试系统来解决问题，要找到合适的解决方案并不容易。
What I find suspicious is the fact that your 2nd method is called for every single result row of your first query, and it seems to open and close database connections every single time. (Your ConnectionHandler might implement proper connection pooling, but I cannot judge this without knowing the code. If the connection is really opened and closed every time, this is by far the most expensive thing in your code).
我发现值得怀疑的是，对于第一个查询的每一个结果行，都会调用第二个方法，并且每次都要打开和关闭数据库连接。(您的ConnectionHandler可能实现适当的连接池，但是我不能在不了解代码的情况下判断它。如果连接每次都被打开和关闭，那么这是到目前为止代码中最昂贵的东西)。
But even with proper connection pooling, your 2nd method creates and executes a new SQL statement for every call. This is called the N+1 Select Query Issue.
但是，即使使用适当的连接池，您的第二个方法也会为每次调用创建并执行一个新的SQL语句。这被称为N+1选择查询问题。
So I suggest the following improvements:
所以我建议如下改进:
-
Try to merge both SQL statements into a single one (maybe using nested SELECTs, etc.). Then you must not execute 1+N queries, which will greatly reduce execution time.
尝试将这两个SQL语句合并为一个语句(可能使用嵌套选择等等)。然后，您不能执行1+N查询，这将极大地减少执行时间。
-
If that's not possible, use at least a
PreparedStatementfor the 2nd method. Make sure to create the prepared statement just once before entering the loop. So you'll have to create and prepare it in the first method. Therefore, you need a second database connection. Open and close it before respectively after the loop too.如果不可能，至少对第二种方法使用PreparedStatement。在进入循环之前，确保只创建一次准备好的语句。所以你必须在第一个方法中创建并准备它。因此，您需要第二个数据库连接。在循环之后分别打开和关闭它。
-
Another strategy: Run the first query, iterate the result set and put all
regCodesin a list. After closing the query, use batch-processing viaPreparedStatementto work off all theregCodes. Another advantage: Therefore only one database connection is required.另一个策略是:运行第一个查询，迭代结果集，并将所有regcode放在列表中。在关闭查询之后，通过PreparedStatement使用批处理来处理所有的regcode。另一个优点是:因此只需要一个数据库连接。
Btw.: Mature object-relational mapping frameworks like Hibernate already provide these strategies out of the box to avoid or at least extenuate the 1+N select problem.
顺便说一句。:像Hibernate这样成熟的对象关系映射框架已经提供了这些现成的策略，以避免或至少减轻1+N选择问题。