Problem with DB Tools Execute Query

[zappagoblin]

Good morning everyone. I have a problem that I can't solve with the Database Connectivity Toolkit - DB Tools Execute Query in specific. In my application, I execute queries in sequence (about 180) which I use to populate a selection tree. The queries are simple (SELECT DISTINCT field FROM TABLE WHERE condition) and the condition is an equality. Sometimes these 180 queries (the database has approximately 130,000 records) are executed in less than 2 seconds, other times they take up to 10 minutes. The same data, the same queries. The database was on the network, so I took care to copy it locally and I make my queries on it. I see that the VI that executes the query (DB Tools Execute Query) hangs in the part where it tries to retrieve the ADODB recordset, and sometimes it stays there for 20 seconds, other times it doesn't stop, other times for minutes before moving on. I really don't know where to hit my head. I use only one connection, which I keep open to perform all 180 queries, and I close it at the end. 

Other stuff:

I Use Labview 2020 32 bit. 

The same software seems to behave differently on different PCs. On some it never crashes, on others it crashes systematically.

I use a database dynamic cursor with 100 cache items. For every query I make, I fetch data in a recordset and I free the object.

I don't have errors when the software hangs, it's all freezing (and this is another problem I have to solve, because I have set a timeout of 3 second on database operations but in this case won't work).

The code is simply a FOR structure that execute, on by one, an array of queries. The database is a MDB format. I tried to execute manually every single query that seems to be "critical" but they worked good.

The amount of memory used by the VI when in execution is approximately 200MB. The CPU and disk usage during hangs are minimal.

Thank you for your suggestions. And sorry for my english.

[anna78smith]

Using a dynamic cursor with caching can cause unpredictable delays depending on how the recordset is managed, especially across different PCs and environments. To stabilize execution, try switching to a static cursor or forward-only cursor, reduce reliance on caching, and ensure indexes exist on the fields used in your WHERE clauses. Also, consider batching queries or consolidating them to minimize repeated recordset creation. Finally, test with different OLEDB providers or drivers, since some PCs may be handling ADODB recordsets differently, which explains the variability.

[zappagoblin]

Thanks anna78smith! I'll follow your advice and I'll give feedback on this.

[DavidBoyd]

Everything @anna78smith said.  I've been working with the Database Connectivity Toolkit for years, and if it's not doing what someone wants, or doing it quickly, there is always an external reason.

Unfortunately, I haven't worked with Microsoft Access databases (file-based, *.mdb) for a very long time.  Long ago I upsized such databases to MS SQL Server.

But yes, do report back what OLEDB driver you are using, for a start.  Assuming you have a .udl file (if not, just create one - you can rename a blank .txt file for this), open it and look at the Providers tab.  From there you can see what providers are installed.  You'll want to enforce the same one, suitable for Access databases, across all your PCs using your application.

And also as @anna78smith said - performance of queries is heavily dependent on having the proper indicies built on the tables.

In the longer term, you want to avoid creating your query text in LabVIEW and forcing the database to interpret it for execution.  If possible, you should create a query within the database and parameterize any values you would use in your WHERE clause. (I think Access supports this.)

As well, you should create a UDL file that specifies your database provider, and the connection/path to the database, and feed the UDL file to your LabVIEW code's "Open Connection" call.  This way, you have more flexibility to relocate your data, even possibly to change the provider type, with zero or little impact to your application.

Good luck!

Dave

[zappagoblin]

Thanks @DavidBoyd for your kind and useful reply. 

I have a .UDL file that I install on every PC that runs my application.

In this UDL file the OLE DB initstring is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\BATVIEW\TEMP.MDB;Persist Security Info=False

I don't have indexes in my tables, but until recently this never caused me any problems. Your advice is to create them directly within ACCESS, and not through DB Tools? I've never used parameterizable queries. I'll try to figure out how; you've opened up a whole new world for me... What I don't understand is the difference in behavior from one PC to another, given the same software and OLE DB engine. And the fact that these differences appear from one day to the next after months of good functioning of the application.

Thank you so much for your kind response.

[zappagoblin]

A clarification, @DavidBoyd: I use Microsoft Jet OLEDB 4.0 because my application is compiled in 32-bit and the engine is resident on every operating system. This allows me to avoid install of additional parts on the PCs on which I distribute the application.

[zappagoblin]

Hi to all. I have inserted two indexes on my table, and switched my dynamic cursor to static. On the PCs with intermittent issues the situation has significantly improved . Unfortunately, the problem hasn't been resolved on PCs that crashed.

[zappagoblin]

Correction, guys: reinstalling my application from scratch on the critical PC, solve the problem even on super-resistent-bugged PCs !! Thanks a lot! But I'll work on parameterized queries to improve my software. Thanks to Anna and David!