Monday, May 26, 2008

Using stored procedure and inline sql in your application

Programmers always ask which is better and which should they choose. Is store procedure has better performance or inline sql with ORM is better at gaining productivity? Well depends who you are, what applications you develop, the answers are very different from person to person.

I use store procedure when I knew the query is complicated which requires multiple joins tables and I knew that i will query it very often, hence using store procedure will make a significant different in performance gains. For example, you run the optimized precompile sql statements. you delegate the computation of data within the sql server and minimize sql query back and forth and also you make your query pretty secured by using SP

Meanwhile using inline sql is not a good practise unless you are using Object Relational Mapping (ORM) tools to generate those inline sql for you and all you need is to use the objects created. It is the fastest and simple way to query and manipulate the database with the power of OOP. If you are using inline sql as a short cut way to query data, you gotta becareful and takes various precautions steps to overcome sql injection attacks. Sometimes a string of sql statement hard coded in your program can be a maintenance problem because it is quite difficult to test the validity of sql statement.

FYI, I use SP for complicated queries. I use inline sql (usually generated by ORM) for simple CRUD to direct display data that do not involve calculations. Query using ORM can be more complicated and requires more resources than using the SP, so just keep things simple and understandable.

I am not bias to one or another, I just want to point out that by using the right technology in your application will have a greater benefit of sticking to one technology. Tool is created for making us easier, if you found that the tool is great, just use it, otherwise get another alternative.

5 comments:

Admin said...

Great summary on the use or ORM and when to use stored procedures.

Anonymous said...

What necessary words... super, a magnificent phrase

Anonymous said...

It is rather valuable information

Anonymous said...

I congratulate, this remarkable idea is necessary just by the way


I suggest you to come on a site, with an information large quantity on a theme interesting you. For myself I have found a lot of the interesting. Hot Health

Anonymous said...

Good day!

The name's Dennis, though most people call me 'Freaky'. :P I'm in my "teens" and kinda lost in the world at the moment...however, I've found the hippie "culture" and discovered that it fits me very well...I'm not trying to fit into it, it just kind of fitted onto me.
I have lots of questions, which hopefully some of the older and wiser people on this board will be able to help answer.
Here's to hoping this board will be able to help me discover more of myself and fuel my beliefs.

Keep smiling, Dennis from [url=http://www.myonlinepayday.com]Personal Loans[/url] website!