Recordset Open method freezes

[eberaud]

Dear LabVIEW enthusiasts,

 

Our application uses .NET methods to access a SQL Server Express LocalDB database. It works, since we do manage to get the records we want. But on a regular basis, the Open method of the Recordset freezes for several seconds instead of executing within a few milliseconds like it usually does.

 

I was able to determine that it was the Open method (and not the GetRows or the Close methods) by measuring the elapsed time as shown on the attached screenshot.

 

Do you know if there is a known issue? I do have the Database Connectivity Toolkit but I have never used it, is there any chance this would fix my issue?

 

Thank you

 

 

[bbean]

The DB toolkit is really just a wrapper for the activex methods and properties you are using.  One thing you could try is opening the connection to the database separately first (see DB Tools Open Connection.vi), executing a query with a forward only cursor on the connection (see DB Tools Execute Query.vi), and then "fetch" the recordset (DB Tools Fetch Recordset) like they do in the DB toolkit.  This way you can separate connection problems to the database from querying problems of a large recordset.  

[eberaud]

Thanks BBean.

 

After discussion with one of our contractor, it seems this issue is likely to be completely unrelated to LabVIEW, so I guess this is not the right place for this thread.

 

I still wanted to attach this file though. Just look at the 1st and 2nd columns. My test VI inserts a new row in the table each time the RecordSet.Open method takes more than 20ms. The 1st column shows the time at which the "freeze" happens and the 2nd column shows how long the Open method took, in milliseconds.

 

There is definitely a pattern that emerge, either every 60 or every 70 milliseconds. This is very weird, I really wonder what might be happening. And it is not related to how many time I query the database, since the results are similar whatever the rate is (second last column).

 

The database has only 500 records and I query either 6 or 40 of them each time (middle column).

 

I wonder what Windows is up to...

[bbean]

Is your database located remotely on a network?  If so I would implement my first recommendation to open the connection to the database separately and leave the connection open all the time.  That way you don't have to re-establish the connection each time to access the DB.  Now you will have to add error checking and loops to re-establish the connection if it goes down.  

 

My guess is you are seeing some network latency or some type of issue related to the nagle algorithm bunching your packets together.

[eberaud]

The database is on the local machine, not on the network. Your point about the connection makes sense though, it seems this Open RecordSet method re-opens the connection each time...