Database Logging is too slow

[Rupert Hewitt]

I am trying to make teststand log its test results in parallel, currently running at about 30 seconds to log to my database. I have an example

http://sine.ni.com/apps/we/niepd_web_display.display_epd4?p_guid=B45EACE3E93D56A4E034080020E74861&p_node=DZ52199&p_source=External

Which should do this but this isnot happy with 2.0.1f1 version of teststand, lots of errors on loading, and I can never get it to log successfully.

Has anyone made the parallel logging work ?

[Elaine_R.]

Hello Rupert -

The tutorial you are looking at was intended for use with TestStand 1.0.x systems and unfortunately is not very compatible with 2.0 and higher. Certainly I will look into making that clearer in the title and text of the tutorial so it doesn't cause confusion to anyone else. Database logging in 2.0 and higher is a much simpler affair (as you can see just by examining the current Database.seq and process model).

If all you are looking for is a way to allow the next UUT to progress while the previous one is still logging (for your SequentialModel.seq), then certainly a few minor changes will get you behavior identical to this example. Below I have attached a modified process model that has logging on a seperate thread. These are the notes for how I did it. Note that all my changes are inside the process model, I don't need to edit Database.seq anymore.

Change List:
(1) Open the subsequence 'Log To Database' and select Specify Module of the 'Log To Database' step.
(1a) set the multithreading pull down option to "New Thread" and press the Settings button.
(1b) if there are any check-boxes enabled, disable them and press OK to exit both windows.

(2) Go to the Parameters tab of the sequence and right-click on every parameter object to turn off the option Pass By Reference. This way the parameters will be copied (by value) and won't change when the UUT starts over.

(3)Go bact to your TestUUTs entry point sequence, and insert a lock step immediately before the 'Log To Database Callback' sequence call step. We do this to make sure that two different UUTs won't try and log simultaneously (not a safe behavior in general).
(3a) configure the lock step to "lock" give it a name, and enable the check box for Create If Does Not Exist
(3b) add another lock step immediately after the database callback, and configure it to 'Early Unlock' with the same name.

Certainly there are other ways to do this, I am simply giving one possible solution. Please let me know if you have any problems seeing this behavior in action or downloading the file.

--Regards

Elaine R.
National Instruments
http://www.ni.com/ask

[Rupert Hewitt]

Hi, Elaine, I tried this and it all looks good up to one point.

If you start a second UUT off running, before the first has finished logging, then hit the ok button before the database has finished its log from the first run, you get an exception when you hit the stop button at the enter uut serial no button, its like the sw is hung from the first run. If I hit retry at the error message the log to database sorts itself out....

Have attached the error log.

Looks good apart from that though ! 🙂

I may be able to figure out whats wrong, i'll have a look through what youve done, though I must admit I havent got to grips with the parallel operation bits yet !

Thanks

Rup


An exception occurred calling 'LogResults' in 'ITSDBLog' of 'DBLog
1.0 Type Library'
An error occurred executing a statement.
Schema: IPW Generic Insert.
Statement: UUT_RESULT.
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Source: TSDBLog

[Elaine_R.]

hello Rupert -

When I was testing this sequence I think I checked for that, so it is strange you're seeing it.

In short, what I did here was... to make the problem really obvious, I turned off execution tracing on a test with 200+ results (usually taking 5-6 seconds to log to my little database) so that the execution time was dramatically shorter than the logging time. What I see is (1) the first UUT starts and runs as usual, (2) as it is still logging (after the report is generated) the next UUT dialog will come up and you can put in # and hit enter (or just hit enter if you want to test the speed w/ no serial number) (3) the second test should begin to run even if the logging on the first test is still going (likely the case)...

In an
ideal world, the prior test's logging will finish before the second test needs it. But, in the case where it hasn't finished yet, the new test will wait at the 'Lock' until the first test reaches the 'unlock'. Or at least that is what I am seeing on my machine here.

From looking over your error it seems that you are using a custom Schema at least, is there anything custom about your process model (aside from this)? Would it be possible for me to run your process model here? or is it dependent on hardware? Somehow it doesn't look like it locking fast enough (or in the correct place) and two calls to the Database are happening at the same time.

At this point I'd have to see your sequence to guess what might be going on.

--Regards

Elaine R.
National Instruments
http://www.ni.com/support

[Rupert Hewitt]

Hi Elain,
All Ive done was as follows,

create a sequence with 1000 loops of a numeric limit test (none adapter),

I am using a custumn schema, I copied the standard schema and added in some of my custum step types but these shouldnt be being used in this sequence.

I have copied your model into my components NI.... directory and set this as the model type.

I turned off report of results and disabled tracing, to get a quick a test as possible.

I will try changing the schema back to the standard one, and repeat again, this will probably be this evening as I'm at our contract manufactureres site at the moment. (I'll also have to revert back into an access database as I dont have the MS SQL server here)

Rupert

[Rupert Hewitt]

Hi, Elain,
I had a bit of a play with your parallel dB sequence last night, I only had access as my database type and your code worked perfectly.

I think my problems may be down to using MS SQL, either the stored procedures or maybe a configuration problem on my part.

I dont have access to the SQL database until monday, I'm going to check the config and get back to you.

Rup

[Elaine_R.]

Rupert -

Thanks for taking the time to try and troubleshoot your database with my example. I was only able to test with an Access database here, so you may very well be onto something. I have not seen (yet) any particular problems with SQL Server in general when dealing with multiple threads, since all the data is local to the API call that completes the logging I don't think that may be a problem. All I can think of is your database or one of its proceedures might be sensitive to the fact that the database connection is opened by one thread (the process Model) and logged to in a second thread (that Database Callback subsequence). If this is the case than it may require further customizations of your process model to
make sure that the database setup and logging all happen on the same thread.

I will see if I have access to an SQL Server database here to run a few tests but any feedback you have will certainly be helpful.


--Regards

Elaine R.
National Instruments
http://www.ni.com/support

[Rupert Hewitt]

Elaine,
Well I eventually got to check the code out again and it looks to me that there is definately some issue with the SQL server stored procedures schema, I dont really understand why, but if I log to the same SQL server database using a standard generic insert schema then all is fine.

I did a minor evaluation of a speed test between the two types of schema and although the stored procedure schema is a 5 to 10 seconds quicker because the logging is now hapenning in parallel it doesnt really make any difference to me.

I think the standard generic insert should work fine for me but you may want to feed the info back to your developers about the stored procedure issue, im happy to provide more details if
you need them !?

[Elaine_R.]

Hello Rupert -

would it be possible for you to send an email summarizing your problems with the multithreading and the "SQLServer Stored Proc" schema you were using. I was unable to duplicate any errors here but we would be interested in trying again to make sure that there is nothing overlooked. If you could send an email via the ni.com/support page, I will be able to catch it on this end and run through another set of tests.

Regards,
Elaine R.
National Instruments
http://www.ni.com/support