Teststand and SQL Express

[slaine]

I was wondering if anyone out there has experience using TestStand and SQL Express...

We have been having a problem while using TestStand in it's default 'log to file' reporting mode... Essentially the test runs we need to perform are very long, and sometimes the combination of TestStand with LabView modules coupled with an ever increasing results structure ends up with a crash. Unfortunately in this scenario all data is lost, so we are trying to migrate to a database solution using SQL Express as the server (with a thought of potentially moving towards the corporate SQL server eventually)

I installed a copy of SQL Express on to one of our develoment systems, and set it up to log all the results generated into the database. This seems to all be working fine, with no really noticeable degradation in performance compared to the file logging mode. However when trying to add another machine to log to the same database, (machine A is the SQL Express server, machine B is another test station running Teststand and connecting to the database on machine A over a LAN) machine B starts to have large pauses when the sequence flow comes to the end of several of the subsequences. If I enable more indepth debug, to allow stepping into the sequence model callbacks etc, the system is actually pausing at the activeX log result to database in the ProcessModelPostResultListEntry sequence contained in the sequence model.

If I install SQL Express to machine B and copy the database from machine A, exactly the same scenario in reverse, i.e. machine B now runs fine, and machine A is pausing during logging steps. I even installed yet another database on machine C which is a slightly faster machine being dual core with 4GB of memory, but machine A is still slow.

I guess this could potentially be a database configuration issue, but I've been crawling over many of the SQL settings without anything obvious jumping out at me. Using TCP/IP opposed to named pipes, as the collected internet wisdom was that over a LAN named pipes have more overhead. Opened ports 1433 (TCP) and 1434(UDP) to support the SQL server and browser services respectively, even turned off firewalls (as much as IT allow me to..) but nothing seems to improve the lag. I'm sure that NI must have tested the ability of teststand to log to databases, and you would imagine that SQL Express would be up to the job of receiving a fairly modest amount of data... Any ideas or suggestions would be very welcome!

(BTW TestStand 4.2.1, Labview 2009 and SQL Express 2008)

Many thanks

[slaine]

A bit more info...

I ran a profiling tool on the SQL Express database to try and figure out the reason for the slow accesses. It would appear that the DB logging step does a SELECT * query on some of the tables at various points throughout the sequence. As the database grows, obviously the tables (especially the STEP_RESULT table) become fairly large, so this reading of the whole table starts to have a noticable time impact. Compound this effect with any slight delay from the network traffic, and that is where the long delay for a remote access of the database comes from. To prove the point, I backed up the database, then cleared out all the accumulated data. Running the test sequence, even on the remote machine was nice and quick again.

You have to wonder about the wisdom of reading whole tables during a step that is meant to be an 'on the fly' reporting mechanism. Granted our test system does generate a reasonably large quantity of test result data, so perhaps the effect is more noticable, but surely the point of logging test results to a database would be the collection of large amounts of data to facilitate analysis over time.

Maybe I'm missing some crucial configuration or setting that would turn off these large data requests from the logging step, but I would be grateful to hear if anyone else has either seen or solved this problem before.

I guess my options are to either build some automatic transfer of data from a set of temporary holding tables (that the sequence actually writes to) to the actual shared database, to radically change the sequence model and manage the database storage in another way, or to look into re-writing the activeX method within the DbLog server.

[Manooch_H]

Slaine -

You are correct that we are aware of the potential degradation in execution performance when performing On-The-Fly Database logging using the Generic Recordset schema (which I assume is what you have selected). You are correct in that the cause for the degradation in performance stems from the use of SELECT statements as opposed to INSERT statements. To workaround this issue, please see the following KnowledgeBase article:

Why Do I Encounter Delays in Execution When Logging to a Database with TestStand?

Hope this helps.

[slaine]

Manooch,

 Thanks for the information. I need to do some further testing to confirm that this has solved the problem, but the initial signs are encouraging. I didn't realise that the solution would be so simple to implement - I had envisioned hours of trekking through the C++ code behind the DBLog ActiveX server, so just having to change some SQL commands was a nice surprise. It's a shame that the operation of the whole database logging system isn't documented a little more fully somewhere. I've managed to piece together most of the information on how it works from the various knowledgebases and forum answers, but it would be nice to have a more thorough explanation on the mechanics of how it works somewhere like maybe the Teststand reference manual?

Once again thanks for the pointer... I had searched through the NI knowledgebase articles previously, and not found this one. Isn't it always the way!

[Manooch_H]

Slaine -

Glad I could help. Please let us know if you are not able to resolve the issue.