Can anyone using SQL Server provide some times it's taking to write results to the database. I'm implementing a new test system that uses TestStand (4.1) and the only thing holding up the deployment is how long it's taking to write the results. The test runs about 6 minutes and there's about 1500 steps to record. I'm using a slightly modified generic insert schema and the logging is taking about 2 minutes though I've seen it take as much as 8. At the last place I worked, I was using TestStand 2.0 but my recollection is that the logging was done in a few seconds. Has anyone experienced high latency in their writes and know what the solution is? Our IT/Network/DB departments have all looked and either have no idea or seem to think the time is normal.
I have no idea if that's related at all, but we had a problem with bad database performance in a recent application (using MS SQL Server Express 2008). It was getting slower and slower the larger the database became.
We decided to clean out the database with each new batch of UUTs since we needed it only for collecting statistics for the current batch. This improved things but not too much.
Then I noticed that the orignal design was to open the database at the beginning of each sequence dealing with it and close it at the end of that sequence. This made for a clear management of resources, but seemed to kill performance. So we switched to opening the database once in ProcessSetup and close it in ProcessCleanup. Since then it is performing nicely (we kept the cleaning-out option however, mainly because we didn't have the time to rewrite that part again).
This is something i personally have never experienced, nor have I had anyone else mention that their database logging took a large amount of time. What version of SQL Server are you using, and what modifications to the schema have you made? If you use on the fly database logging does it take the same aggregate about of time?
We're using SQL Server 2005. We've modified the generic insert schema by adding three additional tables. These are for recording some results from some custom steps and are linked to the STEP_RESULT table. I've run it without these tables and it does not seem to make much difference. Yes, using on-the-fly logging takes the same amount of aggregate time.
Can you try running one of the examples from C:\Documents and Settings\All Users\Documents\National Instruments\TestStand 4.1.1\Examples\Demo using the default schema and let us know what the write to database time is, as well as total execution? Also can you go into the command window and ping the database server, and let us know what the results are.
I used the LabVIEW Computer Motherboard Test Sequence. When I first ran it, the logging was almost too quick too measure. So, I placed a for loop around the steps in the main sequence. I set the loop to run 500 times to get approximately the same number of steps in my sequence. The logging of that data took 2 minutes and 6 seconds. The execution time was 45.16 seconds. The database schema was the generic insert.
When I ping the database, I get response times of 8ms, 6ms, 14ms, and 14 ms. Average is 10ms. TTL for all is 122 and I had 0 lost packets.
Experienced this as well, the item that helped the most was to use start all statement in the command text for all statements in schema:
"SELECT TOP 0 * FROM ...."
the ... would be the name of the table
Also we enable "Share Data Link between executions" and "Use Transaction processing" on data link tab of database options
Thanks for the suggestion but the generic insert schema does not use the SELECT statement and I've tried all of the options with sharing and transaction processing with little effect.
So I tried the same test looping the same example, it took 1:22 to execute and roughly 35 seconds to finish logging to the database and write a report. I don't have the same database (using access), and i'm logging locally, so we're certainly not comparing apples to apples. If you convert this example to log to your database in LabVIEW, how long does it take to loop 8000 times? On a local database it takes roughly 50 seconds. If anyone else has the same database, and can try looping the demo example, we would certainly appreciate the information.
I know this is an old post, but I am having the same issues. Was there a resolution to this problem? Yes, I did try setting the SELECT statement to '0' and also tried the "Share Data Link..." and the "Transaction Processing" all with no luck. The problem seems to have started when our server moved to China, but the same problem occurs when using a local replication server. Now if I set it to write to the local hard drive, transactions seem nearly instant, but writing to the server with only a few results can take an extreme amount of time, minutes when it should only take seconds.