DataAdapter Deferred Commit

[Lihov]

Hi, All!

I rebuilt my SmartConnetor.OpcClassicalExtension for SmartConnector framework v2.3.113 with using of deferred commit feature in DataAdapter class. I got the following results - for 3000 OPC tags data update period duration in EWS server decreases up to 13 seconds from 105 (detailed info by link -

https://docs.google.com/spreadsheets/d/1Uy16JujwfwZbfahqKgbUoAGWs2hOWAcWFJw44Wf75y4/edit?usp=sharing​ ). It is great but not enough because the increasing of OPC tags number enlarge the EWS server update data duration. And when we have 10000 OPC tags the result is unacceptable. Also the CPU usage during data update period is 30-40%...

So, my question is how to obtain low values for update data period (<1000ms for 10000 of OPC tags)? May be using CSP classes help me? Described case is a limit for DataAdapter?

[sesa180908_brid]

EwsClient is used to communicate to any EWS Server.

CspClient is used to communicate to SBO only.

The EwsServerDataAdapter is used manipulate data for a SmartConnector EWS Server.

If the deferred commit in the EwsServerDataAdapter is not sufficient, you could use a direct ODBC connection to the SmartConnector database.  You would be responsible for all validation and data integrity requirements though.  That is why this approach should be used with great caution.  I'm not sure if that would actually help in this situation though but you would be circumventing any EntityFramework (ORM used in SmartConnector) overhead.

That said, I'm not completely clear what all the entries in your spreadsheet represent. A diagram showing the systems involved and protocols used would help.

[JeffBowman · Accepted solution]

Hi,

Unfortunately at this time, the data adapter isn't as 'Write optimized' as one may like. There are ways around this, but it will add some complexity to your update code, as you will need to rely on the Database context directly, and not the adapter to get the faster speeds.

You will need to add a reference to Mongoose.Service.Data in your processor class, as well as reference Mongoose.Service (from your SmartConnector installed directory in your project).

Here is a simple example about how to mass update points (which should in theory be faster.). But it does not include any validation and it will not update any HistoryItems the ValueItem is attached to, it is only just an example of how to use the DB context. I need to stress, you need to be in incredibly, incredibly careful doing it this way, as it can have unintended consequences, which can get you in a lot of trouble.. Also, while we support issues that may come out of using the DataAdapter, we cannot support issues that come up doing it this way.

private void UpdateExistingValues(List<PointToUpdate> pointsToUpdate)
{
     using (var dbContext = new MongooseDbContext())
     {
          Logger.LogTrace(LogCategory.Processor, this.Name, "Updating Existing Valueitems");
          var ewsServer = dbContext.EwsServers.FirstOrDefault(a => a.Id == _dataAdapter.Server.Id);
          if (ewsServer != null)
          {
               var values = dbContext.EwsValueItems.Where(a => a.EwsServerId == _dataAdapter.Server.Id).ToList();
               foreach (var pointToUpdate in pointsToUpdate)
               {
                    var valueItem = values.FirstOrDefault(a => a.AlternateId == pointToUpdate.Id);
                    if (valueItem != null)
                    {
                         if (valueItem.Value != pointToUpdate.Value.ToString())
                         {
                              valueItem.Value = pointToUpdate.Value.ToString(); // You need to make sure the Ews Value Item type is this point type.. or else it can cause issues later..
                              valueItem.LastModified = DateTime.UtcNow; // You MUST set this, so that EWS subscriptions will pick up the change.
                         }     
                    }     
               }
               dbContext.SaveChanges();
          }
     }
}

In one case, I was actually able to reduce the time it took to do the creation of a lot of points from 24 minutes.. to 9 seconds.

Regards,

-Jeff

[Lihov]

Thanks a lot for responses. I'll try it.

[JeffBowman]

Any luck with this?

Regards,

-Jeff

[Lihov]

Hi, Jeff.

I had an urgent task, and I could not check it immediately. Yesterday I did it.

The duration for update operation is 1500-2000 ms. I think it is acceptable results.

Thanks a lot for your help!