• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select takes 2 seconds in dos but minutes with jdbc

 
Eduardo Cobian
Ranch Hand
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What are you planning to do with half a million records once they've been selected?

Jules
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic