performance problem on SQL-Server

[Geert Bellekens]

Hi,

I'm writing a little validation tool that visits (a part of) the model and validates some of our modelling rules against those visited items.
When testing on a local EAP file I got a run time of 7.5 minutes for 10.000 validated items.
When testing on a model on the SQL server I got 46 minutes for the same items.
Does anyone have an idea how I could improve the performance on SQL server repository?

Thanks

Geert

[Geert Bellekens]

I managed to increase the performance considerably, but not thanks EA or SQL server tweaking.
After some profiling I found that the Repository.FindElementByID was taking a lot of time.
I cached all element I already found and runtimes went to:

• local EAP file: 2 minutes
• SQL server (over the network): 10 minutes

So the overall time is better, but there is still a big difference between EAP and SQL server.
I'll try our database specialists now for some more help.

Geert

[«Midnight»]

What do you mean by "cached" in this context. Did you try the new (as of EA 7.5) network optimizer?

[Geert Bellekens]

With cached I mean store every element that was retrieved from EA in a dictionary. Everytime I need an specific element I first look in the cache dictionary. Only if its not there I call the Repository.GetElementByID.

With the network optimizer I guess you mean the WAN optimizer? No I haven't tried that since I work on a LAN. (and I don't control the SQL server machine) Do you think that could make a difference, even on a Gigabit LAN?
I did however check with the database guys. They took a profile and unfortunately there is not much they can do. EA fires an insane amount of queries on the server when I run my validator (about avg 131 per second)
I think it's the overhead of sending all those queries over the network that is killing the performance.

Geert

[«Midnight»]

I really don't know. Somehow the optimizer is supposed to work with EA on lazy loading. Whether this extends as far as some kind of caching (not in the sense of your earlier post) is anyone's guess.

[mrf]

The WAN Optimizer currently works by acting as a proxy to the EA client, located on the same machine/LAN as the DBMS. The WAN Optimizer performs queries on behalf of EA, compresses the data and sends the results back over the WAN to the EA client. This improves performance dramatically over a WAN where bandwidth is a consideration.

That said we have seen some improvements over 100Mb LAN connections, but only when the WAN Optimizer is running on the same machine as the DBMS server.

In future builds of the WAN Optimizer we plan to support batch querying, which should make tasks like adding elements, and applying sets of profile tagged values to elements much faster. We're also continually assessing the amount of SQL traffic EA emits and always trying to minimize it as much as we can - so if you have a particular area you are concerned about, please let us know.

PS: Options like auditing and some addins may cause an increase in SQL traffic, so it may be worth experimenting with combinations of them all to find a satisfactory balance.

[«Midnight»]

Thanks Michael,

That sheds some light on how the WAN Optimizer fits into the picture.

David