December 14, 2010 at 1:08 am
Now this days there is a trend which says that ORM generated SQL performance almost same as SP in mordern / new version of Databases. Because of the reason SP and Inline SQL performance almost same than old days. I have a big doubt in this.
So my question is that can we achieve the same performance using SP and inline SQL with new version of databases?
December 14, 2010 at 2:02 am
What a question! Really hard to give a sensible answer.
I think this one needs to broken into several points:
1) Query parsing / compilation
ORMs generate SQL code that has to be compiled, while stored procedures can benefit from cached plans.
Usually the dynamic statements generated by ORMs can be cached too.
I think this is what you mean with "same performance".
2) Query execution
Stored procedures are written (hopefully) by experienced DBA/Developers that can tune the queries to execute with the best possible plan, using the most efficient syntax and controlling directly the indexes usage.
ORMs generate semantically valid statements, that are not guaranteed to be the efficient.
3) Data extraction strategy
The real problem, IMHO, is that ORMs can lead developers to think that data comes out of the database magically.
Object-Oriented developers tend to write "elegant" code, reusing code as much as possible. Don't be surprised when you find code with loops with a data lookup inside. It can happen without using an ORM as well, but I think it tends to be a bigger problem when the actual data layer access is hidden from the developer.
On the other side, "traditional" DBAs and database developers know well the importance of avoiding loops and procedural code. At least they should.
4) Who holds control?
Performance is the result of combining many factors. When using stored procedures, you hold control on all of those factors.
With dynamic SQL and ORMs you don't.
I would reverse your initial statement into this: "Most modern ORMs can use stored procedures." Why not going for the best of both worlds?
Hope this helps,
Gianluca
-- Gianluca Sartori
December 14, 2010 at 3:04 am
Security should be also your concern.
If you're using stored procedures, the user accessing the database on behalf of the application only needs "exec" rights on the procedures.
If you're using inline SQL, you have to grant rights on each database object you need. Often developers assign the app user to the "db_owner" role or even they're using the "sa" login. Even if the inline SQL is properly parameterized, the database can be "hacked".
December 14, 2010 at 3:38 am
Hi Gianluca,
1st of all thanks for your nice reply.
1. Query parsing / compilation
So do you mean SP and cached Inline Query (Parametarized SQL of ORM using sp_executesql) performance wise same ?
2. Query execution
If you give me one example of query that might be difference in performance within SP and parameterized SQL by ORM.
3. Data extraction strategy
What do you mean by "data layer access is hidden from the developer" ?
"Most modern ORMs can use stored procedures" I agree. But I have a technical and non-technical view points if I use SP then am not going to use of ORM. Then I lost one of the fetures of ORM that less coding and work with multi database without concentrating on SQL query.
Standard OO patern coding can also write without using ORM. What is your openion !
Thanks,
Shubha
December 14, 2010 at 3:46 am
If I exclude the security reason. What will be your answer ? Performace means I want to say faster, optimized.
December 14, 2010 at 4:14 am
Shubhajyoti Ghosh (12/14/2010)
So do you mean SP and cached Inline Query (Parametarized SQL of ORM using sp_executesql) performance wise same?
Yes, as far as cache-hits is concerned. The same exact code executed in a stored procedure and executed with sp_executesql is cached in the same way and doesn't need to be compiled again.
If you give me one example of query that might be difference in performance within SP and parameterized SQL by ORM.
I can't give you a specific example, but, generally speaking, semantically equivalent statements can have very different performance.
Example:
-- SLOW
SELECT DISTINCT A.*
FROM sysobjects AS A
INNER JOIN syscolumns AS B
ON A.id = B.id
-- FAST
SELECT *
FROM sysobjects AS A
WHERE EXISTS (
SELECT 1
FROM syscolumns AS B
WHERE A.id = B.id
)
Will the ORM always pick the most efficient syntax? God knows.
What do you mean by "data layer access is hidden from the developer" ?
When using ORMs, the developer doesn't have to worry about the database layer, that's what the ORM thing is all about.
Not worrying often becomes not caring.
"Most modern ORMs can use stored procedures" I agree. But I have a technical and non-technical view points if I use SP then am not going to use of ORM. Then I lost one of the fetures of ORM that less coding and work with multi database without concentrating on SQL query.
You're right. There are also many tools around that can generate SP code for CRUD operations, leaving you the task to code just the complex business-logic procedures.
Standard OO patern coding can also write without using ORM. What is your openion !
I don't like ORMs, I prefer coding the data access layer by hand or using code generators. However, this is my personal and questionable opinion.
-- Gianluca Sartori
December 14, 2010 at 4:57 am
Hi Gianluca Sartori ,
Just excelent reply in a constructive way. Even I also not prefer "ORMs". Because adopting something new also a questinable point may be other factors also [technical or non-technical]
But looking into the current trend. I just want to clerify my doubts.
Yes, as far as cache-hits is concerned. The same exact code executed in a stored procedure and executed with sp_executesql is cached in the same way and doesn't need to be compiled again.
Very valuable points.
Will the ORM always pick the most efficient syntax? God knows.
Need to be find out. I have to check, if I find out any points on that.
Which tools you used for generate automated DAL?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply