Property Loader SQL

[Paul_Buehlmann]

Hi,

We are loading the Limits from Access with 'OpenDatabase Step', 'OpenSQLStatement Step' and the 'Property Loader'. This worked fine for years. Now we moved the Limit-Database to an SQL-Server. The SQL-Querry remained absolutly the same because the Database Schema is the same:

SELECT Tbl_Stepname.Stepname, Tbl_StepLimit.LowerLimit, Tbl_StepLimit.UpperLimit, Tbl_StepParam.ParaString
FROM Tbl_StepParam INNER JOIN (Tbl_Stepname INNER JOIN (Tbl_StepLimit INNER JOIN Tbl_SeqProperty ON Tbl_StepLimit.StepLimitId = Tbl_SeqProperty.StepLimitId) ON Tbl_Stepname.StepNameId = Tbl_SeqProperty.StepnameId) ON Tbl_StepParam.StepParamId = Tbl_SeqProperty.StepparamId
WHERE Tbl_SeqProperty.LimitParamSetId=112;

For Loading the Limits and the InBuffer the Step-Property-Loader needs !!! 17.257 sec !!! when we are working with the SQL-Server (to load 143 Steps). 

The Same  Data with the Access-Database needs 0.154 Sec.

I don't unterstand what the Step-Property-Loader is looking for.

Of Course in the SQL-Server-Database are much more data, but the result of the mentioned querry is 143 Records.

The previous Open SQL-Statement-Step works very fast and the local variable 'NumRecords' has the value of 143 as expected.

I tested the Querry with the Database-Viewer-Tool in TestStand. No Performance Problem occured. I also tested the Querry in the SQL-Server-Managment Studio and

everthing works fine.

We have installed:

-SQL-Server 2008

-TestStand 4.2 (Engine 4.2.1.83)

-CVI-2010 10.0.1 (419)

Does anybody know these problem? Perhaps we have to update TestStand to a newer Version?

Thank you for your help

Best Regards

Paul Bühlmann

[Norbert_B]

Paul,

what happens if you simply copy the steps to a new sequence?

Also you might check if your open database function is missing user right specifications for the provider....

Norbert

[Paul_Buehlmann]

Hi Norbert,

Thank your for your inputs!

-If I simply copy the steps to a new sequence the PropertyLoader has the same behaviour (needs the same time as before)

-I tried different Connection-ways, with the following ExecutionTime for the Steploader

24.6sec(Odbc):  "Provider=MSDASQL.1;Persist Security Info=False;Data Source=TestLimitenDb" 

16.9sec:        "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=TestLimitenDb;Data Source=...Servername..." 

16.0sec:        "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=TestLimitenDb;Data Source=...Servername...;Initial File Name="";Server SPN="" (We work with windows integrated security)

With the Dababase-Viewer-Tool (in TestStand) the result of the Querry appears very quickly.

And when I unterstand the things correctly the querry has already been executed (in the OPEN SQL Statement step)

Paul

[Norbert_B]

Paul,

your sequence looks quite weird....

No, "Open SQL Statement" does NOT execute the statement.

I am wondering about your SQL steps.... you create the statement just to use the handle in the Property Loader (without its content)?

The Property Loader step uses a constant select statement instead of the previously built statement......

Additionally, there seems some odd behavior in your Property Loader step:

Opening the configuration dialog takes a couple of seconds (2-5 i would say) until the dialog is responsible. It seems to me that you have some corruption in it.

If you re-create that property loader in a new sequence (well, yeah, typing all that stuff again):

Does it also lack performance?

Norbert

[Paul_Buehlmann]

Hi Norbert,

You're right, the Sequence I attached is confusing. Sorry, I attached a newer version, which I tried out with TestStand 2012.

Open SQL-Step:

In the help of this step is written:

"...If it does not require input or output parameters, the step immediately executes the SQL statement."

The variable 'Locals.NumRecords' has the value of 143. Which tells me that the querry has been executed. (The the Execution Time of the step are some Milliseconds)

But the PropertyLoader still needs a lot of time

Finally I tried out all my tasks unter TestStand 2012. Every step in the Setup of the Sequence File I created new and the result is the same.

Your notice, that opening the Property Loader configuration dialog takes a couple of seconds is correct and appears under TestStand 2012 also. As soon as you change the Data Location in the PropertyLoader from File to Database, these behavior appears (by opening and closing of the dioalog) and seems to be normal.

