{"id":174,"date":"2007-03-31T00:06:38","date_gmt":"2007-03-30T23:06:38","guid":{"rendered":"http:\/\/www.itwriting.com\/blog\/?p=174"},"modified":"2007-03-31T00:06:38","modified_gmt":"2007-03-30T23:06:38","slug":"sqlite-what-a-difference-transactions-make","status":"publish","type":"post","link":"https:\/\/www.itwriting.com\/blog\/174-sqlite-what-a-difference-transactions-make.html","title":{"rendered":"SQLite: what a difference transactions make"},"content":{"rendered":"

I received an email from something trying my simple Delphi wrapper<\/a> for Sqlite<\/a>. He wanted to add a million rows to a table of 10 columns of doubles, but was disappointed with the speed.<\/p>\n

\n

I tried with your SQL commands from the wrapper and just for 10000 elements it took me for ages…<\/p>\n<\/blockquote>\n

I had a hunch that wrapping the inserts in a transaction might solve this one, and so it proved. The difference is staggering.<\/p>\n

10,000 rows in 2 seconds, 1 million in under a minute.<\/p>\n

Without the transaction it takes, well, forever, as the email says.<\/p>\n

Worth noting if you use Sqlite; and in fact, many database engines behave like this. The reason I guess is that if you do not explicitly place a sequence of SQL statements within a transaction, then each statement is in effect its own transaction. That means the database engine has a lot of housekeeping to do in order to ensure that the changes were really written to disk, and in opening, writing and closing the journal file. <\/p>\n

 <\/p>\n

Technorati tags: sqlite<\/a>, database<\/a>, delphi<\/a>, performance<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"

I received an email from something trying my simple Delphi wrapper for Sqlite. He wanted to add a million rows to a table of 10 columns of doubles, but was disappointed with the speed. I tried with your SQL commands from the wrapper and just for 10000 elements it took me for ages… I had … Continue reading SQLite: what a difference transactions make<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,80],"tags":[],"class_list":["post-174","post","type-post","status-publish","format-standard","hentry","category-delphi","category-software-development"],"_links":{"self":[{"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/posts\/174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/comments?post=174"}],"version-history":[{"count":0,"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/posts\/174\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/media?parent=174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/categories?post=174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.itwriting.com\/blog\/wp-json\/wp\/v2\/tags?post=174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}