CODEWITHSUNDEEP
javamysqlwhile-loop
sarfaraz Ahmed
sarfaraz Ahmed

Reputation: 159

Iterating data takes too long using java

I have three mysql tables:

  1. Customer
  2. Complaint
  3. Recovery

Problem statement: : I have 50000+ customers, I want to search a customer against whom there's recovery request.

The problem is pretty solved, and working correctly, but the problem is the query takes upto 30 minutes or long.

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: enter image description here

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

Is there any technique through which to reduce the query time, or fasten the search?

JAVA CODE I have two methods:

  1. getMultiSelectionCustomersProfiles(....)
  2. searchRecoveryRequest( String customerRegistrationCode )

Note: method2 is called in 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

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;

}

Here is the tables create Statements

Customer profile table

CREATE TABLE  `crm`.`customerprofile` (
  `REG_CODE` varchar(55) DEFAULT NULL,
  `FULL_NAME` varchar(55) DEFAULT NULL,
  `CNIC` varchar(55) DEFAULT NULL,
  `DATE_OF_BIRTH` datetime DEFAULT NULL,
  `EMAIL` varchar(200) DEFAULT NULL,
  `CONTACT_NO_1` varchar(45) DEFAULT NULL,
  `LONGITUDE` decimal(11,8) NOT NULL,
  `ENTRY_DATE` datetime DEFAULT NULL,
  `SHOP_NAME` varchar(150) DEFAULT NULL,
  `MARKET_ID` int(11) unsigned DEFAULT NULL,
  `CUSTOMER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VEHICLE_TYPE_ID` int(10) DEFAULT NULL,
  `SALE_REF_NO` varchar(255) DEFAULT NULL,
  `MODEL_YEAR` varchar(4) DEFAULT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,

) ENGINE=InnoDB AUTO_INCREMENT=48608 DEFAULT CHARSET=utf8;

complaint Table

CREATE TABLE  `crm`.`complain` (
  `CODE` int(11) NOT NULL AUTO_INCREMENT,
  `COMPLAIN_NO` varchar(10) DEFAULT NULL,
  `DATE` date DEFAULT NULL,
  `COMPLAIN_TYPE_CODE` int(11) NOT NULL,
  `COMPLAIN_REASON_CODE` int(11) NOT NULL,
  `DESCRIPTION` varchar(4000) DEFAULT NULL,
  `STATUS_CODE` int(10) DEFAULT NULL,
  `STAGE_CODE` int(11) DEFAULT NULL,
  `CUST_REG_NO` varchar(55) NOT NULL,
  `CREATION_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` int(11) DEFAULT NULL,
  `COMPLAIN_SUBTYPE_CODE` int(11) NOT NULL,
  `PARTS_ID` int(10) unsigned NOT NULL,
  `LEVEL_ID` int(2) DEFAULT NULL,
  `LAST_MODIFIED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRIORITY_CODE` int(10) unsigned NOT NULL DEFAULT '5',
  `TAT_STATUS_CODE` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`CODE`),
  KEY `STATUS_CODE_FK` (`STATUS_CODE`),
  KEY `STAGE_CODE` (`STAGE_CODE`),
  CONSTRAINT `STAGE_CODE` FOREIGN KEY (`STAGE_CODE`) REFERENCES `stage` (`CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `STATUS_CODE_FK` FOREIGN KEY (`STATUS_CODE`) REFERENCES `status` (`CODE`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3478 DEFAULT CHARSET=latin1;

Recover Table

CREATE TABLE  `crm`.`recovery` (
  `RECOVERY_ID` int(10) NOT NULL AUTO_INCREMENT,
  `COMPLAINT_ID` int(10) DEFAULT NULL,
  `AMOUNT` double DEFAULT NULL,
  `AGING_DATE` datetime DEFAULT NULL,
  `RO_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`RECOVERY_ID`),
  UNIQUE KEY `RECOVERY_ID_UNIQUE` (`RECOVERY_ID`),
  KEY `FK_RO_ID_idx` (`RO_ID`),
  CONSTRAINT `FK_RO_ID` FOREIGN KEY (`RO_ID`) REFERENCES `marketofficer` (`MO_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 138

Answers (1)

EchoMike444
EchoMike444

Reputation: 1692

Your problem is very simple , you dont have any key/indexes on crm.customerprofile .

You must add a key on column REG_CODE in the table crm.customerprofile .

Each time you try to identify a customer by his reg_code , mysql need to read the 50000 rows to find .

Another one that will be useful : a index on CUST_REG_NO in the table crm.complain

Upvotes: 1

Related Questions