Re: Re: [odb-users] slow persist for bulk insert

[Erez GB]

<div dir='rtl'><p style="text-align: left;" dir="ltr"><br><br>
	
	
		Hi Boris,</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr">Following what you suggested, we might need to consider SQLite&nbsp;Prepared Statement as a mean for bulk insert</p><p style="text-align: left;" dir="ltr">in addition to ODB, we would like to continue ODB as possible.</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr"><ul><li>Does ODB supports SQLite&nbsp;Prepared Statement ?<br><br></li><li>Assuming I will use SQLite functions to perform them, can ODB database expose it's internal SQLite Database Connection Handle so I can use the same connection with SQLite functions ?<br><br></li><li>Can ODB objects expose the Insert statment for external use ?<br><br></li><li>Anything else that might help me use ODB with&nbsp;SQLite&nbsp;Prepared Statement ?</li></ul><div><br></div><div>Thank you very much,</div><div>Erez.</div></p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr"><br></p>
	

	
		
	

	


	 
		
		
		<hr>
		<div class="walSignature"><font size="2">שולח: <b>Boris Kolpackov<boris at codesynthesis.com>, </boris at codesynthesis.com></b><br>נושא: <b>Re: [odb-users] slow persist for bulk insert</b></font></div><p>
		

	
	

	


	
		
		
			Hi Erez, <br>
 <br>
Erez GB <erezgb at walla.com> writes: <br>
 <br>
&gt; We are working with ODB and implement a complex schema and it works  <br>
&gt; great :-) <br>
 <br>
Thanks, glad you are finding ODB useful! <br>
 <br>
 <br>
&gt; However, we need better performance when we are insering 10000 of <br>
&gt; objects into our SQLite DB. Can you please help with guidelines for <br>
&gt; improving ODB performance with SQLite? Is there another way for <br>
&gt; fast bulk insert of many ODB objects. <br>
 <br>
SQLite doesn't support bulk INSERT (unlike, say, Oracle or SQL Server) <br>
so you have to execute a separate INSERT statement for each object <br>
(which is what ODB does). So the only way to optimize this is to try <br>
various way you do it. <br>
 <br>
Number one is probably selecting the right transaction granularity. <br>
This earlier post has more information on this: <br>
 <br>
http://www.codesynthesis.com/pipermail/odb-users/2013-May/001270.html <br>
 <br>
Number two is to temporarily disable foreign key constraint checking <br>
(if you use foreign keys). You lose some safety (you need to make sure <br>
all the references are valid) but you usually gain quite a bit in <br>
performance. Here is what the code would look like: <br>
 <br>
connection_ptr c (db-&gt;connection ()); <br>
 <br>
c-&gt;execute ("PRAGMA foreign_keys=OFF"); <br>
 <br>
transaction t(c-&gt;begin()); <br>
 <br>
for(unsigned int i = 0; i &lt; 10000; i++) <br>
{ <br>
  if (i % 100 == 0) <br>
  { <br>
    t.commit (); <br>
    t.reset (c-&gt;begin ()); <br>
  } <br>
 <br>
  ... <br>
} <br>
 <br>
t.commit(); <br>
 <br>
c-&gt;execute ("PRAGMA foreign_keys=ON"); <br>
 <br>
If that's not sufficient, then I found this fairly exhaustive list of <br>
things you can try (most of them via the PRAGMA as above) to improve <br>
performance of INSERT in SQLite: <br>
 <br>
http://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite <br>
 <br>
Note also that in case of SQLite doing any kind of parallel insertion <br>
(e.g., by running multiple transactions from several threads in parallel) <br>
will only make things worse (SQLite has very poor concurrency support, <br>
especially for modifications). <br>
 <br>
Boris <br>
 <br></erezgb at walla.com></p></div><hr><div style="background-color:white;color:black;">Walla! Mail - <a href="http://www.walla.com" style="color:blue">Get your free unlimited mail today</a></div>