CODEWITHSUNDEEP
javamysqljdbc
HMdeveloper
HMdeveloper

Reputation: 2884

huge difference between the running time of the same SQL query in java and mysql

I have a function as follow:

public static ArrayList<Article> getNewsList(Database db, Date start,Date end,int nextNth,int limit,String tableName,String entityQuery,String topicQuery) throws SQLException {
    long a=System.currentTimeMillis();
    ArrayList<Article> youTube=new ArrayList<Article>();
    System.out.println("starting time is : "+a+"    ");
    Connection conn = db.getConnection();
    PreparedStatement stmt = conn
            .prepareStatement("SELECT n.title,n.link,n.Description,n.NewsContent,n.IURL,date(n.Published) FROM "+tableName+" as en inner join news as n on "
                    + " en.dataitemid=n.id where en.dataitemtype=1 and dateAdded between ? and ? "+entityQuery+"  "+topicQuery+" limit ?,?;");
    stmt.setDate(1, start);
    stmt.setDate(2, end);
    stmt.setLong(3, nextNth);
    stmt.setLong(4, limit);

    ResultSet rset = stmt.executeQuery();
        while (rset.next()) {
            Article nw=new Article();
            nw.setTitle(rset.getString(1));
            nw.setLink(rset.getString(2));
            nw.setDescription(rset.getString(3));
            nw.setContent(rset.getString(4));
            nw.setiURL(rset.getString(5));
            nw.setDate(rset.getDate(6));

            youTube.add(nw);
        }
        long b=System.currentTimeMillis();
        System.out.println(b+" total time it takes is: "+(b-a));
    return youTube;
}

this function acts very funny. As you see I have 2 variables for starting and ending time of the function and when I run the function it takes 36 seconds(the result of b-a)

but if I run the same query with the same parameters in mysql it takes only 0.019 seconds which shows the huge difference though as you can see I do not have anything special in my java code that takes long time . I am so curious to know what causes this huge difference. Can anyone help?

Upvotes: 0

Views: 286

Answers (2)

Rusty1
Rusty1

Reputation: 381

There could be network latency / errors involved here. Try moving you a and b timing just before and just after the execute and have a look at that value. In your example you don't mention how many rows you are retrieving but this might be effecting your timings.

Upvotes: 1

Vallabh Patade
Vallabh Patade

Reputation: 5110

Before making query call, it has to do number of things, Load JDBC driver(In class of the object db), Establish connection and then it fires the query.

Thus overall time required is very much. If you put long a=System.currentTimeMillis(); just before stmt.executeQuery() and long b=System.currentTimeMillis(); just after it, you will see some improvement.

Upvotes: 0

Related Questions