1

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

Josef
  • 43
  • 7

0 Answers0