A discussion about unixODBC programming model

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

A discussion about unixODBC programming model

xiaonan-2
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
_______________________________________________
unixODBC-dev mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
Reply | Threaded
Open this post in threaded view
|

Re: A discussion about unixODBC programming model

Nick Gorham-2
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
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev

_______________________________________________
unixODBC-dev mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
Reply | Threaded
Open this post in threaded view
|

Re: A discussion about unixODBC programming model

xiaonan-2


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" <[hidden email]> 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
>> [hidden email]
>> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
>
>_______________________________________________
>unixODBC-dev mailing list
>[hidden email]
>http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev

_______________________________________________
unixODBC-dev mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev

exclude.c (5K) Download Attachment
normal.c (5K) Download Attachment
shared.c (4K) Download Attachment
odbc.ini (250 bytes) Download Attachment
odbcinst.ini (312 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: A discussion about unixODBC programming model

Nick Gorham-2
On 24/11/14 08:24, xiaonan wrote:

>
> 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!:-)

Yep, there are no hard and fast rules, it all depends on the driver and
network protocol if any.

--
Nick
_______________________________________________
unixODBC-dev mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev
Reply | Threaded
Open this post in threaded view
|

Re: A discussion about unixODBC programming model

xiaonan-2

Hi Nick,

Got it, thanks!

Best Regards
Nan Xiao
At 2014-11-24 17:24:03, "Nick Gorham" <[hidden email]> wrote:

>On 24/11/14 08:24, xiaonan wrote:
>>
>> 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!:-)
>
>Yep, there are no hard and fast rules, it all depends on the driver and
>network protocol if any.
>
>--
>Nick
>_______________________________________________
>unixODBC-dev mailing list
>[hidden email]
>http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev

_______________________________________________
unixODBC-dev mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-dev