Author Topic: EA repository performance on Oracle db  (Read 1178 times)

ea1217

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
EA repository performance on Oracle db
« on: September 15, 2018, 01:05:11 am »
Hello,

We experience performance problem on our EA repository, mostly felt during copy, delete and doc generation.

EA version is the 13.5.1351 for all clients. The reposory is set on a Oracle 12.1.0.2.0. The auditing option in the repository has been disabled. However, we use group locks.

The performance problem are clearly observed during copy, delete and more importantly during the doc generation. To determine the causes of the problem, we ran some tests using wireshark and oracle tools. Our conclusion is that our network and database process and response times are OK. Our hypothesis is that the problem comes from the number of queries to the db sent sequentially in small packets by the EA client to the database.

Our test consist in
- the copy of a set of objects from a diagram to new empty one
- the deletion of the same set in the target diagram
- the doc generation of the package containg the objects and the target diagram
The object set consists in 39 objects used to model a screen (33 objects from the user interface tool box and 6 from the common toolbox) and 6 links between objects. Those are simple object (only a short description, no internal constraint nor scenario). The tests were executed in our production repository. Then the total number of it could have introduced a bias.

A few numbers to justify our conclusion. The copy as new took in average 150sec per run. Wireshark shows no timeout during the process and an exchange aroung 70000 packets for a total exange of 26Mb. The max transfert speed was at 2,7Mbps. Most of the network to db access times were 1-2ms a few ones at 41ms. During the operations there were:
- 13803 select queries or sub-queries 
- 173 insert
- 956 update
- 1 delete

The doc generation took in average 80sec. The network speed, server response time and tcp packet size were similar to those of the copy with an exange of 20Mb. The queries on the db consist in:
- 1114 select queries or sub-queries 
- 2 update

The deletion was less critical but took in average 22sec.

Our questions are the following:
- Why is there so many sequential queries sent to the db?
- Why are so many data exchanged during those processes?
- Is there an existing solution to counter this problem?

Thank you for your help!

Jonathan

qwerty

  • EA Guru
  • *****
  • Posts: 10505
  • Karma: +231/-190
  • I'm no guru at all
    • View Profile
Re: EA repository performance on Oracle db
« Reply #1 on: September 15, 2018, 01:29:58 am »
I never used Oracle, but the forum is full of complaints when using EA and Oracle. People tend to switch to any other RDBMS which solves their performance issues.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9392
  • Karma: +258/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: EA repository performance on Oracle db
« Reply #2 on: September 15, 2018, 02:54:03 am »
Hi Jonathan,

The conclusion you reached seems to be consistent with my experience.

EA is a fat client that executes thousand upon thousands of tiny queries  every few seconds.

From experiences and feedback from users we know that Oracle performs terrible as the backend for EA.
The best performing databases are MySQL and SQL Server.

I'm guessing the MySQL and SQL Server are better optimized for the type of usage of EA.
Especially MySQL, which is very often used as the backend for websites. A typical website would show the same type of usage: lots of users executing relatively small queries in rapid succession. Oracle's typical usage is more like being the database of large ERP type systems. So I guess this database is better suited to treat a low number of large queries.

So, although it might be possible to tweak the database for EA, I've never come across a happy EA + Oracle user.
Most of them switch to MySQL or SQL Server at some point and are much happier after the switch.

Geert

Glassboy

  • EA Practitioner
  • ***
  • Posts: 1301
  • Karma: +103/-75
    • View Profile
Re: EA repository performance on Oracle db
« Reply #3 on: September 17, 2018, 06:55:57 am »
So, although it might be possible to tweak the database for EA, I've never come across a happy EA + Oracle user.
Most of them switch to MySQL or SQL Server at some point and are much happier after the switch.

I did have a file of suggested optimisations for Oracle but I appear to have lost it.  It ended up being easier to switch to MSSQL than to get an Oracle DBA's time to tinker.

