PCS performance

[Ian Mitchell]

I'm using PCS to access a remote database for the first time, using a very fast connection (1Gb up, 80Mb down, very low-latency (5ms)) and I'm surprised by the performance.
All these numbers relate to using the EA API, not the EA user interface, which seems to perform fine - not as fast as a local QEAX file, but perfectly usable.
The difference when using the API - for read-only operations - between local QEAX and PCS/remote database performance seems to be a factor of 30-40. So an operation (just fetching the current package, then fetching each element from it in turn) is 200ms on a local repository, but around 4 sec using a cloud repository.
Note this just using the API directly: none of my code is being used.

Is this the expected result? Or am I just using a connection or a repository which needs some tuning? If so, what tuning should I do?
Thanks

[Modesto Vega]

With PCS the first thing that you need to rule out is the network connectivity between your client and PCS, and PCS and the database. You need to consider the following:

• Where is the EA client hosted (physical computer, virtual machine, on the same network as PCS)?
• Where is PCS hosted (physical server, somewhere in the cloud)?
• Where is the database hosted (physical server, somewhere in the cloud, sharing the same infrastructure as PCS)?
• If anything is cloud hosted, are there any firewalls or other security gadgetry in between the various components?

[Geert Bellekens]

I'm not surprised. No matter how you look at it, you are still using a WAN connection to your database.

To rule out PCS as the source of the problem, you could experiment using a direct database connection instead (if you can get the infra guys to agree to open up the necesarry ports on the firewall)
I would expect the direct connection to performance the same at best, but probably worse.

The only real solution I know is to put the client application as close to the database as possible.

I have a lot of clients using either Citrix or Microsoft Terminal Services technology (Remote Desktop, AVD, WVD, whatever they call it this week)
The client is then installed in the same datacenter as the database, so as close as you can get.

It's still not as fast as a when I run tests on my laptop using a locally installed database, but usually fast enough.
My performance tests show a difference of factor 2 or 3 (so 100 seconds locally on my testmachine is 200 to 300 seconds on the production client)

Yours is a factor 20 difference (200 ms -> 4000 ms)

Another big thing is to circumvent the API as much as possible.
I recently refactored a big part of my framework to use only database queries (Repository.SQLQuery) for anything that is not writing.
This was a major improvement to the performance of my tools.

The next thing I'm working on is to use my own database connection instead of relying on Repository.SQLQuery.
Initial tests show this might improve performance with a factor 10 or more.

Geert

[steverumsby]

What Geert said!

My experience is exactly the same. When accessing from home (500Mbps down, 70 up but with a VPN in the way slowing everything down), PCS is noticeably faster than direct DB access (PCS is batching/optimising the DB accesses I assume?). But also, API access is just slow. You maybe don't notice on a Gbps internal network, but it just is. For read access I have converted all my code to use SQL queries, and that speeds things up by 10-20 times, maybe. Things that took minutes now take seconds.

Updates I still do through the API, obviously, and there's generally less of that (for what I do) so the slowness is less important. Occasionally, though, I'll have a script that sits and runs for 10or 20 minutes, sometimes longer, because it is doing a lot of updates.

Steve.

[Modesto Vega]

Just to clarify, we use PCS both from home over a VPN and from the office, with PCS hosted side-by-side with SQL Server on a VM in the cloud. It is not massively fast but we do not have performance problems. Just keep in mind that the transactions are not committed to the database in real time, they are queued/throttled, emphasis intentional. Prior to moving to 64-bit PCS and EA had a poor performance in the configuration described above.

[Ian Mitchell]

Thanks to everyone.
I'll try to get access direct to the DB, and see what difference that makes. I don't know how close PCS and the repository are, so I'll check that as well.

I had an expectation that this would be slower, just not this slow.
I think I may have to go to the 'SQL Only' solution, and optimise repository access a lot more, but I hate doing stuff like this. I makes the code more messy, and introduces loads of errors. But in this configuration, I guess it's the only solution.
There is scope here for a Sparx 'super API' which could do stuff like 'fetch me all the elements in the package'. I already have these kinds of calls for Model Expert, as I was expecting it to be very heavy on repository access, so it doesn't sufer from these issues as much.
But to use SQL everywhere and not the API is a big coding overhead.
Sigh.

[Geert Bellekens]

There is scope here for a Sparx 'super API' which could do stuff like 'fetch me all the elements in the package'. I already have these kinds of calls for Model Expert, as I was expecting it to be very heavy on repository access, so it doesn't sufer from these issues as much.
But to use SQL everywhere and not the API is a big coding overhead.
Sigh.

Not necesarrily. I was able to "simply" replace the API classes in my code by DBWrapper classes, that happen to have the exact same signature as the API objects.
That reduced the impact severely; plus I can focus on the objects where the performance was worst, and add DBwrappers step by step.

So all I needed to do was create a class with the exact same interface as EA.Element, that gets it's data from an SQL Query.
And then of course add some methods to get all classes of a package or branch in one go. (so I only need a single query)

You can find the DBWrapper classes I already did here: https://github.com/GeertBellekens/Enterprise-Architect-Add-in-Framework/tree/master/EAAddinFramework/EADBWrappers
I might add more in the future, but not onless I see a real need.

It was ofcourse some work to write the DB Wrapper classes, but the impact to the rest of my (functional) code was minimal. (change type from EA.Element to EADBElement)

I did already quarantined the EA API to a specific layer of wrappers in my framework though, so that helped to contain the impact as well.
If the EA.xxx classes are used all-over in your code, I guess there will be a bit more impact.

Geert

[Elpis]

I'm using PCS to access a remote database for the first time, using a very fast connection (1Gb up, 80Mb down, very low-latency (5ms)) and I'm surprised by the performance.
All these numbers relate to using the EA API, not the EA user interface, which seems to perform fine - not as fast as a local QEAX file, but perfectly usable.
The difference when using the API - for read-only operations - between local QEAX and PCS/remote database performance seems to be a factor of 30-40. So an operation (just fetching the current package, then fetching each element from it in turn) is 200ms on a local repository, but around 4 sec using a cloud repository.
Note this just using the API directly: none of my code is being used.

Is this the expected result? Or am I just using a connection or a repository which needs some tuning? If so, what tuning should I do?
Thanks

For me the difference is quite reasonable. Assuming, every query against local/lan database repository is one-for-one replaced with LAN/WAN request against PCS (not sure about that, but I suspect so in most cases), then with PCS we have additional overheads: serializing/deserializing request and response (this can be quite big...), sending them over WAN, handling them in PCS (not to mention getting data from DB server), ...
So, while getting query data from local/LAN repository can be as fast as well under 1 ms, the same operation on PCS can take teens of ms or more, even on the relatively fast net link.