jeudi 17 septembre 2015

Java SQLite JDBC "SELECT" statement very slow

I am using a SELECT statement on a table with roughly 45000 entries.

I always need just a single entry and this takes between 1-90ms.

I don't get why some of those are faster than others and i'd like to know how to speed up this process.

The code is as follows:

                long startTime = System.nanoTime();

                PreparedStatement selectDB = cStatic.prepareStatement("SELECT column1, column2, column3, column4 FROM DB WHERE ID = ?");
                selectDB.setInt(1, rsIDs.getInt(3));

                ResultSet rsDB = selectDB.executeQuery();

                long endTime = System.nanoTime();

                long duration = (startTime - endTime) / 1000000;

Since i already searched for solutions i am already using indexes for "getInt/getString" from result set and also only call the needed columns of the table instead of the full row.

But the processing times [ms] are still like this for one single select statement:

33, 2, 1, 46, 17, 2, 20, 28, 27, 25, 18

Somewhere else in the process the time sometimes reaches up to 90ms, these are just the first entries

Every other statement (UPDATE , INSERT, DELETE) hardly even uses 1ms, why is SELECT needing so much time?

Aucun commentaire:

Enregistrer un commentaire