Performance for large databases with 10+ million entries

[Typ0genius]

Hello,

what level of performance can be expected when working with large databases, for example when inserting and querying millions of entries? I am currently using Core Data, but I’m not satisfied with its performance, which noticeably degrades at this scale. Most of the data is structured as one-to-many relationships. This may be partly due to my own lack of experience, but also due to limitations of the framework itself.

How would you expect SQLiteData to perform in comparison in such scenarios? As a developer, what should I pay particular attention to when working with SQLiteData? For example, Core Data requires saving the context regularly when inserting large batches, otherwise memory consumption grows rapidly.

I would greatly appreciate any insights.

[mbrandonw]

Hi @Typ0genius, it's hard to say without knowing more about the schema you are dealing with, but in general I think it is fair to say that using SQLite directly is going to generally be faster than Core Data, which uses SQLite under the hood but has layers of abstraction built on top. And our SQLiteData library also has abstractions built on top of SQLite, but it's not much beyond some type safety and decoding logic, which is a bare minimum of what you want since SQLite is largely an untyped database and its API is built in C.

There absolutely are people out there using SQLite for databases that have hundreds of millions of rows and such that the database file is tens of gigabytes. The usual limiting factor of SQLite's performance is how well your schema is designed, such as making sure you have indices that turn the kinds of queries you write into fast look ups.

As one concrete example of speed differences between SQLite and SwiftData: By tweaking the settings of SQLite in various ways, someone was able to insert 100 million rows into a SQLite database in 34 seconds (source). And according to this, it can take upwards of 7 seconds to insert 100 thousand rows into SwiftData. So, that very rough comparison is essentially a 200x speed up of SQLiteData over SwiftData. This by no means says that SQLite is always 200x faster than SwiftData. Such a claim does not even make sense. But I think it is safe to say that SQLite is much faster than SwiftData when it comes to inserting many rows at once.

If you provide some concrete info about your schema (i.e. the models that are persisted to Core Data), as well as the rough number of rows in the database for each model, then I might be able provide more concrete information.

[Typ0genius]

@mbrandonw Thanks for the quick reply. I quickly built a sample project based on your reminders demo, and this is my schema. There may be multiple variations of the SampleTable, but they all reference the DBApp and no other relations are involved. For now, there is no need to join multiple variations of SampleTable. How many rows a SampleTable will actually contain is hard to say, but in the worst case I expect somewhere between 10 and 100 million. The data is provided as DataFrames, and I would also prefer to read them in that format.

[mbrandonw]

Hi @Typ0genius, thanks for sharing some code, but I'm just not entirely sure what I am looking at. Is this sample app supposed to output anything?

I can say that there are definitely improvements you can make to this code, but a lot of it has to do with better domain modeling. For example, you seem to be using the title of a DBApp as a primary key:

let existingApp =
  try DBApp
  .where { $0.title.eq(appTitle) }
  .fetchOne(db)

So at the very least I would expect it to have a unique index on that column. That would allow you to replace a whole bunch of code that looks like this:

let existingApp =
try DBApp
  .where { $0.title.eq(appTitle) }
  .fetchOne(db)

if let existingApp = existingApp {
  appTitleToIDMap[appTitle] = existingApp.id
} else {
  let newAppID = UUID()
  try DBApp.insert {
    DBApp.Draft(id: newAppID, title: appTitle)
  }
  .execute(db)
  appTitleToIDMap[appTitle] = newAppID
}

…with a more idiomatic "INSERT … ON CONFLICT DO NOTHING" in SQL:

appTitleToIDMap[appTitle] = try DBApp.insert {
  DBApp.Draft(title: appTitle)
} onConflict: {
  $0.title
} doUpdate: { _ in
  /* do nothing */
}
.returning(\.id)
.fetchOne(db)

This will try to insert a new DBApp based on the title if that title does not already exist. If it does exist, it just silently ignores the insert. And will ultimately return the ID of the newly inserted row or existing row. And then finally assigns that to the appTitleToIDMap.

There are a number of places in your code that you can swap out Swift logic for munging data together for idiomatic and declarative SQL statements.

I also recommend refactoring some of your code so that instead of looping over a collection and performing an insert { model } for each model, you build up an array of those models and then do a single insert { models }. It is generally more performant to insert a bunch of data into a SQLite database with a single query than executing a whole bunch of queries to insert a bunch of data.