wood burning stoves 2.0*
The moose likes JDBC 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


JavaRanch » Java Forums » Databases » JDBC
Bookmark "select takes 2 seconds in dos but minutes with jdbc" Watch "select takes 2 seconds in dos but minutes with jdbc" New topic
Author

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?

Thanks.
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?

Jules
Peter den Haan
author
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
Hello,

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 (rs.next()) {
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)));
marketStart=asset.thisDayMarketStart(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???

Thanks.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
Consider Paul's rocket mass heater.
 
subject: select takes 2 seconds in dos but minutes with jdbc