我有三个 mysql 表:
问题陈述:我有 50000 多个客户，我想搜索有恢复请求的客户。
The problem is pretty solved, and working correctly, but the problem is the query takes upto 30 minutes or long.
请指导我如何减少查询所花费的时间，或者如何加快搜索速度？
这是 mysql 表的快速帮助 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.
是否有任何技术可以减少查询时间或加快搜索速度？
Java 代码 我有两种方法:
注意:method1中调用了method2
方法一:
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;
}
方法2
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;
}
谢谢。
最佳答案
如果没有可运行的测试系统，要找到问题的合适解决方案并不容易。
我觉得可疑的是，您的第 2 个方法是针对第一个查询的每个结果行调用的，而且它似乎每次都打开和关闭数据库连接。 (您的 ConnectionHandler 可能会实现适当的连接池，但如果不知道代码，我无法判断这一点。如果连接真的每次都打开和关闭，那么这是您代码中迄今为止最昂贵的东西)。
但即使有适当的连接池，您的第二个方法也会为每次调用创建并执行一个新的 SQL 语句。这称为 N+1 Select Query Issue .
因此我建议进行以下改进:
尝试将两个 SQL 语句合并为一个语句(可能使用嵌套的 SELECT 等)。那么一定不要执行1+N次查询，这样会大大减少执行时间。
如果不可能，至少使用 PreparedStatement对于第二种方法。确保在进入循环之前创建准备好的语句仅一次。所以你必须在第一种方法中创建和准备它。因此，您需要第二个数据库连接。在循环之后分别打开和关闭它。
另一种策略:运行第一个查询，迭代结果集并将所有 regCodes 放入列表中。关闭查询后，使用batch-processing via PreparedStatement处理所有 regCodes。另一个优势:因此只需要一个数据库连接。
顺便说一句:成熟的对象关系映射框架(如 Hibernate)已经提供了这些开箱即用的策略，以避免或至少减轻 1+N 选择问题。
关于java - mysql查询使用java花费的时间太长，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50989635/
我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div
我有一个Ruby程序，它使用rubyzip压缩XML文件的目录树。gem。我的问题是文件开始变得很重，我想提高压缩级别，因为压缩时间不是问题。我在rubyzipdocumentation中找不到一种为创建的ZIP文件指定压缩级别的方法。有人知道如何更改此设置吗？是否有另一个允许指定压缩级别的Ruby库？ 最佳答案 这是我通过查看rubyzip内部创建的代码。level=Zlib::BEST_COMPRESSIONZip::ZipOutputStream.open(zip_file)do|zip|Dir.glob("**/*")d
类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc
很好奇，就使用rubyonrails自动化单元测试而言，你们正在做什么？您是否创建了一个脚本来在cron中运行rake作业并将结果邮寄给您？git中的预提交Hook？只是手动调用？我完全理解测试，但想知道在错误发生之前捕获错误的最佳实践是什么。让我们理所当然地认为测试本身是完美无缺的，并且可以正常工作。下一步是什么以确保他们在正确的时间将可能有害的结果传达给您？ 最佳答案 不确定您到底想听什么，但是有几个级别的自动代码库控制:在处理某项功能时，您可以使用类似autotest的内容获得关于哪些有效，哪些无效的即时反馈。要确保您的提
假设我做了一个模块如下:m=Module.newdoclassCendend三个问题:除了对m的引用之外，还有什么方法可以访问C和m中的其他内容？我可以在创建匿名模块后为其命名吗(就像我输入“module...”一样)？如何在使用完匿名模块后将其删除，使其定义的常量不再存在？ 最佳答案 三个答案:是的，使用ObjectSpace.此代码使c引用你的类(class)C不引用m:c=nilObjectSpace.each_object{|obj|c=objif(Class===objandobj.name=~/::C$/)}当然这取决于
我正在尝试使用ruby和Savon来使用网络服务。测试服务为http://www.webservicex.net/WS/WSDetails.aspx?WSID=9&CATID=2require'rubygems'require'savon'client=Savon::Client.new"http://www.webservicex.net/stockquote.asmx?WSDL"client.get_quotedo|soap|soap.body={:symbol=>"AAPL"}end返回SOAP异常。检查soap信封，在我看来soap请求没有正确的命名空间。任何人都可以建议我
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
我想将html转换为纯文本。不过，我不想只删除标签，我想智能地保留尽可能多的格式。为插入换行符标签，检测段落并格式化它们等。输入非常简单，通常是格式良好的html(不是整个文档，只是一堆内容，通常没有anchor或图像)。我可以将几个正则表达式放在一起，让我达到80%，但我认为可能有一些现有的解决方案更智能。 最佳答案 首先，不要尝试为此使用正则表达式。很有可能你会想出一个脆弱/脆弱的解决方案，它会随着HTML的变化而崩溃，或者很难管理和维护。您可以使用Nokogiri快速解析HTML并提取文本:require'nokogiri'h