{"id":2339,"date":"2014-09-30T19:38:09","date_gmt":"2014-09-30T15:38:09","guid":{"rendered":"https:\/\/blog.alwawee.ru\/?p=2339"},"modified":"2014-09-30T19:53:19","modified_gmt":"2014-09-30T15:53:19","slug":"sqlite-fmdb-batch-bulk-inserts","status":"publish","type":"post","link":"https:\/\/blog.alwawee.ru\/?p=2339","title":{"rendered":"SQLite FMDB Batch \/ Bulk Inserts"},"content":{"rendered":"<p><a href=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png\" alt=\"SQLite\" width=\"300\" height=\"142\" class=\"aligncenter size-medium wp-image-2346\" srcset=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png 300w, https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-1024x484.png 1024w, https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite.png 1280w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\nI use <strong>FMDB<\/strong> framework to work with <strong>SQLite<\/strong> database on <strong>iOS<\/strong>. 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 <strong>50 seconds<\/strong> to finish inserting about <strong>1300 rows<\/strong>. So I decided to find a faster solution. <\/p>\n<p>And found it <a href=\"http:\/\/stackoverflow.com\/questions\/11784143\/fastest-way-to-insert-many-rows-into-sqlite-db-on-iphone\" title=\"http:\/\/stackoverflow.com\/questions\/11784143\/fastest-way-to-insert-many-rows-into-sqlite-db-on-iphone\" target=\"_blank\">here<\/a>. To insert many rows faster you should use <strong>transactions<\/strong>. It is very easy, you shouldn&#8217;t use complex approaches, like preparing huge <strong>SQL<\/strong> statements. I got <strong>0.5 seconds<\/strong> as a result on <strong>iPhone 3GS<\/strong>. <a href=\"http:\/\/stackoverflow.com\/questions\/1711631\/how-do-i-improve-insert-per-second-performance-of-sqlite\" title=\"http:\/\/stackoverflow.com\/questions\/1711631\/how-do-i-improve-insert-per-second-performance-of-sqlite\" target=\"_blank\">Here<\/a> is more sophisticated approach to optimize insert of millions of rows, but it&#8217;s not my case, I&#8217;m satistfied with 0.5 second result.<\/p>\n<p><a href=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/72483466_clockthinkstock.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/72483466_clockthinkstock-300x168.jpg\" alt=\"_72483466_clockthinkstock\" width=\"300\" height=\"168\" class=\"aligncenter size-medium wp-image-2344\" srcset=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/72483466_clockthinkstock-300x168.jpg 300w, https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/72483466_clockthinkstock.jpg 464w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>This is a sample code: <\/p>\n<pre>\r\n+ (BOOL)addToDbRegionArray:(NSArray *)regionArray\r\n{\r\n  FMDatabase *db = [[DBConnection sharedInstance] db];\r\n  \r\n  if ( ! [db open] ) {\r\n    return NO;\r\n  }\r\n  \r\n  [db beginTransaction];\r\n  \r\n  for (RRRegion *region in regionArray) {\r\n    [db executeUpdate:@\"INSERT INTO regions (region_id, name, city_id, country_id, has_metro, is_popular) VALUES (?, ?, ?, ?, ?, ?);\" withArgumentsInArray:@[@(region.sid), region.name, @(region.cityId), @(region.countryId), @(region.hasMetro), @(region.isPopular)]];\r\n  }\r\n  \r\n  [db commit];\r\n  [db close];\r\n  \r\n  return YES;\r\n}\r\n<\/pre>\n<p>I have a class <strong>RRRegion<\/strong>, 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. <\/p>\n<p>I should mention, that when I was using caching of statements by <strong>FMDB<\/strong>, I got strange <strong>leak statement<\/strong> errors, so I don&#8217;t cache them. I don&#8217;t have this line: <\/p>\n<pre>\r\n[db setShouldCacheStatements:NO];\r\n<\/pre>\n<p>This is how I measured the time it takes: <\/p>\n<pre>\r\nNSDate *start = [NSDate date];\r\nNSArray *jsonArray = (NSArray *)JSON;\r\nNSMutableArray *regionArray = [NSMutableArray new];\r\nfor (NSDictionary *jsonDic in jsonArray) {\r\n  RRRegion *region = [[RRRegion alloc] initFromDic:jsonDic];\r\n  [regionArray addObject:region];\r\n}\r\n\r\n[RRRegion addToDbRegionArray:regionArray];\r\n\r\nNSDate *finish = [NSDate date];\r\nNSTimeInterval interval = [finish timeIntervalSinceDate:start];\r\nNSLog(@\"Time consumed to add regions: %f\", interval);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[135,133,134,11,132],"class_list":["post-2339","post","type-post","status-publish","format-standard","hentry","category-ios","tag-bulk-insert","tag-fmdb","tag-insert","tag-sql","tag-sqlite"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQLite FMDB Batch \/ Bulk Inserts - Denis Kutlubaev<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.alwawee.ru\/?p=2339\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLite FMDB Batch \/ Bulk Inserts - Denis Kutlubaev\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.alwawee.ru\/?p=2339\" \/>\n<meta property=\"og:site_name\" content=\"Denis Kutlubaev\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/alwaweemobile\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-30T15:38:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-09-30T15:53:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png\" \/>\n<meta name=\"author\" content=\"Denis Kutlubaev\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Denis Kutlubaev\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339\"},\"author\":{\"name\":\"Denis Kutlubaev\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/#\\\/schema\\\/person\\\/58f1f1f57155b48afab83ba53b3e26a7\"},\"headline\":\"SQLite FMDB Batch \\\/ Bulk Inserts\",\"datePublished\":\"2014-09-30T15:38:09+00:00\",\"dateModified\":\"2014-09-30T15:53:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339\"},\"wordCount\":201,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blog.alwawee.ru\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/SQLite-300x142.png\",\"keywords\":[\"BULK INSERT\",\"FMDB\",\"INSERT\",\"SQL\",\"SQLite\"],\"articleSection\":[\"iOS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339\",\"url\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339\",\"name\":\"SQLite FMDB Batch \\\/ Bulk Inserts - Denis Kutlubaev\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/blog.alwawee.ru\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/SQLite-300x142.png\",\"datePublished\":\"2014-09-30T15:38:09+00:00\",\"dateModified\":\"2014-09-30T15:53:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/#\\\/schema\\\/person\\\/58f1f1f57155b48afab83ba53b3e26a7\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#primaryimage\",\"url\":\"https:\\\/\\\/blog.alwawee.ru\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/SQLite.png\",\"contentUrl\":\"https:\\\/\\\/blog.alwawee.ru\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/SQLite.png\",\"width\":1280,\"height\":606},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/?p=2339#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/blog.alwawee.ru\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLite FMDB Batch \\\/ Bulk Inserts\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/#website\",\"url\":\"https:\\\/\\\/blog.alwawee.ru\\\/\",\"name\":\"Denis Kutlubaev\",\"description\":\"iOS Development\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/blog.alwawee.ru\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/blog.alwawee.ru\\\/#\\\/schema\\\/person\\\/58f1f1f57155b48afab83ba53b3e26a7\",\"name\":\"Denis Kutlubaev\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg\",\"caption\":\"Denis Kutlubaev\"},\"description\":\"iOS Developer, creator of Tornado Browser and many other popular apps\",\"sameAs\":[\"http:\\\/\\\/dennis.alwawee.com\"],\"url\":\"https:\\\/\\\/blog.alwawee.ru\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLite FMDB Batch \/ Bulk Inserts - Denis Kutlubaev","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.alwawee.ru\/?p=2339","og_locale":"en_US","og_type":"article","og_title":"SQLite FMDB Batch \/ Bulk Inserts - Denis Kutlubaev","og_description":"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 [&hellip;]","og_url":"https:\/\/blog.alwawee.ru\/?p=2339","og_site_name":"Denis Kutlubaev","article_publisher":"https:\/\/www.facebook.com\/alwaweemobile","article_published_time":"2014-09-30T15:38:09+00:00","article_modified_time":"2014-09-30T15:53:19+00:00","og_image":[{"url":"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png","type":"","width":"","height":""}],"author":"Denis Kutlubaev","twitter_misc":{"Written by":"Denis Kutlubaev","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.alwawee.ru\/?p=2339#article","isPartOf":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339"},"author":{"name":"Denis Kutlubaev","@id":"https:\/\/blog.alwawee.ru\/#\/schema\/person\/58f1f1f57155b48afab83ba53b3e26a7"},"headline":"SQLite FMDB Batch \/ Bulk Inserts","datePublished":"2014-09-30T15:38:09+00:00","dateModified":"2014-09-30T15:53:19+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339"},"wordCount":201,"commentCount":0,"image":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339#primaryimage"},"thumbnailUrl":"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png","keywords":["BULK INSERT","FMDB","INSERT","SQL","SQLite"],"articleSection":["iOS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.alwawee.ru\/?p=2339#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.alwawee.ru\/?p=2339","url":"https:\/\/blog.alwawee.ru\/?p=2339","name":"SQLite FMDB Batch \/ Bulk Inserts - Denis Kutlubaev","isPartOf":{"@id":"https:\/\/blog.alwawee.ru\/#website"},"primaryImageOfPage":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339#primaryimage"},"image":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339#primaryimage"},"thumbnailUrl":"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite-300x142.png","datePublished":"2014-09-30T15:38:09+00:00","dateModified":"2014-09-30T15:53:19+00:00","author":{"@id":"https:\/\/blog.alwawee.ru\/#\/schema\/person\/58f1f1f57155b48afab83ba53b3e26a7"},"breadcrumb":{"@id":"https:\/\/blog.alwawee.ru\/?p=2339#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.alwawee.ru\/?p=2339"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.alwawee.ru\/?p=2339#primaryimage","url":"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite.png","contentUrl":"https:\/\/blog.alwawee.ru\/wp-content\/uploads\/2014\/09\/SQLite.png","width":1280,"height":606},{"@type":"BreadcrumbList","@id":"https:\/\/blog.alwawee.ru\/?p=2339#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blog.alwawee.ru\/"},{"@type":"ListItem","position":2,"name":"SQLite FMDB Batch \/ Bulk Inserts"}]},{"@type":"WebSite","@id":"https:\/\/blog.alwawee.ru\/#website","url":"https:\/\/blog.alwawee.ru\/","name":"Denis Kutlubaev","description":"iOS Development","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.alwawee.ru\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blog.alwawee.ru\/#\/schema\/person\/58f1f1f57155b48afab83ba53b3e26a7","name":"Denis Kutlubaev","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/346de75d27c818be0aa7fddeada2e50e30cc15b4da6b3b8110c5d766cd2afa34?s=96&d=mm&r=pg","caption":"Denis Kutlubaev"},"description":"iOS Developer, creator of Tornado Browser and many other popular apps","sameAs":["http:\/\/dennis.alwawee.com"],"url":"https:\/\/blog.alwawee.ru\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=\/wp\/v2\/posts\/2339","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2339"}],"version-history":[{"count":0,"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=\/wp\/v2\/posts\/2339\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.alwawee.ru\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}