mysql query takes too long using java

Question

I have three mysql tables:

Customer
Complaint
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:

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:

getMultiSelectionCustomersProfiles(....)
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;

}

Thanks.

It's better to have a complex view that you can select from it instead. — cdaiga, Jun 22 '18 at 14:13
definitely index on primary key, you won't be able to index for %abcd% though — Thijs Steel, Jun 22 '18 at 14:16
You should analyse your query by executing it directly against the database, using the MySQL shell or any other tool like MySQL Workbench. I think this is not a java problem but more a SQL/MySQL problem. — isnot2bad, Jun 22 '18 at 14:24
@isnot2bad i am using MySQL Workbench, but its took too much time. — sarfaraz Ahmed, Jun 22 '18 at 14:26
@sarfarazAhmed have you tried to analyze and optimize it (see https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html or https://dev.mysql.com/doc/refman/8.0/en/optimization.html in general)? — isnot2bad, Jun 22 '18 at 14:28
@isnot2bad sir my first method work fine, execute quickly, when i call second one it go too long because i compare one by one profile so please suggest me the fasten way to search — sarfaraz Ahmed, Jun 22 '18 at 14:32
@sarfarazAhmed ok - so the first query itself is fast, but iterating over all the results and executing the second query for each of them takes a long time? — isnot2bad, Jun 22 '18 at 14:33

isnot2bad · Accepted Answer · 2018-06-22T15:00:42.813

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).

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.

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.
If that's not possible, use at least a PreparedStatement for 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.
Another strategy: Run the first query, iterate the result set and put all regCodes in a list. After closing the query, use batch-processing via PreparedStatement to work off all the regCodes. Another advantage: Therefore only one database connection is required.

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.

mysql query takes too long using java

1 Answers1

Linked