Java – JDBC select is very slow compared to Firefox DB manager

Solved, of course, after the release, it hit me... Now use it http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC#Download Different drivers do not require a lot of configuration

Rest on the original question below

I'm using the SQLite database containing OpenStreetMap data, and I'm having some trouble using JDBC

The following query is what I want to use to get a location close to my user location (the number is from my test data and added by java 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

Both 'roads' and' nodes' contain about 130000 lines

This particular query is one of the most intensive purchases. It has only been used twice, so it should meet my needs When using Firefox SQLite, it executes in about 281 milliseconds, but in Java using SQLite jdbc-v056, it takes 12 to 14 seconds (with full processor load)

Any clues on how to solve this problem?

public Node getNodeClosestToLocation(Location loc){
        try {
            Class.forName("org.sqlite.JDBC");
            Statement stat = conn.createStatement();
            String q = "SELECT roads.nodeID,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;
    }

Solution

When it comes to select statements and queries in JDBC, they are very slow if they are not used correctly what time?

>Ensure the correct columns in the index table A simple line, such as:

Statement stat = connection. createStatement(); Stat.executeupdate ("create index {index_name}" ({column_name}) on the order); “); stat.close();

Create index: http://www.w3schools.com/sql/sql_create_index.asp

>Insertion requires a longer index because each previous index needs to be updated when a new record is inserted After executing all insert statements (better performance), it is best to create the index first The index column will affect the insertion performance, but the selection performance will be significantly faster. > Changing the jdbc driver may help, but it should not be a potential problem in general Also make sure you are running in native mode The pure Java mode is significantly slower, at least from what I've noticed Assuming you are using SQLite JDBC, the following code snippet will tell you the running mode

System. out. println(String.format(“％s mode”,sqliteJDBCLoader.isNativeMode()？ “native”：“pure-java”));

I have encountered the same problem because of the slow selection speed in databases with a history of more than 500K If I am not indexed, my application will run for 9.9 days Now it's an ultrafast 2 minutes to do the same thing SQLite is very fast when using correct and optimized SQL