Database Close Connection Takes a Long Time

[Hooovahh]

I have a few hundred MS Access database that I need to open one at a time, read something out of and close.  What I read is pretty simple and takes about 100ms to execute on each database.  But for some reason depending on my network connection of the computer the close operation may take up to 23 seconds for each database.  This is painfully slow when I have so many databases and its even more frustrating because it is on the close when I don't really care anymore about the database.  The slownless is when performing the actual Close invoke node on the Connection session in the DB Tools Close Connection.vi.

I've tried changing the timeout setting in the open, but it still takes about 23 seconds.  Any suggestions for closing this database quicker, or figuring out why it takes so long in the first place?  

My connection string is and this is using the normal NI Database Toolkit.

Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=%s

Thanks.

Edit:  Oh and I also tried opening them all at once, operating and then closing them all at once but I get connection errors after opening 64 database at once.  I'm guessing it is a MS Access limitation.

[tst]

---

@Hooovahh wrote:

Edit:  Oh and I also tried opening them all at once, operating and then closing them all at once but I get connection errors after opening 64 database at once.  I'm guessing it is a MS Access limitation.

---

I don't know about the performance thing (although using ADO.NET might have better performance), but presumably you should be only do N of these in parallel (where N is <=64, in this case), no? Obviously the code is somewhat more complex, but I believe in you.

[Hooovahh]

---

@tst wrote:

I don't know about the performance thing (although using ADO.NET might have better performance), but presumably you should be only do N of these in parallel (where N is <=64, in this case), no? Obviously the code is somewhat more complex, but I believe in you.

---

Yeah it does get a bit more complicated, I open say 200 databases one at a time then read some basic information from each for categorizing them.  After they are categorized I need to read detailed information from each of them.  I could theoretically open them once, perform the operation based on what is in them, and close them but based on how the rest of the software was designed it would be quite a pain to do it this way.

So a few other pieces of information I forgot to mention.  The databases are somewhat small, all under 3MB, but are on a SMB network location, and all operations are reads, the files are actually read-only.  So I copied the files locally to see if the network I/O was the issue, and the delay in close was still there even when the files were on my local drive.  Then I disconnected from my wireless network, and the close now takes about 1ms each.  If I reconnect, back up to 23 seconds for most, but some are still 1ms.  And again at this point the files are stored locally and networking should have nothing to do with the database operations.

[Eric1977]

Just out of curiosity, what happens when you actually open the databases themselves in Access? Do you get the same performance hit?

[SnowMule]

Watch for errors going into the database VI's, and probe the references going into and coming out of them.

I've seen issues in the past where the connection reference would get reset to some invalid value inside of the VI's.  They're not written very well.

[Hooovahh]

You know I never noticed before but on open it takes about 20 second if I have my wireless connected, but is almost immediate if I'm not.  So it appears to be an Access connection issue, maybe trying to resolve some network location.

[Hooovahh]

So I looked into this a bit, but didn't get anywhere.  For me the solution was just to make sure I was on a wired connection to the network, and not wireless.  When doing this, or when disconnecting from the wireless and having no wired connection, the open of MS Access, and the closing of the connection was fast enough.