Using (DbDataReader reader = await command.ExecuteReaderAsync()) Using (SqliteCommand command = DbConnection.GetConnection().CreateCommand())Ĭommand.CommandText = "SELECT ID, FirstName, LastName FROM Users" SELECT ID, FirstName, LastName FROM Users Significantly cheaper is the access by ordinal. As it turned out, that is a really expensive way. I used to be happy with just accessing the columns by their name. Mapping: Access columns in your data reader by ordinal, not by column nameĮven if your database is accessed very fast, one single piece is left: the correct mapping. Index columns used in where clauses or joinsĬreate single-column indexes as well as composite indexes for columns you frequently use as filters. I didn't find any serious performance loss with it. Use a primary key for every single table, more than ever when you're joining that table in your queries.Īnd by the way: if you're using Guids as identifiers, you can store them as text and make them a primary key, too. To make it short: put the table you're getting more results from on the left. But from my experience the most important point is to organize your joins the right way, as described in the document. You can learn a lot of optimizing your queries for the SQLite Query Planner. Instead write out every single column you want to select. But at the end of the day you will be able to access your database without blocking your UI thread.
Just use it! But make sure you have some time to change your codebase, as that will trigger to the top of every single call at the UI of your application. The SqliteCommand offers async alternatives to ExecuteNonQuery(), ExecuteScalar() and ExecuteReader(). I am using the unit of work pattern, so I collect all the operations as commands in _databaseCommands, and commit them at once. Using (var transaction = connection.BeginTransaction())įoreach (SqliteCommand databaseCommand in _databaseCommands)ĭatabaseCommand.Transaction = transaction Īwait databaseCommand.ExecuteNonQueryAsync() SqliteConnection connection = DbConnection.GetConnection()
Using transactions for any kind of write operation (UPDATE, DELETE, INSERT) improved the performance dramatically, all the more when using multiple of these operations at one go. I am no database specialist so it could be possible there is something set which is useless, or something is still missing.
#Reading and writing data in db sqlite ios trial
To be honest, the following options are the result of trial & error. If (_connection.State = ConnectionState.Closed)Īs you're using a single connection across different threads sooner or later, set Serialized as the threading mode before any connection is opened in the FinishedLaunching()-method of your AppDelegate: SqliteConnection.SetConfig(SQLiteConfig.Serialized) Set PRAGMA Options Public static SqliteConnection GetConnection() Private static SqliteConnection _connection Using a single connection solves that problem, and it's not expensive in any way as your app is the only client, that is accessing the database. That means that every connection is trying to get exclusive access to that file (depending on the level of protection against data-loss you have configured via pragma-options). Use a single connection for your whole application Today I am not talking about 200-600 milliseconds for that kind of selects mentioned above, but about 10-20 milliseconds. At the end some things in combination did the trick. I tried literally everything I found online, not everything had an effect. What sounds fast at the first glance is a real show-stopper when you're swiping through a set of pictures and every swipe delays about half a second or more, which always feels like 1-2 seconds to a user. A huge problem.įetching my data with ADO.NET () was very time consuming, which means that simple selects of a few dozen records could last 200-600 milliseconds. I didn't think that much about performance - iPhones are no super computers, that's for sure, but a few hundred records should not be a problem.Īs it turned out, it was a problem.
#Reading and writing data in db sqlite ios Offline
As my application works completely offline only synchronizing with a server every minute, there are a lot more of reads than writes. I had only a dozen tables and a few hundred records to store. Wherever you look, everyone seems to recommend that little but powerful database system. For storing data locally within an iOS app there is almost no alternative to SQLite.
0 kommentar(er)
