Is ORM faster?

[Kevin]

Hi,

I am new in ORM and planning to use it on my next project. Since i am already an user of Telerik, Open Access will be my first choice.

I understand ORM will do a mapping between database and the object/class. All the SQL query will did by the ORM itself. Question is should i not to use any SQL query or stored-procedures at all after i implemented ORM.
Lets say i have an Customer table in DB, when i need to update a bunch of record in that table, in ORM i need to query all the related records then loop each of it, make changes on the object then update them back to database. But if using SQL query or Stored-Procedure, this can achieve by a single query which i think it is faster. It does not need to get all the data from DB and map to object, then no need loop one by one to update, and then send back the updated data to DB again.

Please advise me on whether i should using T-SQL after implemented ORM and correct me if my thought on above situation is wrong.

Thanks.

[IT-Als]

Hi Kevin,

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)

Regards

Henrik

[Kevin]

Hi Henrik,

You are right. If the performance is not impact too much, i will prefer have a better programming modeling, more object oriented way to dealing with data. Thanks, your experiences in using OA really give me confidence towards ORM and OA.