LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Is the Database Connectivity Toolkit compatible with connection pooling?

I am developing an application that makes numerous calls to a SQL server database.  I am using the Database Connectivity Toolkit to perform these calls.  This application is used both locally on the LAN in the same facility as the server and remotely over a WAN.  Using the accepted best practice of opening the connection when you need it and closing it as soon as your done seems to result in some significant delays opening the connection over the WAN.  Often, actually making the connection requires more time than performing the SQL operation.  Because the overhead of negotiating a connection to the server is time consuming an optimization technique called connection pooling is used to "cache" connections for reuse to avoid the costly process of negotiating a new connection.  I am trying to determine if the Database Connectivity Toolkit can benefit from connection pooling.  From what I have been able to determine through testing it appears that the Database Connectivity Toolkit does not benefit from connection pooling and negotiates a new connection every time it needs to connect to the database.  I am unable to verify this because I cannot view the block diagram of the low level routine responsible for making the connection (GOOP Object Repository.vi).  

 

Can anyone confirm that the Database Connectivity Toolkit is compatible with connection pooling?

 

http://msdn.microsoft.com/en-us/library/ms810829.aspx

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

0 Kudos
Message 1 of 4
(2,644 Views)

I don't know anything about connection pooling, but you can basically do this yourself by replacing the code which opens the connection with a relatively simple manager VI. It needs to cache the configuration data for connecting to the DB (connection string, user, password, etc.) and the connection reference. At its simplest form, it can have three actions (or you can have three wrappers) - Open, Get and Close. Calling Get will return the existing reference, but you can also check the reference for errors inside the VI and reopen the connection if there's an error. This is what I do in my code and it works quite well.


___________________
Try to take over the world!
0 Kudos
Message 2 of 4
(2,621 Views)

Hey dcamp,

The way I understand how connection pooling works all the pooling work is done by the ODBC driver manager.  LabVIEW is capable of connecting to ODBC Data Sources.  Instructions on that can be found here: http://www.ni.com/pdf/manuals/371525a.pdf  If you configure the ODBC Data Sources to use pooling those configurations should be independent of LabVIEW. Please let me know if I am missing something or if you have any additional questions.

Kevin Fort
Principal Software Engineer
NI
0 Kudos
Message 3 of 4
(2,600 Views)

This is exactly how I understood pooling to work.  For some reason though, it appears that everytime I open a connection, same computer, user, connection string, it appears to be negotiating a new connection with the server.  I've enabled connection pooling in the ODBC settings for SQL Server and even in the connection string itself and there seems to be no change in behavior.  There must be something misconfigured on my end.

0 Kudos
Message 4 of 4
(2,594 Views)