{"action":"created","issue":{"url":"https://api.github.com/repos/mro/librdf.sqlite/issues/10","labels_url":"https://api.github.com/repos/mro/librdf.sqlite/issues/10/labels{/name}","comments_url":"https://api.github.com/repos/mro/librdf.sqlite/issues/10/comments","events_url":"https://api.github.com/repos/mro/librdf.sqlite/issues/10/events","html_url":"https://github.com/mro/librdf.sqlite/issues/10","id":113096854,"number":10,"title":"Bulk insert of triples","user":{"login":"abargnesi","id":149445,"avatar_url":"https://avatars.githubusercontent.com/u/149445?v=3","gravatar_id":"","url":"https://api.github.com/users/abargnesi","html_url":"https://github.com/abargnesi","followers_url":"https://api.github.com/users/abargnesi/followers","following_url":"https://api.github.com/users/abargnesi/following{/other_user}","gists_url":"https://api.github.com/users/abargnesi/gists{/gist_id}","starred_url":"https://api.github.com/users/abargnesi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/abargnesi/subscriptions","organizations_url":"https://api.github.com/users/abargnesi/orgs","repos_url":"https://api.github.com/users/abargnesi/repos","events_url":"https://api.github.com/users/abargnesi/events{/privacy}","received_events_url":"https://api.github.com/users/abargnesi/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-10-23T20:48:14Z","updated_at":"2015-10-24T02:43:06Z","closed_at":"2015-10-23T21:13:34Z","body":"First off, great work. It seems like a promising improvement over the stock SQLite storage.\r\n\r\nI am creating a SQLite database of around 20 million triples and insert seems to slow as I am inserting.\r\n\r\nI have set the `synchronous` option of `off` to disable journaling, but it doesn't seem to help much. With the stock SQLite storage it seems to insert at a consistent rate.\r\n\r\nAre there additional options to speed up bulk-insert?"},"comment":{"url":"https://api.github.com/repos/mro/librdf.sqlite/issues/comments/150743312","html_url":"https://github.com/mro/librdf.sqlite/issues/10#issuecomment-150743312","issue_url":"https://api.github.com/repos/mro/librdf.sqlite/issues/10","id":150743312,"user":{"login":"abargnesi","id":149445,"avatar_url":"https://avatars.githubusercontent.com/u/149445?v=3","gravatar_id":"","url":"https://api.github.com/users/abargnesi","html_url":"https://github.com/abargnesi","followers_url":"https://api.github.com/users/abargnesi/followers","following_url":"https://api.github.com/users/abargnesi/following{/other_user}","gists_url":"https://api.github.com/users/abargnesi/gists{/gist_id}","starred_url":"https://api.github.com/users/abargnesi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/abargnesi/subscriptions","organizations_url":"https://api.github.com/users/abargnesi/orgs","repos_url":"https://api.github.com/users/abargnesi/repos","events_url":"https://api.github.com/users/abargnesi/events{/privacy}","received_events_url":"https://api.github.com/users/abargnesi/received_events","type":"User","site_admin":false},"created_at":"2015-10-24T02:43:06Z","updated_at":"2015-10-24T02:43:06Z","body":"My dataset contains 19799726 triples.\r\n\r\nI tested the following query on the `triples` view:\r\n\r\n```sql\r\nselect * from triples where p_uri = 'http://www.openbel.org/vocabulary/prefix';\r\n```\r\n\r\nThe following results seem to indicate a full table scan of the `triple_relations` table:\r\n\r\n```sql\r\nsqlite> .explain\r\nsqlite> .eqp on\r\nsqlite> .timer on\r\nsqlite> select * from triples where p_uri = 'http://www.openbel.org/vocabulary/prefix';\r\n--EQP-- 0,0,0,SCAN TABLE triple_relations\r\n--EQP-- 0,1,1,SEARCH TABLE so_uris AS s_uris USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,2,2,SEARCH TABLE so_blanks AS s_blanks USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,3,3,SEARCH TABLE p_uris AS p_uris USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,4,4,SEARCH TABLE so_uris AS o_uris USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,5,5,SEARCH TABLE so_blanks AS o_blanks USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,6,6,SEARCH TABLE o_literals AS o_literals USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,7,7,SEARCH TABLE t_uris AS o_lit_uris USING INTEGER PRIMARY KEY (rowid=?)\r\n--EQP-- 0,8,8,SEARCH TABLE c_uris AS c_uris USING INTEGER PRIMARY KEY (rowid=?)\r\nid    s_uri_id       s_bl  p_ur  o_ur  o_blank_id     o_  o_datatype_id  c_uri_id    s_uri                                               s_blank     p_uri                                     o_uri       o_blank     o_text      o_language  o_datatype                               c_uri     \r\n----  -------------  ----  ----  ----  -------------  --  -------------  ----------  --------------------------------------------------  ----------  ----------------------------------------  ----------  ----------  ----------  ----------  ---------------------------------------  ----------\r\n-8953203191115340872  -2153809890787916446        8891365258715654442                       -761117637572586306  -7210322708539680434              http://www.openbel.org/bel/namespace/rgd-rat-genes              http://www.openbel.org/vocabulary/prefix                          rgd                     http://www.w3.org/2001/XMLSchema#string            \r\n-5942690236445784542  -5651519178411819840        8891365258715654442                       -120847399540968945  -7210322708539680434              http://www.openbel.org/bel/namespace/mesh-anatomy               http://www.openbel.org/vocabulary/prefix                          mesha                   http://www.w3.org/2001/XMLSchema#string            \r\n-5750330924181196839  1625998193914647814        8891365258715654442                       3684776179048037764  -7210322708539680434              http://www.openbel.org/bel/namespace/swissprot                  http://www.openbel.org/vocabulary/prefix                          sp                      http://www.w3.org/2001/XMLSchema#string            \r\n-5639833229778965523  -1834755243247703679        8891365258715654442                       -3777992359957531766  -7210322708539680434              http://www.openbel.org/bel/namespace/cell-line-ontology              http://www.openbel.org/vocabulary/prefix                          clo                     http://www.w3.org/2001/XMLSchema#string            \r\n-5432515727605213476  -2024215230190190826        8891365258715654442                       7694073778182938261  -7210322708539680434              http://www.openbel.org/bel/namespace/mgi-mouse-genes              http://www.openbel.org/vocabulary/prefix                          mgi                     http://www.w3.org/2001/XMLSchema#string            \r\n-4522727871672482014  6251866281173790839        8891365258715654442                       -227163063072304314  -7210322708539680434              http://www.openbel.org/bel/namespace/experimental-factor-ontology              http://www.openbel.org/vocabulary/prefix                          efo                     http://www.w3.org/2001/XMLSchema#string            \r\n-3419368143309930828  6718338200768323000        8891365258715654442                       -1636581229217248134  -7210322708539680434              http://www.openbel.org/bel/namespace/cell-ontology              http://www.openbel.org/vocabulary/prefix                          cl                      http://www.w3.org/2001/XMLSchema#string            \r\n-1757139288187511514  5908700977917003841        8891365258715654442                       6233310147803604084  -7210322708539680434              http://www.openbel.org/bel/namespace/ncbi-taxonomy              http://www.openbel.org/vocabulary/prefix                          taxon                   http://www.w3.org/2001/XMLSchema#string            \r\n-1485986718452197738  6916964323530588694        8891365258715654442                       8012652466210525602  -7210322708539680434              http://www.openbel.org/bel/namespace/affy-probeset              http://www.openbel.org/vocabulary/prefix                          affx                    http://www.w3.org/2001/XMLSchema#string            \r\n-1076062797968999171  1765045227617811608        8891365258715654442                       -8763427777865176605  -7210322708539680434              http://www.openbel.org/bel/namespace/mesh-diseases              http://www.openbel.org/vocabulary/prefix                          meshd                   http://www.w3.org/2001/XMLSchema#string            \r\n-1027106250966710582  2665414293436938691        8891365258715654442                       -7539692296793019894  -7210322708539680434              http://www.openbel.org/bel/namespace/entrez-gene                http://www.openbel.org/vocabulary/prefix                          egid                    http://www.w3.org/2001/XMLSchema#string            \r\n-242850415018138191  6076170973665239127        8891365258715654442                       -6762884962701503883  -7210322708539680434              http://www.openbel.org/bel/namespace/chebi                      http://www.openbel.org/vocabulary/prefix                          chebi                   http://www.w3.org/2001/XMLSchema#string            \r\n2087263891606945741  -1967734694912135205        8891365258715654442                       8500928990870149547  -7210322708539680434              http://www.openbel.org/bel/namespace/go-biological-process              http://www.openbel.org/vocabulary/prefix                          gobp                    http://www.w3.org/2001/XMLSchema#string            \r\n2404370976512930390  -4736273581141158102        8891365258715654442                       2799465496762415635  -7210322708539680434              http://www.openbel.org/bel/namespace/go-cellular-component              http://www.openbel.org/vocabulary/prefix                          gocc                    http://www.w3.org/2001/XMLSchema#string            \r\n2545215817738855362  -5196219641959336837        8891365258715654442                       -1576279980644318209  -7210322708539680434              http://www.openbel.org/bel/namespace/mesh-processes              http://www.openbel.org/vocabulary/prefix                          meshpp                  http://www.w3.org/2001/XMLSchema#string            \r\n4083383934576935379  3472155070150475385        8891365258715654442                       7897920779876238115  -7210322708539680434              http://www.openbel.org/bel/namespace/selventa-named-complexes              http://www.openbel.org/vocabulary/prefix                          scomp                   http://www.w3.org/2001/XMLSchema#string            \r\n4296693752481446004  -7670413923544302630        8891365258715654442                       -2486069982777384744  -7210322708539680434              http://www.openbel.org/bel/namespace/selventa-legacy-chemicals              http://www.openbel.org/vocabulary/prefix                          schem                   http://www.w3.org/2001/XMLSchema#string            \r\n4562283096969546622  -8485103775036161392        8891365258715654442                       327606950770410263  -7210322708539680434              http://www.openbel.org/bel/namespace/selventa-legacy-diseases              http://www.openbel.org/vocabulary/prefix                          sdis                    http://www.w3.org/2001/XMLSchema#string            \r\n4895500147765907541  -4514441565986994500        8891365258715654442                       -6637659743822861254  -7210322708539680434              http://www.openbel.org/bel/namespace/mesh-cellular-structures              http://www.openbel.org/vocabulary/prefix                          meshcs                  http://www.w3.org/2001/XMLSchema#string            \r\n5097380505216658639  -2727350442066289402        8891365258715654442                       -4363043793032171563  -7210322708539680434              http://www.openbel.org/bel/namespace/disease-ontology              http://www.openbel.org/vocabulary/prefix                          do                      http://www.w3.org/2001/XMLSchema#string            \r\n6511820928570329606  -7393013952352333055        8891365258715654442                       -6955547283323735094  -7210322708539680434              http://www.openbel.org/bel/namespace/uberon                     http://www.openbel.org/vocabulary/prefix                          uberon                  http://www.w3.org/2001/XMLSchema#string            \r\n6820393681347544038  8696709812581015502        8891365258715654442                       -3555706574775279679  -7210322708539680434              http://www.openbel.org/bel/namespace/hgnc-human-genes              http://www.openbel.org/vocabulary/prefix                          hgnc                    http://www.w3.org/2001/XMLSchema#string            \r\n7302589939682261954  -8870919588248040035        8891365258715654442                       3393709359063564125  -7210322708539680434              http://www.openbel.org/bel/namespace/selventa-protein-families              http://www.openbel.org/vocabulary/prefix                          sfam                    http://www.w3.org/2001/XMLSchema#string            \r\n7821956787822654574  5400458648806173970        8891365258715654442                       8932767905229499789  -7210322708539680434              http://www.openbel.org/bel/namespace/mesh-chemicals              http://www.openbel.org/vocabulary/prefix                          meshc                   http://www.w3.org/2001/XMLSchema#string            \r\nRun Time: real 51.818 user 27.939997 sys 23.779998\r\n```\r\n\r\nAny thoughts on the full table scan of the triple_relations table?"}}