dpQuery and Bonus value

[chris@blue-catalyst.co.uk]

Hi,

does the bonus value in dpQuery work when using RDB and Oracle? I've read in the help that the mode (Each datapoint for itself/Data at start time/All datapoints together) is no longer supported and haven't had any luck with bonus value.

I'm using v3.15 p11 and tried the following:

Bonus value: 0
SELECT '_offline.._value', '_offline.._stime' FROM 'mydp.state' TIMERANGE("2018.08.22 09:53:05","2018.08.22 10:15:07",1,0)

Bonus value: 1
SELECT '_offline.._value', '_offline.._stime' FROM 'mydp.state' TIMERANGE("2018.08.22 09:53:05","2018.08.22 10:15:07",1,1)

but both bring back only 2 results, both within the timerange.



When I use dpGetPeriod the bonus value does appear to work

Bonus value: 0
dpGetPeriod("2018.08.22 09:53:05","2018.08.22 10:15:07", 0, "mydp.state", dbVal, dtTime);

Bonus value: 1
dpGetPeriod("2018.08.22 09:53:05","2018.08.22 10:15:07", 1, "mydp.state", dbVal, dtTime);

This time the first returns the 2 results in the time period, the second returns 4 results, an additional value before the start and after the end time.


I have a number of items which I need to retrieve historical data for and would best suit a query rather than multiple dpGetPeriod's.

Should the dpQuery bonus value work or have I missed something?

Thanks in advance,

Chris

[leoknipp]

As far as I know the bonus parameter is currently not supported for RDB queries.
If you have a defined list of dp elements you can use dpGetPeriod-calls instead of a TIMERANGE query.

Best Regards
Leopold Knipp
Senior Support Specialist

[leoknipp]

As far as I know the bonus parameter is currently not supported for RDB queries.
If you have a defined list of dp elements you can use dpGetPeriod-calls instead of a TIMERANGE query.

Best Regards
Leopold Knipp
Senior Support Specialist

[chris@blue-catalyst.co.uk]

Ok, thanks.

It would be good if this information could be included within the SQL panel or online help.

Best regards,

Chris

[leoknipp]

The previous information was not correct. The bonus parameter can be used at TIMERANGE queries when using RDB archiving.
I have just tested it successfully with a project running WinCC OA 3.15.

Best Regards
Leopold Knipp
Senior Support Specialist

[chris@blue-catalyst.co.uk]

Hi Leopold,

Thank you for your clarification.

I tried this again but received the same result (i.e. no bonus values) and I wondered what the difference could be between our systems.

I have discovered that in order for you to receive bonus values you must have queryRDBdirect = 0, is this as per your setup?

This would suggest that the data manager is required to obtain bonus values?

As a SQL query is considerably quicker than dpGetPeriod we intend to setQueryRDBDirect() whilst obtaining bonus values, is this a suitable solution?

Thank you for your assistance.

Best regards,

Chris

[leoknipp]

I tested it again and I have seen that there is a difference if queryRDBDirect is used or not.
When data is read with queryRDBDirect = 0 the bonus parameter can be used.

Why do you think that a query with TIMERANGE for a specific DPE is faster then a dpGetPeriod?
Have you tested it?

Best Regards
Leopold Knipp
Senior Support Specialist

[chris@blue-catalyst.co.uk]

Hi Leopold,

Yes, there is a significant difference in speed when using a query over dpGetPeriod, on our system at least. The following is a quick example I ran on our system, the dpQuery took less than a second, the dpGetPeriod took 48 seconds, there are 60 dp's which it queries and I ran the code for one system.



time tStartTime = makeTime(2018,8,29,0,0,0);
time tEndTime = makeTime(2018,8,30,0,0,0);

DebugTN("QUERY START");

setQueryRDBDirect(FALSE);
// loop by system
for (int i = 1; i

[leoknipp]

I had a look at the script and made some test with a dpQuery and a dpGetPeriod.

With your script you cannot compare the results because
1) for the dpQuery you are using queryRDBDirect = 0
2) for the dpGetPeriod you are using queryRDBDirect = 1
3) for the dpGetPeriod you are reading data for every datapoint on its own (60 function calls compared to 1 dpQuery call)

In my tests I modifed the script and used all the time queryRDBDirect = 0.
Also the query was modified to read data for every DP on its own.

The time to read data is nearly the same in that case.

When doing a dpQuery for all datapoints it is faster than reading data for every dp on its own.

Also you have to keep in mind that reading exactly the same data again is probably faster then reading it for the first time.
If I do the same query in my test several times the first execution needs longer.
Therefore you have to be careful when comparing the results.

Best Regards
Leopold Knipp
Senior Support Specialist