File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes select takes 2 seconds in dos but minutes with jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "select takes 2 seconds in dos but minutes with jdbc" Watch "select takes 2 seconds in dos but minutes with jdbc" New topic

select takes 2 seconds in dos but minutes with jdbc

Eduardo Cobian
Ranch Hand

Joined: Nov 07, 2001
Posts: 45
Hello all,

I have a table with price bars: long time, double open, double, close, double high, double low, int volume;

If I do (from the dos window) a select * from bars where time>xxxx and time <yyyyy it takes 2 seconds for 500.000 records.

The same select from java takes minutes and if the time span is 3 yeras it crashes without any message.

Can anyone suggest something?

Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
What are you planning to do with half a million records once they've been selected?

Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
With the "dos window" I assume you mean some SQL client like SQL*Plus or similar. Does it really print 500,000 records worth of data in 2 seconds? On my box a dos window is surprisingly sluggish

Why Java is so much slower, and why you're crashing, is impossible to say based on the information you gave. It entirely depends on how you you approached the problem. The fact that you're crashing suggests that there's room for optimisation Can you post some more details?

- Peter
Eduardo Cobian
Ranch Hand

Joined: Nov 07, 2001
Posts: 45

thanks for answering.

I need to read these 500.000 to display 15 minutes bars for 3 years. I read the 1 minute bar and create 15 minutes bar.

When I say dos I mean a dos window (start-execute: cmd) and then I do: 2mysql -u root -p", "use dbName" and I do a select which doesn't display all the records but has to read them all. "select * from bars where time > xxxx and time < yyyyy and volume = 3333". I don't have an index on volume so the db has to read them all (I think).

The java part is:
String sql = "select * from bar1min_"+assetId+" where time >= '"+date1+"' and time <= '"+date2+"'";
Connection conn=DBUtil.getConnection(dbhost);
if(conn == null) return null;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while ( {
bar.time = Long.parseLong(rs.getString("time"));
bar.O = rs.getDouble("open");
bar.H = rs.getDouble("high");
bar.L = rs.getDouble("low");
bar.C = rs.getDouble("close");
bar.V = rs.getInt("volume"); // util.pln( sdf.format(new java.util.Date(bar.time)));
OneMinuteBarLoader.createBars(barss, bar, newbar, lastBarTime,interval,marketStart,marketEnd,stra);

This takes ages. And it works with bars for two years (350.000 records) but not with bars for 3 years (500.000 records).

Can you help me???

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33132

You aren't comparing the same thing. Even without an index on volume, the database can optimize the query somewhat. It can decide to filter by volume first. More importantly, it doesn't have to pass as many rows back across the network.

Since you have a valid reason for needing all that data, you need to work on tuning the query. A few suggestions:
1) Make sure there is an index on the date field.
2) List only the rows you need rather than select *. This will allow you to avoid transfering things across the network that you will not use.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: select takes 2 seconds in dos but minutes with jdbc
It's not a secret anymore!