Huge time accessing database from Java

Question

I'm a junior java programmer and I've finally made my first program, all by myself, apart from school :).

The basics are: you can store data on it and retrieve it anytime. The main thing is, I want to be able to run this program on another computer (as a runable .jar file).

Therefore I had to install JRE and microsoft access 2010 drivers (they both are 32 bit), and the program works perfect, but there is 1 small problem.

It takes ages (literaly, 17 seconds) to store or delete something from the database. What is the cause of this? Can I change it?

Edit:

Here's the code to insert an object of the class Woord into the database.

public static void ToevoegenWoord(Woord woord) {
    try (Connection conn = DriverManager.getConnection("jdbc:odbc:DatabaseSenne")) {
        PreparedStatement addWoord =
            conn.prepareStatement("INSERT INTO Woorden VALUES (?)");
        addWoord.setString(1, woord.getWoord());
        addWoord.executeUpdate();
    } catch (SQLException ex) { 
        for (Throwable t : ex) {
            System.out.println("Het woord kond niet worden toegevoegd aan de databank.");
            t.printStackTrace();
        } 
    }
}

Can you show the code on how you connect and insert data into database. Try googling about connection pool. It might help you. — Subir Kumar Sao, Nov 08 '12 at 13:30
Good time to learn how to profile an application. Try this: http://docs.oracle.com/javase/6/docs/technotes/tools/share/jvisualvm.html — vainolo, Nov 08 '12 at 13:30
you can also add log prints to your code with the time, to see where is your problem — vainolo, Nov 08 '12 at 13:31
I don't have any problems with my code, it runs good, but slow when I insert data into the database. — JordyV, Nov 08 '12 at 13:37
How the spinning spacenoodle did I not know about the *try with ressources* syntax? That's amazing! :D — brimborium, Nov 08 '12 at 13:51
@JordyV How many rows does the table in question have? Do you have an index? If it is a clustered index, then that is the problem... This should use a non-clustered index! — ppeterka, Nov 08 '12 at 13:52
@ppeterka I don't know, I didn't have to declare the amount of rows I want to use.. — JordyV, Nov 08 '12 at 13:58
You should start method names with lowercase letters (according to the [Java Naming Conventions](http://www.oracle.com/technetwork/java/javase/documentation/codeconventions-135099.html#367)). So your method would be `void toevoegenWoord(Woord woord)` instead of `void ToevoegenWoord(Woord woord)`. — brimborium, Nov 08 '12 at 13:58
@brimborium That acutaly doesn't matter, at all.. But thanks for telling me, I didn't see that one :) I normally do write all my methods starting with a lowercase letter.. — JordyV, Nov 08 '12 at 14:05
Sure it doesn't matter for the compiler. But it does matter for code readability. It's definitely not a fix to your problem, but as you are a "junior java programmer" I thought that suggestions might pay off for your... ;) If you are not producing clean java code, you might as well code in C++ \*duck and run\* — brimborium, Nov 08 '12 at 14:09
@brimborium try-with-resources was introduced in SE 7 so I guess you can be forgiven for not knowing about it ;-) — Martin Wilson, Nov 08 '12 at 14:11
There are many close calls in JDBC, for instance in the ResultSet. They are intended for cleanup. As the native driver might need to clean up things too, one should call close. — Joop Eggen, Nov 08 '12 at 14:38

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

Most likely creating Connection every time is slow operation in your case (especially using JDBC-ODBC bridge). To confirm this try to put print statements with timestamp before and after the line that get Connection from DriverManager. If that's the case consider not to open connection on every request but open it once and reuse, better yet use some sort of Connection Pooling, there are plenty of options available.

If that's mot the case then actual insert could be slow as well. Again simple profiling with print statements should help you to discover where your code is spending most of the time.

edited May 23 '17 at 12:04

Community

1
1

answered Nov 08 '12 at 14:06

maximdim

8,041
3
33
48

To be honest, I don't understand half of what you guys mean. As I said, I'm a junior programmer. When you talk about things I don't yet know/understand, give some examples or more information about it please. – JordyV Nov 08 '12 at 14:08
If you don't know how to use print statements in Java (System.out.println(""+new Date())) then perhaps it's good idea to start with something simpler than Database operations and read a good introductory level book on Java programming. – maximdim Nov 08 '12 at 14:11
I do know about them, but I don't know the reference to them as I don't follow my courses in english. But now I know they're just prints I know what you're talking about. I use them too, to see what goes wrong at which point. – JordyV Nov 08 '12 at 14:15
But where do you display your so called 'logs' with an executable jar file? Not in your console, that's for sure.. – JordyV Nov 08 '12 at 14:28

score 0 · Answer 2 · answered Nov 08 '12 at 14:20

First of all, congrats on your first independent foray. To answer your question / elaborate on maximdim's answer, the concern is that calling:

try (Connection conn = DriverManager.getConnection("jdbc:odbc:DatabaseSenne")) {

every time you're using this function may be a major bottleneck (or perhaps another section of your code is.) Most importantly, you will want to understand the concept of using logging or even standard print statements to help diagnose where you are seeing an issue. Wrapping individual lines of code like so:

System.out.println("Before Connection retrieval: " + new Date().getTime());
try (Connection conn = DriverManager.getConnection("jdbc:odbc:DatabaseSenne")) {
        System.out.println("AFTER Connection retrieval: " + new Date().getTime());

...to see how many milliseconds pass for each call can help you determine exactly where your bottleneck lies.

And if it is too fast, then you can use `System.nanoTime()` to get nanoseconds. That should be enough time resolution. ;) — brimborium, Nov 08 '12 at 14:23
well, that can help for when my project is not yet compiled to an executable .jar file. But, in eclipse, I don't have that problem. So, should I use showMessageDialogs instead? — JordyV, Nov 08 '12 at 14:31

score 0 · Answer 3 · answered Nov 08 '12 at 14:41

0

Advise: use another database, like Derby, hsqldb. They are not so different from MSAccess, (= can use a file based DB), but perform better (than JDBC/ODBC). And can even be embedded in the application (without extra installation of the DB).

answered Nov 08 '12 at 14:41

Joop Eggen

107,315
7
83
138

Would love to do so. Even considered using SQLite, but I just can't find the right information all by myself to use and implement it in my program.. – JordyV Nov 08 '12 at 14:47

Huge time accessing database from Java

3 Answers3