[unixODBC-dev] A discussion about unixODBC programming model

xiaonan xn212516 at 163.com
Mon Nov 24 08:24:58 GMT 2014



Hi Nick,


	I have do the performance test to check which method is the best one: The process has 20 threads, and every thread insert 5000 records into MySQL database. 


	There are 3 kinds of connection model:


	(1) normal case:
	The execution flow of every thread likes this:
	
	for ()
	{
		Connect database;
		Execute SQL statement;
		Disconnect database;
	}
	
	After testing 5 times, the average time is 16 seconds.
	
	(2) Shared connection case:
	In this model, all the 20 threads share a common connection. And the execution flow of every thread likes this:
	
	for ()
	{
		Execute SQL statement;
	}
	
	After testing 5 times, the average time is 19 seconds.
	
	(3) Excluded connection case:
	In this model, all the 20 threads have a exclusive connection. And the execution flow of every thread likes this:
	
	Connect database;
	for ()
	{
		Execute SQL statement;
	}
	Disconnect database;
	
	After testing 5 times, the average time is 5 seconds.
	
	So after testing, the third method is more efficient than the other two methods.
	
	The attachment are the source code. If you have interest, you can read them, thanks!:-)


Best Regards
Nan Xiao

At 2014-11-20 16:11:38, "Nick Gorham" <nick at lurcher.org> wrote:
>On 20/11/14 02:40, xiaonan wrote:
>> Hi Nick,
>>
>> 	Now our project uses unixODBC like this:
>> 	
>> 	(1) Use connection pool:
>> 	/usr/local/etc/odbcinst.ini
>> 	[ODBC]
>> 	Trace=No
>> 	Pooling=Yes
>> 	
>> 	(2) When program initiates, allocate a global environment handle:
>> 	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &g_env_handle));
>> 	
>> 	(3) Encapsulate a SQL operation function:
>> 	sql_func()
>> 	{
>> 		/* Allocate connection handle */
>> 		SQLAllocHandle(SQL_HANDLE_DBC, g_env_handle, &conn_handle);
>> 		/* Connect database */
>> 		SQLConnect(conn_handle, ...);
>> 		
>> 		/* Allocate statement handle */
>> 		SQLAllocHandle(SQL_HANDLE_STMT, conn_handle, &stmt_handle);
>> 		/* Execute statement */
>> 		SQLExecDirect(stmt_handle, ...);
>> 		/* Free statement handle */
>> 		SQLFreeHandle(SQL_HANDLE_STMT, stmt_handle);
>> 		
>> 		/* Disconnect database */
>> 		SQLDisconnect(conn_handle);
>> 		/* Free connection handle */
>> 		SQLFreeHandle(SQL_HANDLE_DBC, conn_handle);
>> 	}
>> 	
>> 	So all the SQL related operations will be executed through this sql_func.
>>
>>
>> 	This encapsulate is easy and clear, but now I consider whether this will lead low efficiency. Because every SQL operation will allocate connection handle, connect database (although the connection pool is used), allocate statement handle, free handles, etc.
>> 	
>> 	So I consider whether I can do like this:
>> 	(1) This is the same as now: still use connection pool.
>> 	
>> 	(2) When program initiates, except the global environment handle, I still allocate a connection handle pool:
>> 	create_connection_handle_pool()
>> 	{
>> 		for (...)
>> 		{
>> 			/* Allocate connection handle */
>> 			SQLAllocHandle(SQL_HANDLE_DBC, g_env_handle, &conn_handle);
>> 			/* Connect database */
>> 			SQLConnect(conn_handle, ...);
>> 		}
>> 	}
>> 	
>> 	(3) So the SQL operation function can be:
>> 	sql_func()
>> 	{
>> 		select a connection handle from connection handle pool;
>> 		
>> 		/* Allocate statement handle */
>> 		SQLAllocHandle(SQL_HANDLE_STMT, conn_handle, &stmt_handle);
>> 		/* Execute statement */
>> 		SQLExecDirect(stmt_handle, ...);
>> 		/* Free statement handle */
>> 		SQLFreeHandle(SQL_HANDLE_STMT, stmt_handle);
>> 		
>> 	}
>> 	
>> 	This will not allocate connection handle and connect database every time. So I think this will improve efficiency.
>> 	
>> 	Nick, could you help to comment on this idea? Such as if it is worth to create a connection handle pool? Is there any risk of implementing this?
>>
>>          Thanks very much in advance!
>> 	
>> Best Regards
>> Nan Xiao
>
>Its how I would (and do) work. Only thing to be aware of is I have seen 
>situations where if the connection sits idle for some time, and the 
>connection to the database is via a firewall, then I have seen the 
>firewall drop the connection. But on a LAN its all fine.
>
>I would also check the SQLGetInfo( SQL_ACTIVE_STATEMENTS ) value for 
>your database. You may only need one connection and then run multiple 
>statements from that one connection.
>
>-- 
>Nick
>> _______________________________________________
>> unixODBC-dev mailing list
>> unixODBC-dev at mailman.unixodbc.org
>> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
>
>_______________________________________________
>unixODBC-dev mailing list
>unixODBC-dev at mailman.unixodbc.org
>http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: exclude.c
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-dev/attachments/20141124/b6d91c9d/attachment.c>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: normal.c
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-dev/attachments/20141124/b6d91c9d/attachment-0001.c>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: shared.c
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-dev/attachments/20141124/b6d91c9d/attachment-0002.c>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: odbc.ini
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-dev/attachments/20141124/b6d91c9d/attachment.pl>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: odbcinst.ini
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-dev/attachments/20141124/b6d91c9d/attachment-0001.pl>


More information about the unixODBC-dev mailing list