I am using SQLite-Net Extensions on my Windows phone 8.1, insertion of 2000 rows tooks over 20 minuts due speed of my SD cart I think. But selection from database seems to be very slow too. Selecting of all my (2000) rows by ID(int) tooks about 120 seconds. I tried to execute SQLITE PRAGMAS right after my database was opened. It had no efect to speed of selects.
dbConnection.ExecuteScalarAsync<int>("PRAGMA main.locking_mode = EXCLUSIVE").Wait();
dbConnection.ExecuteScalarAsync<int>("PRAGMA main.synchronous = NORMAL").Wait();
dbConnection.ExecuteScalarAsync<int>("PRAGMA main.journal_mode = WAL").Wait();
dbConnection.ExecuteScalarAsync<int>("PRAGMA main.cache_size = 10000").Wait();
dbConnection.ExecuteScalarAsync<int>("PRAGMA main.temp_store = MEMORY").Wait();
I am using async version of SQLite Net extensions: SQLite.Net.Async.SQLiteAsyncConnection
Could you give me some advice how to use sqlite with SQLite-Net Extensions by right and fast way?
This is example of my recursive method for selecting files from subfolders:
private async Task<List<SongModel>> GetAllSongsFromFolderHelp(int folderID,bool fromSubFolders,bool appendChilderen ,List<SongModel> filesAcc)
{
List<DbFFModel> files = await dbConnection.QueryAsync<DbFFModel>("Select * from DbFFModel where IDParent = ?", folderID); //select
foreach (DbFFModel ff in files)
{
if (ff.IsFile)
{
SongModel tmp = await this.GetSongByFileIDAsync(ff.ID, appendChilderen);
if(tmp != null)
filesAcc.Add(tmp);
}
else if (fromSubFolders)
await GetAllSongsFromFolderHelp(ff.ID, fromSubFolders,appendChilderen, filesAcc);
}
return filesAcc;
}
This is example of inserting of the records:
IReadOnlyList<StorageFile> files = await storageFolder.GetFilesAsync();
foreach (StorageFile storageFile in files)
{
bool isSupportedMusic = Classes.SupportedFiles.IsSupportedMusic(storageFile.ContentType, storageFile.FileType);
if (isSupportedMusic)
{
BasicProperties fileProperties = await storageFile.GetBasicPropertiesAsync();
int fileID = await dbConnection.ExecuteScalarAsync<int>("select ID from DbFFModel where Path = ?", storageFile.Path);
DbFFModel dbFile = await dbConnection.FindAsync<DbFFModel>(fileID);
bool updateFile = true;
if (dbFile == null || (!DateTimeComparer.IsEqualRounded(dbFile.DateModified, Convert.ToDateTime(fileProperties.DateModified.ToString()))))
{
if (dbFile == null)
{
updateFile = false;
dbFile = new DbFFModel();
}
dbFile.Mark = true;
dbFile.IDParent = dbFolder.ID;
dbFile.Path = storageFile.Path;
dbFile.IsFile = true;
dbFile.DisplayName = Path.GetFileName(storageFile.Path);
dbFile.DateCreated = storageFile.DateCreated.DateTime;
dbFile.DateModified = fileProperties.DateModified.DateTime;
if (updateFile)
await dbConnection.UpdateAsync(dbFile);//update row
else
await dbConnection.InsertAsync(dbFile);//insert row
await MakeSongDb(dbFile.ID, storageFile);
}
else
{
await UpdateMark(dbFile);
}
}
}
Thanks