Windows performance issue

[nmoore-genetec]

Hi there,

I have an application which uses Drift that works pretty well, I've implemented many of the required features, etc.

But one of the things I need to do is a bulk ingestion of data from a text file, and it was taking nearly 20 seconds to accomplish this. So I decided to make a bare-bones console application using simply the Dart sqlite3 package to determine if there was weirdness going on in Drift.

I've attached the console application here as a zip file:

simple sqlite ingester (1).zip

It takes about 12 seconds to complete the insertion. I've now run the application both on Android devices (as a Flutter app) and my colleague's Mac (console app, the exact one found in the ZIP above) and they only take about 3-4 seconds. I've also tried running an .exe/AOT version off the application on my device, still takes 12 seconds.

As I understand it, sqlite3.dart is very minimally a use of FFI in order to use sqlite3's API. Any ideas why it would be so slow on Windows ?

I have also tried on my machine: a C# console application using a sqlite3 Nuget package, and sqlite3 CLI's .import functionality on a CSV file with the same data. In this series of tests, the resultant databases were identical (i.e. all the same default PRAGMAs, exact same resultant file size) but again these only took 3 seconds to complete.

[simolus3]

I'll take a more in-depth look at this soon, but this may have a similar cause as this one, where adopting prepared statements for inserts that are run multiple times turned out to make things much faster. Could you check if that may be work here as well?

[nmoore-genetec]

So I implemented that as follows:

....
  final lines = file.readAsLinesSync();
  final statementParameters = makeStatementParameters(lines);

  var stopwatch = Stopwatch()..start();
  db.execute('BEGIN');
  
  var statement = db.prepare(
      'INSERT INTO book_items (category, state, book, start_date, end_date) VALUES (:cat, :st, :book, :sd, :ed)');
  for (final params in statementParameters) {
    statement.executeWith(params);
  }

  db.execute('COMMIT');

  stopwatch.stop();
  print('Executing SQL took: ${stopwatch.elapsedMilliseconds} ms');

  db.dispose();
}

List<StatementParameters> makeStatementParameters(Iterable<String> lines) {
  List<StatementParameters> statementParameters = [];

  for (final line in lines) {
    final parts = line.split(';');
    if (parts.length == 5) {
      statementParameters.add(StatementParameters.named({
        ":cat": parts[0],
        ":st": parts[1],
        ":book": parts[2],
        ":sd": parts[3],
        ":ed": parts[4]
      }));
    }
  }

  return statementParameters;
}

Which consistently returns about 7.8 seconds. 25% improvement for almost no effort is significant, so firstly thank you.

Do you think this could improve further ? Is it common that Dart ffi packages have performance differences between platforms, (and particularly bad for Windows) ?