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">First, thank you very much for your quick response and kindness, I have included bellow the code we use to Insert\Save ODB objects,</p><p style="text-align: left;" dir="ltr">we have a template that handles some of the repeated code for use that is also used in UnitTesting (where ODB cannot be used)</p><p style="text-align: left;" dir="ltr">so we use aliases to your objects like database, transaction etc'</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr">Do you see any problem or things that might cause such a huge difference between prepared statements to it ?</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr">Maybe I am missing something about ODB usage ?</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr">-------------------------------------------------------------------------------------------</p><p style="text-align: left;" dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">	</span>bool SaveOwnData(ODBDatabase data_base)</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">	</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>if(data_base == NULL)</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>return false;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>}</p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>long new_id = 0;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>bool bOperationSuccessfulFlag = true;<span class="Apple-tab-span" style="white-space:pre">	</span></p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>ODBTransaction save_transaction(data_base-&gt;begin ());</p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>// this template cannot use shared_from_this, we pass the actual instance of the "this" pointer casted to it's real class</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>ORMClass&amp; this_casted_reference = *((ORMClass*)this);</p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>bool bIsNewObject = getIsNewObjectFlag();</p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>// actually save this object</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>try</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>if(!getIsNewObjectFlag())</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>//</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>// updating an existing object</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>data_base-&gt;update(this_casted_reference);</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>}</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>else</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>//</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>// inserting a new object</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>new_id = data_base-&gt;persist(this_casted_reference);</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>//</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>// after saving it, this object is not new anymore</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">				</span>setIsNewObjectFlag(false);</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>}</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>}</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>catch (const odb::exception&amp; e)</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>//</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>// log the error in e-&gt;what()</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>const char* sCause = e.what();</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>string sError("ORMPersistData::SaveOwnData - ODB Exception ");</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>sError += sCause;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>BOOST_ASSERT_MSG(0, sError.c_str());</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>//&nbsp;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>bOperationSuccessfulFlag = false;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>}</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>catch(...)</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>{</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>BOOST_ASSERT_MSG(0, "ORMPersistData::SaveOwnData - Unspecified Exception");</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">			</span>bOperationSuccessfulFlag = false;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>}</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>//</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>save_transaction.commit();</p><p dir="ltr"><br></p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">		</span>return bOperationSuccessfulFlag;</p><p dir="ltr"><span class="Apple-tab-span" style="white-space:pre">	</span>}</p><p style="text-align: left;" dir="ltr"><br></p><p style="text-align: left;" dir="ltr">-------------------------------------------------------------------------------------------</p><p style="text-align: left;" dir="ltr"><br></p>
	

	
		
	

	


	 
		
		
		<hr style="text-align: left;">
		<div class="walSignature" style="text-align: left;"><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 style="text-align: left;">
		

	
	

	


	
		
		
			Hi Erez, <br>
 <br>
Erez GB <erezgb at walla.com> writes: <br>
 <br>
&gt; Following what you suggested, we might need to consider SQLite Prepared <br>
&gt; Statement as a mean for bulk insert <br>
 <br>
ODB already uses prepared statements. It also caches/reuses prepared <br>
statements and even memory buffers. I think you will be hard-pressed <br>
to achieve better performance with hand-coded INSERTs unless you <br>
exploit some application-specific knowledge (which is, generally, <br>
quite hard to do in the case of INSERT). <br>
 <br>
 <br>
&gt;   * Assuming I will use SQLite functions to perform them, can ODB database <br>
&gt;     expose it's internal SQLite Database Connection Handle so I can use the <br>
&gt;     same connection with SQLite functions ? <br>
 <br>
Yes, see Section 18.3, "SQLite Connection and Connection Factory". <br>
 <br>
 <br>
&gt;   * Can ODB objects expose the Insert statment for external use? <br>
 <br>
You can use them (see libodb-sqlite/odb/sqlite/statement.hxx) though <br>
they are quite low level and optimized for the way ODB does things <br>
(e.g., caching/reusing as much as possible). <br>
 <br>
 <br>
&gt;   * Anything else that might help me use ODB with SQLite Prepared  <br>
&gt;     Statement ? <br>
 <br>
As I said, you are already using prepared statements. What you can <br>
do is show us the class you are trying to persist, your transaction, <br>
the numbers that you get, and the numbers that you would like to get. <br>
Even better way would be to create a test program that mimics your <br>
application scenario that we can try. <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>