[freetds] [FreeTDS] connection pool
Thompson, William
bill.d.thompson at baml.com
Thu Sep 1 05:35:44 EDT 2016
Hi Eric,
Your original question was how to insert data into a database table as quickly as possible.
You said that you'd tried individual inserts using dbcmd() and dbsqlexec(), which are part of the db-library API.
You should use the "bcp" api that is part of db-library. This is the quickest way to insert data into a database table.
The api is described here, in the Sybase documentation. FreeTDS implements these functions too.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ocs_12.5.1.dblib/html/dblib/X40041.htm
It will require a little more programming than using dbsqlexec(), but if speed is your requirement, this is the way forward.
Regards,
Bill
-----Original Message-----
From: FreeTDS [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Xiaobiao Yin
Sent: 01 September 2016 01:53
To: FreeTDS Development Group
Subject: Re: [freetds] [FreeTDS] connection pool
Hi Frediano
Thank you really.
In my case, I have to insert the rows to table one by one.
For the connection pooling, I got the information from the User Guide.
http://www.freetds.org/userguide/tdspool.htm,
The FreeTDS connection pool currently only supports TDS version 4.2.
If FreeTDS 1.0 supports protocol 7.2 with some limitations, where can I find the
limitation's description, so that I can identify if it will affect my case.
BTW, is there any sample code for connection pooling?
Thanks in advance
Eric
________________________________
From: FreeTDS <freetds-bounces at lists.ibiblio.org> on behalf of Frediano Ziglio <freddy77 at gmail.com>
Sent: Wednesday, August 31, 2016 9:18 PM
To: FreeTDS Development Group
Subject: Re: [freetds] [FreeTDS] connection pool
2016-08-31 11:35 GMT+01:00 Xiaobiao Yin <xiaobiaoyin at outlook.com>:
> Hi Experts
>
>
> I use FreeTDS (linux) to communicate with a SQL server 2005 in
> Windows,
>
> and tried to use [dbcmd() and dbsqlexec()] 100 times to insert 1 item to database tables.
>
> The performance is that one insert statement consumes about 2~3ms.
>
>
> I know there is a so called Connection pool, but it only support TDS 4.2 while SQL server need TDS 7.2.
>
>
> So in order to optimize the performance, I may need to open 2 or
> more connections, it is a little hard to
>
> control these connections.
>
>
> My question is that are there some ways to optimize the performace.
>
> The target should be 1ms per 1 insert statement.
>
>
> Any suggetions are welcome.
>
>
> Thanks
>
> Eric
>
It's not clear if the problem is application or connections.
If you have one application that needs to insert rows into a table it's better to batch the inserts instead of using a query for each row.
If you have multiple process each needing to connect to database and insert a row then would be better to use a connection pooling. The connection pooling included in FreeTDS 1.0 supports protocol 7.2 with some limitations.
Frediano
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
FreeTDS Info Page - ibiblio<http://lists.ibiblio.org/mailman/listinfo/freetds>
lists.ibiblio.org
The FreeTDS mailing list hosts discussions of all things FreeTDS. That includes help of all kinds, as well as development issues. This is list recommended reading for ...
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
More information about the FreeTDS
mailing list