Sunshine

  • EA User
  • **
  • Posts: 848
  • Karma: +66/-4
  • Its the results that count
    • View Profile
Re: EA repository performance on Oracle db
« Reply #4 on: September 18, 2018, 10:32:34 am »
Concur with all the previous comments regarding Oracle DB. I've been in 3 organisations with Oracle and 2 with MS SQL for the Database in last 10 years. Each time with Oracle we hit performance issues. After tweaking Oracle for months with no real benefit we moved to MS SQL and it immediately fixed those problems.
Perhaps there is an Oracle DBA out there who can make it perform well with Sparx EA but I've not come across them yet.
Happy to help
:)

ea1217

  • EA Novice
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: EA repository performance on Oracle db
« Reply #5 on: October 09, 2018, 02:12:07 am »
Thank you all for your answers!

As we are subject to strong constraints regarding the DBMS we can use, I hoped for another solution. Has anyone tried with Postgresql?

Glassboy

  • EA Practitioner
  • ***
  • Posts: 1301
  • Karma: +103/-75
    • View Profile
Re: EA repository performance on Oracle db
« Reply #6 on: October 09, 2018, 07:20:58 am »
As we are subject to strong constraints regarding the DBMS we can use, I hoped for another solution. Has anyone tried with Postgresql?

yes.  I've had both a bad experience and a good experience.

steen.jensen

  • EA User
  • **
  • Posts: 88
  • Karma: +1/-1
    • View Profile
Re: EA repository performance on Oracle db
« Reply #7 on: October 09, 2018, 08:26:09 am »
Maybe time to realize that Sparx EA developers are still in the 1990 and developes old fashion Client-Server apps with bad SQL-queries. Most of other old apps architectures have evolved to modern apps, but not Sparx, still in retro Client-Server architecture.

Glassboy

  • EA Practitioner
  • ***
  • Posts: 1301
  • Karma: +103/-75
    • View Profile
Re: EA repository performance on Oracle db
« Reply #8 on: October 09, 2018, 08:33:18 am »
Maybe time to realize that Sparx EA developers are still in the 1990 and developes old fashion Client-Server apps with bad SQL-queries. Most of other old apps architectures have evolved to modern apps, but not Sparx, still in retro Client-Server architecture.

I've had issues with Oracle with a host of other uses, so I think your comments are a little bit off.

Sunshine

  • EA User
  • **
  • Posts: 848
  • Karma: +66/-4
  • Its the results that count
    • View Profile
Re: EA repository performance on Oracle db
« Reply #9 on: October 11, 2018, 04:31:07 am »
Maybe time to realize that Sparx EA developers are still in the 1990 and developes old fashion Client-Server apps with bad SQL-queries. Most of other old apps architectures have evolved to modern apps, but not Sparx, still in retro Client-Server architecture.
Bit of a harsh negative statement there buddy  :(

Yes Sparx EA is a client-server app but not necessarily with bad SQL queries as they work fine on SQL and MySQL. You know modern databases not like Oracle which is from the 1980's and can't cope well with the style of query.

To be fair to Sparx Systems they have introduced Cloud Pro Server and Web EA over the last couple of years so they are changing the architecture to a that of a web oriented one. If you've done any development over the decades you'll realise it does take time to migrate a product from one style of architecture to another.

The important thing to remember is that Sparx EA works heaps better than anything else on the market. If you've found something better with a modern architecture then please share. I doubt you have :)

Happy to help
:)

qwerty

  • EA Guru
  • *****
  • Posts: 10505
  • Karma: +231/-190
  • I'm no guru at all
    • View Profile
Re: EA repository performance on Oracle db
« Reply #10 on: October 11, 2018, 08:05:58 am »
The important thing to remember is that Sparx EA works heaps better than anything else on the market. If you've found something better with a modern architecture then please share. I doubt you have :)
That's true. Sadly it could be sooo much better, though.

q.