07-11-2024 11:16 AM
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.
Solved! Go to Solution.
07-11-2024 02:16 PM - edited 07-11-2024 02:19 PM
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.
07-11-2024 06:13 PM
Trim whitespace vi? I don't get it. I'm talking about DB query times. Please explain how they're related.
07-11-2024 11:42 PM
When you say DB query times, is it truly on the DB side?
07-15-2024 11:06 AM
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.
07-15-2024 03:47 PM
@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??
07-16-2024 09:21 AM
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.
07-16-2024 12:19 PM
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.
07-17-2024 10:57 AM
A few questions.
The other dependency to look out for is "USERDefRef*.dll" this is published by the World Wide Web Consortium, W3C, and is not threadsafe.
07-17-2024 02:46 PM
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?