“But for a replacement for fopen ()”
- About SQLite

And also, most likely, under Android, BlackBerry and in the sandbox of browsers for web applications, but I did not check.
')

Why it may be necessary to work with SQLite directly?

Any experienced iOS developer immediately reproaches me for using SQLite directly (or rather, not directly, but through FmDb, but it's almost the same as directly). He will say that you need to use CoreData, because It does all sorts of nishtyakov automatically, such as Undo and Redo. And in it you can draw beautiful schemes, which are then nice to show to the customer. And in android, for example, there is OrmLite.

And I agree - but until then, until you have the base has passed, say, for 10 tables of 500,000 entries in each. And if there are 52 tables, and there are especially fat tables of a million and more? And the database needs to be synchronized with the server through the third format, besides the customer is critical, will the synchronization last for an hour or five? If you have met tasks of this size, welcome to the cut! If you haven’t met it, it’s the same, because no one is immune from projects with big data, even if they are mobile and less.

Sequence of query operators

The fact is that when we are taught to work with a database, in most cases we are taught to work with the best enterprise solutions. For example, I was taught at the Institute on Oracle, someone - on MS SQL. But SQLite is many times simpler - for example, it follows from the epigraph to an article taken from the official site of SQLite.

Quite by chance I noticed that

SELECT * FROM tablename WHERE col1 LIKE '%string%' AND col2 = 123456

works 3-4 times slower than

SELECT * FROM tablename WHERE col2 = 123456 AND col1 LIKE '%string%'

on a table of, say, 300,000 records. We just swapped the operators, and how the result has changed!

In the textbooks on databases, most likely, they don’t even focus on such features, and they do it right - in all enterprise solutions there are query optimizers. For example, on MS SQL Server 2008 Web Edition, there is no difference on the same data and the same requests.

But on SQLite there is. This needs to be remembered. In the SQLite world, more “simple” operations should always go to the left of more “complex” ones.

SQLite database can and should be indexed too

When thinking of SQLite as an alternative to fopen, a database without stored procedures, semaphores and users, you forget that it, like any normal database, supports indexes. There is so much written about them that you should not focus on syntactic features - just remember that as soon as the size of the database exceeds 50,000 lines, it needs to be indexed. And when using complex queries - before.

I will allow myself only a small note - indexing is best done after the basic queries are written, based on their analysis. When designing a database, when the developer does not know by heart the entire business logic of the application, he may be mistaken as to which field will most often search / select. However, having SQL queries before your eyes, writing the correct index is worth nothing.

If you often do a selection of several tables, it makes sense to cache the data

On one of the projects, at the start of the application, it was necessary to ask the user to choose the car he wants to work with. In order to create a complete description of the machine, it was necessary to refer to several tables:

• Year of issue
• Manufacturer
• Model
• Equipment
• Some other technical characteristics that the customer wished to show when choosing a machine. For example, MPG (miles per gallon, an analogue of our kilometers per liter).

Making a request on five tables and compiling a list of machines, we slowed down the iPhone to a response of 6-8 seconds on each picker. There are two ways out - when you first start (in our case, when synchronizing), you can either create a view with all the possible data, or, if it is more convenient, save data objects directly to your hard disk. Also once, at a convenient moment when the user knows about the delay and is ready to wait.

It is more convenient to save objects if the presentation would have to include for each, say, a machine, several fields from the picking table. In the database, this would lead to the inevitable duplication of lines (if you connect one-to-many to several lines of picking parameters, you would have to create several identical lines with the vehicle ID and other parameters), and in the object all data will be stored in one copy.

SQLite - single-threaded database

Not directly related to optimization, but this also should not be forgotten. Accessing a SQLite database of two threads simultaneously will inevitably cause crash. There are two ways out:

• Synchronize calls with the @synchronized directive. This is if it's too late to change the architecture, as I did;
• If the task is laid out at the design stage, start a query manager based on NSOperationQueue. It insures against errors automatically, and what is done automatically is often done without errors.

In general, it is always better to access the database from minor threads, even if the UI thread does nothing except scrolling the wait indicator. Time to write delegate methods and asynchronize business logic will more than pay for the positive user experience.

Conclusion

All that I have listed above, it was only a special case. There is no universal optimization task that can be simulated in 1.5 days or 10% of the total project time. This has to do as needed. The main rule that has always helped me to choose the right way to optimize is that the time spent on the sample should not increase with the size of the base, except in special cases . It should remain about the same as a hundred records, and a hundred thousand.

And another rule, the favorite phrase of my boss - do not optimize what does not need optimization. Often it does not matter to the user whether your code will be executed one millisecond or one hundred, and it is important for the customer whether you delayed the project for a day or passed it on time.