Databases
StackOverflow2013
Postsdbo
POJDBC SELECT very slow in comparison to Firefox DB manager
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJDBC SELECT very slow in comparison to Firefox DB manager
    primarykey
    data
    text
    <p><strong>Solved</strong>, of course after posting here it hit me... Now using different drivers from <a href="http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC#Download" rel="nofollow">http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC#Download</a> that don't need extensive configuration. </p> <p>Original question below the break.</p> <hr> <p>I'm fooling around with a SQLite database containing OpenStreetMap data, and I'm having some trouble with JDBC. </p> <p>The query below is the one I'd like to use to get a location close to the location of my user quicky (numbers are from my test-data, and are added by the java code).</p> <pre><code>SELECT roads.nodeID, lat, lon FROM roads INNER JOIN nodes ON roads.nodeID=nodes.nodeID ORDER BY (ABS(lat - (12.598418)) + ABS(lon - (-70.043514))) ASC LIMIT 1 </code></pre> <p>'roads' and 'nodes' both contain approximately 130,000 rows.</p> <p>This specific query is one of the most intensive buyt it's only used twice so that should be OK for my needs. It executes in about 281 ms when using the Firefox SQLite, but in Java using sqlitejdbc-v056 it takes between 12 and 14 seconds (with full processor load).</p> <p>Any clues on how to fix this?</p> <pre><code>public Node getNodeClosestToLocation(Location loc){ try { Class.forName("org.sqlite.JDBC"); Statement stat = conn.createStatement(); String q = "SELECT roads.nodeID, lat, lon "+ "FROM roads "+ "INNER JOIN nodes "+ "ON roads.nodeID=nodes.nodeID "+ "ORDER BY (ABS(lat - ("+loc.getLat()+")) + ABS(lon - ("+loc.getLon()+"))) ASC "+ "LIMIT 1"; long start = System.currentTimeMillis(); System.out.println(q); rs = stat.executeQuery(q); if(rs.next()) { System.out.println("Done. " + (System.currentTimeMillis() - start)); return new Node(rs.getInt("nodeID"), rs.getFloat("lat"), rs.getFloat("lon")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJelle Veraa
    1. USJelle Veraa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. This table or related slice is empty.
      1. VTUndeletion
    1. COHow come sqllite query is related to browser? Could that some other component in application?
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. USkosa
    2. COSorry for the confusion: that was just to demonstrate that the query itself should not be slow per-se as another appliction (the SQLite DB Manager plugin for the Firefox browser in this case) does it a lot faster. You can see me doing the timing, and it's a very 'young' project with just one single thread running.
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. USJelle Veraa
    3. COare you sure you use the same amount of data, the same query and the results are the same? and both are read from the same memory storage - RAM / HDD / SDD? if you read 100 rows from RAM for FF test and 130 000 from HDD for Java, there would be no wonder :) otherwise,, it is probably a bug in Java's implementation
      singulars
      1. POJDBC SELECT very slow in comparison to Firefox DB manager
      1. USAprillion
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload