I am trying to developing an app. In this app I need a local database. I choose sqflite database but it is very slow. It is taking so much time to fetch and insert data. It is my database helper class. As you can see in code that I have lot of rows. I am new at asking question if you need more information you can comment.

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart' as sqf;

class DataBaseHelper {
  static Future<sqf.Database> database() async {
    final dbPath = sqf.getDatabasesPath();
    return sqf.openDatabase(
      join(await dbPath, 'habits_record.db'),
      onCreate: (db, version) async {
        await db.transaction((txn) async {
          var batch = txn.batch();
          await txn.execute(
            '''CREATE TABLE habitTable(id INTEGER PRIMARY KEY, title TEXT, reason TEXT,plan TEXT, iconData TEXT,hour INTEGER, minute INTEGER, notificationText TEXT, notificationId INTEGER,alarmHour INTEGER, alarmMinute INTEGER)''',
          );

          await txn.execute(
            '''CREATE TABLE event(id TEXT PRIMARY KEY, dateTime TEXT, habitId INTEGER)''',
          );
          await txn.execute(
            '''CREATE TABLE note(id TEXT PRIMARY KEY, dateTime TEXT, habitId INTEGER, Note TEXT)''',
          );
          await batch.commit();
        });
      },
      version: 1,
    );
  }

  static Future<void> insertNote(Map<String, Object> data) async {
    final db = await DataBaseHelper.database();
    db.insert('note', data, conflictAlgorithm: sqf.ConflictAlgorithm.replace);
   
  }

  static Future<void> deleteNote(String id) async {
    final db = await DataBaseHelper.database();
   
    await db.delete(
      'note',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  static Future<List<Map<String, dynamic>>> fetchAndSetNotes() async {
    final db = await DataBaseHelper.database();

    return await db.query('note');
  }

  static Future<void> updateNote(Map<String, Object> newNote) async {
    final db = await DataBaseHelper.database();
    final batch = db.batch();
    batch.update(
      'note', newNote, where: 'id = ?',
      whereArgs: [newNote['id']],
    );
 
    batch.commit(continueOnError: true);
  }

  static Future<void> insertEvent(Map<String, Object> data) async {
    final db = await database();
    await db.insert('event', data,
        conflictAlgorithm: sqf.ConflictAlgorithm.replace);
  }

  static Future<void> deleteEvent(String id) async {
    final db = await DataBaseHelper.database();

    await db.delete(
      'event',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  static Future<List<Map<String, dynamic>>> fethEvent() async {
    final db = await DataBaseHelper.database();

    return await db.query('event');
  }

  static Future<void> insertHabit(Map<String, Object> data) async {
    final db = await database();
    await db.insert('habitTable', data,
        conflictAlgorithm: sqf.ConflictAlgorithm.replace);
  }

  static Future<List<Map<String, dynamic>>> habits() async {
    final db = await DataBaseHelper.database();

    return await db.query('habitTable');
  }

  static Future<void> deleteHabit(int id) async {
    final db = await DataBaseHelper.database();

    await db.delete(
      'habitTable',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  static Future<void> updateHabit(Map<String, Object> oneHabit) async {
    final db = await DataBaseHelper.database();

    await db.update(
      'habitTable',
      oneHabit,
      where: 'id = ?',
      whereArgs: [oneHabit['id']],
    );
  }
}

CodePudding user response：

If sqflite has not the speed you want , you can use

Here is the ObjectBox vs Hive vs
Sqflite performance benchmarks.

You can decide which one you want to go with.

In CRUD operations, you can see that sqflite is very slow when comparing with others.