Jump to content

Recordset Open method freezes


Recommended Posts

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

 

 

post-14511-0-19937600-1406757261_thumb.p

Link to comment

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.  

Link to comment

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...

post-14511-0-68250300-1406912070.png

Link to comment

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.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.