I think the Property Loader Step in my Application is not correctly configured, or there is a bug in it's implementation. Mention that, the same concept works with MS-Access instead of MS-SLQ-Server properly.

I'm thinking about reporting my problem to NI as an SRQ. In the past I made the experience, that the TestStand-Forum is the faster way, but at the moment I don't get a step forward.

What do you think Norbert?

Paul

[Norbert_B · Accepted solution]

Paul,

you are correct about the documentation of the Open SQL Statement step. As your step obviously does not set the option "Require Parameter", it should immediatly execute. Which obviously matches your observation (which on the other hand confirm correctness of the documentation).

To sum up a little:

1.) The delay occurs solely by the Property Loader step

2.) The delay only occurs if a MS-SQL Server is configured. Using a MS Access in the Property Loader does not show the delay (both databases comparable, also in amount of content?)

3.) The delay occurs in TS 2012. Previous versions of TestStand do not encounter this delay.

Please comment on each point and, if necessary, correct them describing the appropriate situation. Thanks.

Have you already tried to create a complete new Property Loader with a similar configuration? Does that also encounter the delay?

Norbert

[Paul_Buehlmann]

Norbert,

1.) The delay occurs solely by the Property Loader step

-> correct!

ExecutionTime with SQL-Server in seconds:

Open   Database	0.0135375
Open SQL Statement	0.0225255
Property Loader	18.3694554
Close SQL Statement	0.0011199
Close Database	0.0013043

2.) The delay only occurs if a MS-SQL Server is configured. Using a MS Access in the Property Loader does not show the delay (both databases comparable, also in amount of content?)

-> Correct. It Appears only with the MS-SQL-Server 
    The Database is absolutly comparable, but the amount of Data is not the same. In the SQL-Server we have more data. But the Execution-Time of the querry is very fast    (with  Access-Connection and SQL-Server-Connection)

    For the tests I used the same data and the same amount of records (143 records)

3.) The delay occurs in TS 2012. Previous versions of TestStand do not encounter this delay.

->  Not correct: The delay encountered with TestStand 4.2 (our actual installed Version) and after this I made my last Tests in TestStand 2012, showed same problem. 

     Other Version I had not tested.

Have you already tried to create a complete new Property Loader with a similar configuration? Does that also encounter the delay?

- > Yes I had tried this.

Paul

[Paul_Buehlmann]

1.) The delay occurs solely by the Property Loader step

-> correct!

ExecutionTime with SQL-Server in seconds:

Open   Database	0.0135375
Open SQL Statement	0.0225255
Property Loader	18.3694554
Close SQL Statement	0.0011199
Close Database	0.0013043

2.) The delay only occurs if a MS-SQL Server is configured. Using a MS Access in the Property Loader does not show the delay (both databases comparable, also in amount of content?)

-> Correct. It Appears only with the MS-SQL-Server 
    The Database is absolutly comparable, but the amount of Data is not the same. In the SQL-Server we have more data. But the Execution-Time of the querry is very fast    (with  Access-Connection and SQL-Server-Connection)

    For the tests I used the same data and the same amount of records (143 records)

3.) The delay occurs in TS 2012. Previous versions of TestStand do not encounter this delay.

->  Not correct: The delay encountered with TestStand 4.2 (our actual installed Version) and after this I made my last Tests in TestStand 2012, showed same problem. 

     Other Version I had not tested.

Have you already tried to create a complete new Property Loader with a similar configuration? Does that also encounter the delay?

- > Yes I had tried this.

Paul

[Norbert_B]

Paul,

one question i forgot regarding the comparison of MS-SQL vs. MS Access:

Are both local?

If not: Both per network? Same hosting system?

Norbert

[Paul_Buehlmann]

Hi Norbert,

Both are per Network (inhouse). But the SQL-Server is a separate Server.

We also save our measurements on the same Server with TestStand, but in a different Database.

Paul

[Norbert_B]

Paul,

from your information, i would expect different performance between MS SQL and MS Access because

- the size of the database is different, in favor for MS Access (smaller dataset)

- the communication to the hosting system per network can have different effect on the transfer (none in favor with current information).

Nevertheless, the difference is too great to simply explain it by these two keypoints.

What i ask you to do to dig down further:

Can you please create a little dataset in MS SQL as new database (same host) which you load using the Property Loader?

Essential question which i am after:

Can you see a direct relation between database size and runtime performance of the Property Loader step?

thanks,
Norbert