CODEWITHSUNDEEP
mysqldatabaseperformancejdbc
Vasu
Vasu

Reputation: 125

How to avoid unnecessary fetching of data from database?

When executing the following JDBC code, I have in MySQL administrator observed several unnecessary database fetching activities.

public void tableData() {
    List<TaxAccountReportTableBean> tdata = new ArrayList<TaxAccountReportTableBean>();
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    Class.forName("com.mysql.jdbc.Driver");
    con = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/inventory" + "", "root", "root");
    st = (Statement)con.createStatement(); 

    try {
        String qry = "SELECT taxno, region, taxname ,  tax, cf, cftax  FROM  Tax";
        System.out.println("-------->"+qry);
        rs = st.executeQuery(qry);

        while (rs.next()) {
            trtb = new TaxAccountReportTableBean();
            trtb.setTaxno(rs.getString(1));
            trtb.setRegion(rs.getString(2));
            trtb.setTaxname(rs.getString(3));
            trtb.setTaxpercent(rs.getString(4));
            trtb.setCfreq(rs.getString(5));
            trtb.setCftax(rs.getString(6));
            tdata.add(trtb);
        }

        rs.close();
        st.close();
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

When I execute the query, I have observed in MySQL administrator that 10 queries are accessing database. Due to this unnecessary fetching the application performance is poor. How is this caused and how can I solve it?

Update: I have debugged the code and found that the unnecessary query fetching is occurring during connection creation process and not during getting data from query. How can I avoid this problem?

Upvotes: 1

Views: 292

Answers (1)

Code Lღver
Code Lღver

Reputation: 15603

In mysql you can use the WHERE condition to filter the data And can use the limit clause to get the specific rows data.

String qry = "SELECT taxno, region, taxname ,  tax, cf, cftax  FROM  Tax WHERE your_condition limit 10"

Your condition will be like this:

name LIKE "%abc%" //or you can use any thing based on your db structure.

Upvotes: 1

Related Questions