Tag Archives: SQL

iOS

SQLite FMDB Batch / Bulk Inserts

Leave a comment

SQLite
I use FMDB framework to work with SQLite database on iOS. Sometimes you need to insert many rows, many objects that come from a server to your local database. It can take some time. When I was inserting rows one by one, the method took 50 seconds to finish inserting about 1300 rows. So I decided to find a faster solution.

And found it here. To insert many rows faster you should use transactions. It is very easy, you shouldn’t use complex approaches, like preparing huge SQL statements. I got 0.5 seconds as a result on iPhone 3GS. Here is more sophisticated approach to optimize insert of millions of rows, but it’s not my case, I’m satistfied with 0.5 second result.

_72483466_clockthinkstock

This is a sample code:

I have a class RRRegion, which objects I am inserting. I open database, then begin a transaction, execute as many updates as I have objects and commit a transaction, close database.

I should mention, that when I was using caching of statements by FMDB, I got strange leak statement errors, so I don’t cache them. I don’t have this line:

This is how I measured the time it takes:

iOS

SQLite on Mac OS X examples

Leave a comment

Here I decided to write some SQLite(SQLite on wiki) on Mac OS X examples of code for creation of database and database management.

1. Go to the folder where you want to create SQLite database (all this is written on Applications/Services/Terminal):

cd /Users/user/Documents/

2. Create database:

sqlite3 SimpleVocabulary.sql

3. Create table in that database:

CREATE TABLE Cards(CardID VARCHAR(5) PRIMARY KEY, Word VARCHAR(1000), Translation VARCHAR(3000), MemoStatus VARCHAR(1));

4. Insert values to the table:

INSERT INTO Cards VALUES ("1", "Mother", "Mama", "Y");

5. Select values from table:

SELECT * FROM Cards;

Another code examples:

1. cd /Users/user/Documents/
2. sqlite3 TexDatabase.sql
1.SELECT * FROM Lessons;
2.SELECT COUNT(*) FROM Lessons;
3.DELETE FROM Lessons;
4.CREATE TABLE Lessons(LessonID VARCHAR(5) PRIMARY KEY, LessonGroup VARCHAR(5), LessonTopic VARCHAR(100), LessonText VARCHAR(20000), LessonCode VARCHAR(100), LessonPicture VARCHAR(100));
5.Drop table Lessons;
6..schema

Here .schema command allows you to look at your tables structure.

Another example:
1. cd /Users/user/Desktop/Alwawee/AlwaweeApps/Бизнес-цитаты/Database
2. sqlite3 BusCitDatabase.sql
3. sqlite> CREATE TABLE BusCits(ID VARCHAR(5) PRIMARY KEY, Text VARCHAR(3000), Author VARCHAR(500));
4. sqlite> .quit
5. SELECT COUNT(*) FROM BusCits;
6. SELECT COUNT(*) FROM BusAuthors;
7. sqlite> CREATE TABLE BusAuthors(AuthorID VARCHAR(5) PRIMARY KEY, AuthorName VARCHAR(100), AuthorInfo VARCHAR(3000));
8. SELECT COUNT(*) FROM BusAuthors;
9. DELETE FROM BusAuthors;
10. DELETE FROM BusCits;
11. SELECT DISTINCT Author FROM BusCits WHERE Author NOT IN(SELECT AuthorName FROM BusAuthors);
12. cd /Users/user/Documents/
13. cd SimpVoc
14. sqlite3 TransWords.sql
15. INSERT INTO Cards VALUES ("1", "Mother", "Madre", "Y");
16. INSERT INTO Cards VALUES ("2", "Example", "Ejemplo", "Y");
17. INSERT INTO Cards VALUES ("3", "World", "Mundo", "Y");
18. SELECT * FROM Cards;

Here .quit command quits sqlite> mode on your Terminal.