Posted 03 Feb 2010
Link to this post
Well, as always in our business it depends.. :-)... I always answer performance questions with the question..: Is it fast enough for what you are trying to achieve?
We used OA for a large distributed system and haven't encountered any issues regarding performance yet..
Thankfully, OpenAccess is flexible as to querying data.. you can use OQL (an object oriented SQL like query language), LINQ, SP's or plain SQL and still get object instances out of the query.
You're right about when updating bunch of objects... you have to retrieve the objects, update the property, and persist it again... However, OA only opens a (write) database transaction during the commit, up until then you operate on objects in memory (once retrieved)... So changes to the objects will be written all at once (during the commit)..
I think doing custom SQL or SP's would always to faster, but you have to compare against other metrics as raw database performance..You get a programming model, that is closer to the "real world" in form of entities (instances of persistent classes) that you can query, manipulate and persist. Trivial database requests as getting a list of customers (against any criteria), a specifc customer, etc.. is done in a snap... you can say you are on a higher abstraction layer than if you were using raw SQL.
Anyway, OA has several ways of boosting performance regarding queries... in particular fetchplans... But like I said, we haven't seen any issues yet and we're even not using fetchplans extensively... We went back to the first line I wrote... It perform fast enough, so that's fine...the problem is defining what "fast enough"... is.. :-)
Hope it shed some light on your questions...I would give it a whirl if I were you... you could be as surprised as I was back when I started using OA (some 4+ years ago)