Large Model and Better Performance

[colo]

Hi, I'm using EA (6.5 biuld 802) with a large model  (8700 objects aprox) and the repository is in SQL Server. There are some functions like (view Hierarchy, search, etc) where the performance is really decreased..
Have you any tunnig script to improve the performance in DB?
Thanks in advance

[«Midnight»]

I think you should submit a bug report to Sparx. They've done some work on SQL Server (and other repository) performance in the past, but there seems to be a pattern of slow response cropping up every few builds.

If you can, provide them with a zipped copy of your model.

[Dave_Bullet]

Hi Colo,

The problem is not SQL server unfortunately and indexing columns won't help (besides the tables aren't that big with 8700 objects - my repository is a similar size).  This is largely because EA takes a very singular "OO" approach to database access.  This means it makes many many database calls to return single rows of data - rather than do "bulk" retrievals.  the end result is a very "chatty" interface between client and server - slowing the system.  Turn on SQL Profiler and you'll see what I mean.

Some examples of the chatty interface:
- Project load - EA seems to retrieve the whole hierarchy and I'm sure this is one branch per SQL call (rather than the whole lot)
- Diagram load - One SQL call per element on a diagram (rather than 1 select to get all elements and another to get all connectors)

EA needs to be re-architected (or its data access layer modified) to cache or intelligently "forward retrieve" data.  The only solution you have is to put EA as close as possible to SQL server.  If you are planning on using a distributed team (WAN latency makes this problem so bad you can't use EA on a large model remotely) - then you can setup SQL Server merge replication between sites.  I've done this and it works.  Can help you here if you need it.

Cheers,
David.