Dataset.Update() to local SQLite Database takes a long time

See more:

VB

SQLite

dataset

Hi all,

my sqlite media database needs a long time to update (I'm using dataset.update()) in vb.net. I have a master table with 30 columns. This table is filled with the primary keys of the other 29 tables.

Each table of the other 29 tables has 2 columns. 1. column with the primary key related to the master table and 2. column contains the value.

At application start i open the connection for one time and load the complete database with all tables into a dataset (which is not a problem). Then i create for each table in the dataset a new sqlitedataadapter and put that into a list(of sqlitedataadapter) (also not a problem).

Let say my media database has no entries in there. Then i add a new folder which contains 300 mp3 files.

When i do no update during the search it takes around 3 seconds to add all the files. When i do an update after the search it takes more than 5 seconds and my harddrive works like mad. But the media database file grows only from 67kbyte to ca. 90kbyte.

Why it takes so long to update the database?

Here is the code

Please note that I'm using predefined list(s) of tables, columns and sqlitedataadapters to do the update. Especially i'm using the for loop.

Sub DATABASE_UPDATE(Optional Table As Integer = Nothing)
	If Table = Nothing Then
		For i As Integer = 0 To MEDIATABLELIST.Count - 1
			Dim MEDIASQLITECOMMANDBUILDER As New SQLiteCommandBuilder(MEDIASQLITEDATAADAPTERLIST.Item(i))
			MEDIASQLITEDATAADAPTERLIST.Item(i).Update(MEDIADATABASE, MEDIATABLELIST.Item(i))
		Next
	Else
		Dim MEDIASQLITECOMMANDBUILDER As New SQLiteCommandBuilder(MEDIASQLITEDATAADAPTERLIST.Item(Table))
		MEDIASQLITEDATAADAPTERLIST.Item(Table).Update(MEDIADATABASE, MEDIATABLELIST.Item(Table))
	End If
End Sub