Query time for Access DB varies

[Dobbs]

---

I'm using LabVIEW 2019 with the DB toolkit to query an Access DB.  It's a small DB with just a few hundred records.  DB was created with Access 2010 running on Windows 10.  DB connection remains open for the duration of the program.  I time how long each query takes to execute using the LV high resolution timer functions.  I do a connection using a system DSN created with Microsoft's 32 bit tool.

Here's the issue: queries normally take 5 - 50 ms to execute.  That's the case on my two dell laptops.  But on a Dell 7050 micro running windows 10, the query times vary wildly, from 5 ms to 16 seconds (yes, variation can be a factor of 3000.)  All computers are using ACEODBC.DLL driver supporting .accdb file format.  The variation is random and unpredictable.  But variation never happen on two laptops, they're always fast.  What might be the root cause?  I want to update the DB driver, but can't find a link on the MS site where office 2010 will still be supported. Thoughts?  Thanks.

[JÞB]

Apparently,  this is serendipity (I don't believe in coincidence)

---

@Dobbs wrote:

---

I'm using LabVIEW 2019 with the DB toolkit to query an Access DB.  It's a small DB with just a few hundred records.  DB was created with Access 2010 running on Windows 10.  DB connection remains open for the duration of the program.  I time how long each query takes to execute using the LV high resolution timer functions.  I do a connection using a system DSN created with Microsoft's 32 bit tool.

Here's the issue: queries normally take 5 - 50 ms to execute.  That's the case on my two dell laptops.  But on a Dell 7050 micro running windows 10, the query times vary wildly, from 5 ms to 16 seconds (yes, variation can be a factor of 3000.)  All computers are using ACEODBC.DLL driver supporting .accdb file format.  The variation is random and unpredictable.  But variation never happen on two laptops, they're always fast.  What might be the root cause?  I want to update the DB driver, but can't find a link on the MS site where office 2010 will still be supported. Thoughts?  Thanks.

---

I would bet $ against my 8-Ball that remarkably the "Trim Whitespace.vi" preforms "less than optimally."

Trim Whitespace.vi is "ungainly" for performance.   This is your likely problem. 

https://forums.ni.com/t5/LabVIEW/Trim-Whitespace-vi-is-not-re-entrant-Why/m-p/3647247#M1024210 

I've seen the same thing many times. 

[Dobbs]

Trim whitespace vi?  I don't get it.  I'm talking about DB query times.  Please explain how they're related.

[santo_13 · Accepted solution]

When you say DB query times, is it truly on the DB side?

[Dobbs]

Santhosh:

Thanks for your comment.  To answer, I am timing the query as follows:

* Start timer

* Execute query VI

* Fetch recordset data VI

* Free object VI

* Stop timer

See the picture.  I did not investigate to see which of the three VIs is varying so much, but I suspect it is the Fetch VI.

I can run the identical query multiple times, and get wildly different query times, from 5 ms up to 16 s (16,000 ms).  Clearly, there is some sort of driver problem that causes the Windows driver to do hundreds or thousands of times as many calculations as is necessary.  Or that's what it looks like.  The code seems solid.  The compiled EXE never misbehaves on my two Dell laptops, just on a Dell 7050 micro.  So I don't suspect that it's a LabVIEW code issue.

[Eric1977]

---

@Dobbs wrote:

---

I'm using LabVIEW 2019 with the DB toolkit to query an Access DB.  It's a small DB with just a few hundred records.  DB was created with Access 2010 running on Windows 10.  DB connection remains open for the duration of the program.  I time how long each query takes to execute using the LV high resolution timer functions.  I do a connection using a system DSN created with Microsoft's 32 bit tool.

Here's the issue: queries normally take 5 - 50 ms to execute.  That's the case on my two dell laptops.  But on a Dell 7050 micro running windows 10, the query times vary wildly, from 5 ms to 16 seconds (yes, variation can be a factor of 3000.)  All computers are using ACEODBC.DLL driver supporting .accdb file format.  The variation is random and unpredictable.  But variation never happen on two laptops, they're always fast.  What might be the root cause?  I want to update the DB driver, but can't find a link on the MS site where office 2010 will still be supported. Thoughts?  Thanks.

---

Highly doubt it's a LabVIEW issue.

Based on what you stated, it appears that it's that one Dell laptop. You state that one of the laptops "varies wildly." What happens when you run the query via Access on that specific laptop??

[Dobbs]

Eric:

Thanks for your message.

To be clear, there are three computers at play.  Two are Dell laptops, and they both deliver speedy query results when running the LabVIEW exe file.  There is a third computer, not a laptop, but a Dell OptiPlex 7050 micro.  This is the computer with wildly varying query times.  It has Windows 10 and Office 2010 (with MS Access) loaded on it.  All the Office apps, including Access, seem to work fine.

I took your suggestion to run queries from within Access.  When I do this, I consistently get speedy results.  So Access and the ODBC driver are working fine together.  When I perform a query from within the LabVIEW exe, it has to use a system DSN created with the MS ODBC Administrator tool to connect to the DB.  I could try re-creating the DSN.  But the DSN must be working now, as the queries always execute successfully, albeit sometimes very slowly.  As I wrote, I'm not sure of the root cause.  I don't know enough about how Windows operates under the hood.  Fortunately, there are smarter people than me out there, and one might be able to help.

[Eric1977]

Can you share your entire project with us including all of the VIs? Without seeing anything, I suspect it's something on that PC rather than the other two.

[JÞB]

A few questions. 

• Check under the hood of the actual DB TK Query for Trim Whitespace.vi (just asking)
• Why would you even attempt Benchmarking DB Query times when Obtaining And Releasing references inside the Benchmark?  Frankly, the Obtain - Release reference operations are going to swamp the Query process!  So, I now doubt that the Query itself is the time constraining operation.
• Triple check the TK VI Properties to ensure debugging is disabled.
• Also, try to force a thread release by placing a 0mSec Stall Dataflow.vim after the Close DB Reference.vi !  This "Might, but is not guaranteed to," let Windows Garbage collection actually Close the open references so that another Reference can be opened.  (is the TK threadsafe?  If Trim Whitespace.vi is used it cannot be)

The other dependency to look out for is "USERDefRef*.dll"  this is published by the World Wide Web Consortium, W3C, and is not threadsafe.

[Dobbs]

Eric:

I have no problem sharing the project.  Given that I'm not an expert LabVIEW user, I don't know the best way to do this.  I there a tool in LabVIEW that zips up the entire project so you can attach that file to the drag and drop?