Reputation: 7265
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
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
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
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