ADODB possible LabVIEW ActiveX Bug

[GregFreeman]

I think I have found a fundamental issue with the DB Toolkit Open connection. It seems to not correctly use connection pooling. The reason I believe it's an issue with LabVIEW and ADODB ActiveX specifically is because the problem does not manifest itself using the ADODB driver in C#. This is better shown with examples. All I am doing in these examples is opening and closing connections and benchmarking the connection open time.

Adodb and Oracle driver in LabVIEW.

 

ADODB in C#

 

namespace TestAdodbOpenTime
{
 class Program
 {
 static void Main(string[] args)
 {
 Stopwatch sw = new Stopwatch();

 for (int i = 0; i < 30; i++)
 {
 ADODB.Connection cn = new ADODB.Connection();
 int count = Environment.TickCount;
 cn.Open("Provider=OraOLEDB.Oracle;Data Source=FASTBAW;Extended Properties=PLSQLRSet=1;Pooling=true;", "USERID", "PASSWORD", -1);
 sw.Stop();
 cn.Close();
 int elapsedTime = Environment.TickCount - count;
 Debug.WriteLine("RunTime " + elapsedTime);
 }
 }
 }
}

Output:

RunTime 203
RunTime 0
RunTime 0
RunTime 0
RunTime 0
RunTime 0
RunTime 0
RunTime 0
RunTime 0

 

Notice the time nicely aligns between the LabVIEW code leveraging the .NET driver and the C# code using ADODB. The first connection takes a bit to open then the rest the connection pooling takes over nicely and the connect time is 0. 

 

Now cue the LabVIEW ActiveX implementation and every open connection time is pretty crummy and very sporadic. 

 

One thing I happened to find out by accident when troubleshooting was if I add a property node on the block diagram where I open a connection, and if I don't close the reference, my subsequent connect times are WAY faster (between 1 and 3 ms). That is what leads me to believe this may be a bug in whatever LabVIEW does to interface with ActiveX.

 

Has anyone seen issues like this before or have any idea of where I can look to help me avoid wrapping up the driver myself?

 

Edited April 18, 2018 by GregFreeman

[smithd]

In the .net code you have it looks like you aren't actually destroying the objects but I think the labview equivalent is -- equivalent to if you called this in the .net version. My guess is that when you get the connection properties in the screenshot you're leaking a reference which prevents overall destruction of the main connection object thus leading to faster subsequent runs. What happens if you change your first code so that you wait to close the connections until after you open all of them? Does that go fast?

[GregFreeman]

EDIT: You might be spot on smithd. I added Marshal.ReleaseComObject(cn) in my for loop and the times match almost perfectly to the LabVIEW ActiveX implementation. Just confused if that is being called under the hood of the open somehow, how the close connection would work. That reference would then be dead. That's one thing that makes me thing this may be a Red Herring.

That's definitely a good thought that didn't cross my mind. I changed the LabVIEW code to leave the connections open but still no luck.

Edited April 19, 2018 by GregFreeman

[GregFreeman]

For completeness, this is the c# code where I'm now seeing matching (slow) timing numbers.

namespace TestAdodbOpenTime
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();

            for (int i = 0; i < 30; i++)
            {
                ADODB.Connection cn = new ADODB.Connection();
                int count = Environment.TickCount;
                cn.Open("Provider=OraOLEDB.Oracle;Data Source=DATASOURCE;Extended Properties=PLSQLRSet=1;Pooling=true;", "UID", "PWD", -1);
                sw.Stop();
                cn.Close();
                Marshal.ReleaseComObject(cn);
                int elapsedTime = Environment.TickCount - count;
                Debug.WriteLine("RunTime " + elapsedTime);

            }

        }
    }
}

Output:

RunTime 218
RunTime 62
RunTime 47
RunTime 31
RunTime 63

...

Edited April 19, 2018 by GregFreeman

[smithd]

Well...thats confusing. It looks like automation open claims to be able to reuse references:

" If open new instance is TRUE, LabVIEW creates a new instance of the Automation Refnum. If FALSE (default), LabVIEW tries to connect to an instance of the refnum that is already open. If the attempt is unsuccessful, LabVIEW opens a new instance. "

So in the configuration above it shouldn't need to create a new instance.

I don't know if it will help, but to get more info maybe try timing the 3 sections of the open function (automation open, then connect, then the subVI at the end which..not clear what it does). If you really wanted to dig into it, I'm betting something like process monitor or process explorer could tell you about what handles and such are actually open, and that might tell you the difference...but I'm not totally sure which tool would accomplish that.

[Rolf Kalbermatter]

One difference between LabVIEW and .Net is the threading for ActiveX components. While ActiveX components can be multithreading safe, they seldom really are and specify the so called Apartement threading to be required. This means that the component is safe to be called from any user thread but that all methods of an object need to be called from the same thread that was used when creating the object.

In .Net as long as you do not use multi-threading by means of creating Thread() objects or some derivated objects of Thread(), your code runs single threaded (in the main() thread of your application). LabVIEW threading is more complicated and automatic multi-threading. This means that you do not have much control over how LabVIEW distributes code over the multiple threads. And the only thread execution system where LabVIEW does guarantee that all the code is called in the same thread is the UI Execution system. This also means that apartment threaded ActiveX objects are always executed in the UI Execution system and have to compete with other UI actions in LabVIEW and anything else that may need to be called in a single threaded context. This might also play a role here.

Aside from that, I'm not really sure how LabVIEW should know to not create a new connection object each time, but instead reuse an already created one. .Net seems to somehow do it but the API you are using is in fact a .Net wrapper around the actual COM ADO API.

Edited April 22, 2018 by rolfk