iOS SQLiteNormalizedCache cold start can be slower than reloading data from backend

Client SDKs
,
1

I have a simple query that loads a list of Visa card transactions. I’m currently testing with a large list of 4922 TransactionFragments. (Queries & Fragments shown at bottom of this post).

As you can see from the following table: cold start queries from SQLiteNormalizedCache are an order of magnitude slower than doing a requery, and **CAN EVEN BE SLOWER THAN LOADING FRESH FROM THE BACKEND.
**
How do I fix this? I need to instantly run this query when the app opens (it’s the first thing the user sees) so I can’t preload.

TIMING

  • Fresh load from backend (AWS, Node, Postgres): 4-8 seconds
  • Cold start load from SQLiteNormalizedCache: 6 seconds
  • Requery (after cold start) from SQLiteNormalizedCache: 0.95 seconds
  • Query from InMemoryNormalizedCache: 0.72 seconds

I added return try? CacheKeyInfo(jsonValue: object["id"]) to my SchemaConfiguration and that changed nothing.

query Transactions($updatedSince: String) {
transactions(updatedSince: $updatedSince) {
endCursor
hasNext
totalTransactions
transactions {
…TransactionFragment
}
}
}

type TransactionFragment {
id: ID!
createdAt: String!
updatedAt: String!
deletedAt: String
transactionType: TransactionType!
transactionStatus: TransactionStatus
declineReason: String
actionRanAssignToEnvelopes: Boolean!
actionRanSendNotification: Boolean!
name: String!
nameOriginal: String
amount: Int!
merchantId: String
mcc: String
cardId: String
cardLast4: String
notes: String
checkId: String
checkDepositError: CheckDepositError
addressCity: String
addressState: String
addressZip: String
addressCountry: String
addressLine1: String
addressLine2: String
locationLat: String
locationLong: String
locationUnstructured: String
merchantLogo: String
merchantPhone: String
merchantWebsite: String
fundedByTransactionId: ID
toEnvelopeId: ID
fromEnvelopeId: ID
}

2

@josh_josh_josh I have been starting work on a pretty big caching overhaul we are planning to do, although even for the current caching setup I don’t think 5,000 entries should be taking multiple seconds even on cold start. I am going to look back through our current caching code and see if anything stands out performance wise for SQLite, may not be possible but if you could provide a sample project that recreates this with mock data it would make it much easier to try and track down a potential issue.

3

@josh_josh_josh I have a PR up to do some batching of select queries as a general performance improvement, it’s possible this may help with your issue if you are willing to point your code to the PR branch and give it a try I’d love to hear the results. Adding batching to sqlite selects by BobaFetters · Pull Request #725 · apollographql/apollo-ios-dev · GitHub

4

@ZachF-Apollo I got a chance to test your commit today and it basically has no effect. The fromCache load time isn’t any better or worse for my specific query :man_shrugging:

So sorry, but I’m running a solo engineering org over here so I don’t have the bandwidth to reproduce in a standalone project. Totally get how that would help. I figured out a workaround to load the most recent 200 records first and trick my users into thinking app startup is loading faster than it actually is so I’m running with that for now. Happy to test more one-off branches if you ever need. Including if you want to breadcrumb one with tons of print statement timestamps or something.