How long should a BulkEdit Import take?

[Tanquen]

If I look at the status in ViewX I can see that there are a few hundred or more Outstanding Template Transactions and it's processing maybe 4 or 5 each second.

I have a little faster PC at home and when running the same import on a copy of the same VM there are no Outstanding Template Transactions. That don't seem right but when I look at the PointNumber they have been updated.

The import only has the FullName, Name, OutstationId and PointNumber columns.

I say it don't seem right as I've only run the import on the VM on the work PC before now and it has always been this slow.

[BevanWeiss]

I suspect that the system that is slow has additional load to it, like field communications, other clients connected etc.

It possibly also has badly configured anti-virus or other things which are resulting in overall bad system performance.

Template Transactions can only be processed if it can obtain and action a database lock.  So you should check your current lock statistics.  You want these to be low (less than 10% total time in lock is typically good enough).

If there are template transactions then you are obviously updating a template, so you might want to have a look at the release notes of new versions of Geo SCADA, there have been some improvements to template propagation in recent releases.

[Tanquen]

It's a virtual machine and I just copied it from my work computer to my home computer and the network is disabled in the VM. The host machines are clean Windows 10 installs with nothing else really installed other than Microsoft office. The task managers on both look very similar for CPU usage and hard drive usage before during and after the imports. Also, one of the first imports I did was typical or what has been typical where you always have to wait while it completes the template transactions before you can do any new modifications. So I've gotten in the habit of checking the status and seeing how many are left. It took a while to figure out the first time that happened as I'd run an import thinking it was done and then want to make new changes or continue working on the application and didn't understand why I couldn't make any new changes. Now seemingly out of nowhere the VM on my home computer finishes the import instantly with no transactions and when I go in and look at the settings I wanted to change they appear to be changed. 

Normal to me is using the bulk edit tool to do an export, the export is usually very fast under a minute or so if that. Then after modifying the CSV I run an import and that's usually much slower and you can see the process and the status bar at the bottom of the bulk edit tool. After that's done I go in and look at the status and normally see a few hundred or after larger imports at few thousand outstanding template transactions. But now after moving the virtual machine from one computer to the other and doing an original import that did show outstanding transactions now no longer seems to show any outstanding transactions even if I do a large test export and import. So that made me think that something's been wrong all along and is now changed but it's still wrong on the other VM.

It's almost like when you do an import in ViewX and you're like wow that was fast and oh it's got an error of some kind in the messages so it didn't actually run the import, that's why it finished so quickly. This seems to be finishing unbelievably quickly with no errors. So that's one of the first questions, is it normal to see a lot of outstanding transactions that take a long time to clear. Is it normal to see any. My home computer may be a little faster but not to the point where something that takes now 10 to 15 minutes on a previous instance of the very same virtual machine is now instantaneous. Like now it's fast enough to finish the same time the bulk edit utility finishes.

I'm not sure where to read the total time in lock. I did find the server status read write lock diagnostics but there's hundreds of rows and I'm not sure if you're just looking for the row with the longest time in readlock percentage. There's only one above 1% background integrity check at 1.7%.

[Tanquen]

Found the mouse over DB Lock Usage and the VM on the slower work PC is at 37.5% and the little faster home PC is at 39.6%. 

A base copy of the same VM before the import of the current app is resting at 4.0% on the same work PC.

[BevanWeiss]

'before the import of the current app'... you mean a completely empty database has 4% lock?  That doesn't sound right.

~38% isn't a good number to have your lock rate at steady state.

It's not going to kill your system, but you will suffer some significant performance lags when making DB configuration changes.

You should probably get someone to audit the system to determine what items are resulting in the high lock ratio, and to get that lower.

Configuration actions typically don't get actioned instantly through out the database.  They need to propagate from the top down to the bottom, and to do that it needs a lock on the database.  If your system is already locked close to 40% of the time.. that doesn't leave much of a window for the template propagation events to occur (which will themselves increase the lock ratio also.. since they need to get a lock to make the changes).

You should also note that Windows 10 is not recommended as a server environment.  It prioritises UI events above background service tasks, which is definitely not desirable for a server.  It also has licence limitations that means that in most situations it is not allowable to be used as a server (you should read the Microsoft Windows EULA around this).

[Tanquen]

I let the other VMs run without doing much and they all seem to settle to a lower number like 1.234325% or even 0.312677%. Maybe an import has a much delayed and long effect. I'm not sure as it is mostly hours or days between imports but I've not watched that setting before now. 

As sort of a test this morning I launched the primary VM (The one I work on the most) on my work computer and a backup and then a copy on my home machine.

The one running on my home machine is setting at about 17% CPU usage with 12 to 13 of that being the ViewX app and the lock usage is at around 1%.

The backup running on the work computer is pretty much the same but a little better with 3% to 4% CPU usage and the lock usage is at 0.342677%.

The one I was working in all day yesterday on my work computer was not showing me the lock usage at all for a time but right now it's showing 0.766556% but my CPU usage I noticed yesterday afternoon went crazy at over 50% consistently for the ClearSCADA ODBC driver. I've seen that before and I'm not sure what sets it off I'm just creating templates and instances. The application is getting rather large and this is the largest site in their system and I've actually had to delete some old sites to make room as we're hitting the 25,000 object limit.

These are all virtual machines and they are all copies of the same VM running Server 2016 has their operating system. Then the hosts are running Windows 10.

I was wondering if pausing the VMs is a problem. Each night when I'm finished I back up the database and move it to another machine and then pause the virtual machine and shut down the computer.

I'd really like to settle the ODBC driver as that really seems to slow down the ViewX interface quite a bit and make it not much fun to use.

I'm tempted to end task or reboot the machine but I'm not sure if that'll break something or make things worse.

[Tanquen]

What's the best way to check the point count in a group or template or instance? I was hoping it would be on the View status and on the template instances there's a display objects but that's not really it either. For now I've been doing bulk edit exports and then looking at the number of points.

[geoffpatton]

I have done the exact same thing or you can do an SQL query on CDBPoint where FullName like your template's name.

I do this usually by opening the Events and then editing the SQL. In newer versions of ClearSCADA/GeoSCADA your user has to have the right to Edit SQL.

[geoffpatton]

Maybe it is your Virus Scanner monitoring everything that the ODBC driver does? Try setting your Virus scanner to ignore the ODBC Driver.

[Tanquen]

I did a reboot yesterday and the ODBC driver is back to normal or nowhere to be seen in task manager. I don't know what sets it off but once it gets triggered it's like locked at 50 plus percent thereafter.

I removed Windows defender from server 2016 and I guess we'll see if that helps. but I have to reboot and that means I lose all my bulk edit settings again, man they really could polish that program a bit.

[BevanWeiss]

As I mentioned in my very first post, and as Geoff has mentioned, you should check your anti-virus to ensure that you have it configured correctly in accordance with the Schneider guidance

https://tprojects.schneider-electric.com/telemetry/display/public/CS/Anti-virus+Scan+Exclusions

[Tanquen]

Sorry the first mention of it was pretty vague and sounds like a million other references to virus scanners causing all sorts of nefarious things and it never turns out to be the virus scanner. As I already said, I've uninstalled it so I'll see what happens.

Thanks for the link. The info there doesn't sound like anything that I'm experiencing but who knows turning off the virus scanner may help, There's the first time for everything. 🙂