CODEWITHSUNDEEP
javaswingsqlite
GOXR3PLUS
GOXR3PLUS

Reputation: 7265

Bad SQLITE update Performance

I am fairly new in SQL(now working on SQLITE application) and it is a section in my app when i try this piece of Code:

public void addSong(LibrarySong song){

    for(int i=0; i<intoPanel.getComponentCount(); i++)  //Check if doublicates exist
        if(song.getName().equals(intoPanel.getComponent(i).getName()))
            return;


    intoPanel.add(song);    

    //Add the song to the database table
    try{
        Container.DataBase.connection.prepareStatement("INSERT INTO '"
                + Container.libWindow.libInfoWindow.currentLib.getName()+ "'"   //Table Name
                + " (PATH,STARS,DATE,HOUR) VALUES ('"
                + song.getName() + "'," + song.stars + ",'"
                + song.dateCreated + "','" + song.hourCreated + "')").executeUpdate();
    }catch(SQLException sql){ sql.printStackTrace(); };
}

The Problem: The above method just add the song to a Jtable and then to database table.The problem is that the performance is too bad for the database.Why might this happen? i use the statement somewhere wrong or i have to to the update with different way?Thanks for reply.

Upvotes: 0

Views: 571

Answers (3)

Joop Eggen
Joop Eggen

Reputation: 109623

I think, that the component usage is suboptimal: the first for-loop.

Use a

Set<String> songNames = new HashSet<>();

if (songNames.contains(song.getName())) { return; }
songNames.add(song.getName());

Or maybe a Map for other uses.

And use the prepared statement with placeholders ?. This escapes single quotes too. And is safer.

Upvotes: 0

CL.
CL.

Reputation: 180310

The most expensive part of accessing a database is not the execution of the statement itself, but all the synchronizations done for transactions.

By default, each SQL command is put into an automatic transaction, so you get the overhead for all of them.

If you have multiple updates, you should group them into a single transaction:

Container.DataBase.connection.setAutoCommit(false);
...
for (...)
    addSong(...);
Container.DataBase.connection.commit();

Upvotes: 1

Tschallacka
Tschallacka

Reputation: 28742

Basically the problem boils down to that every write to disc is done by sqlite itself in default mode

you could enable

PRAGMA journal_mode = WAL
PRAGMA synchronous = NORMAL

To make use of the operating system disc buffer. Just remember to flush/commit everything regularly or at break points after inserts. Only risk is if there is sudden powerloss or reboot, your database might end up corrupted.

https://www.sqlite.org/atomiccommit.html

Upvotes: 1

Related Questions