Difference Between Stored Procedure and Inline Quries
-
Hi all, What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query... What is the professional Approch.... I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled... plz guide me..... :) Thanx Sajjad
-
Hi all, What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query... What is the professional Approch.... I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled... plz guide me..... :) Thanx Sajjad
Stored procedures are stored in the database. It means that the database can optimise the query in advance of it being run - this is especially useful if the query is going to get run several times. An "Inline Query" (what I'd probably call an ad hoc query) is sent to the database as it is needed. This means that the database engine has to parse it, figure out an optimised plan and then run it (so it has more work to do). SQL Server 2000 onwards does some caching of these types of queries so the impact on performance is reduced, but it is still a good idea to create stored procedures. I blogged about this a while ago, so here is a link: Original Link[^] - cached version as my blog site appears to be down at the moment[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
Hi all, What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query... What is the professional Approch.... I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled... plz guide me..... :) Thanx Sajjad
This is a somewhat hotly debated issue among many of the developers I work with, however, the general concensus has been to use stored procedures rather than inline queries. You can get better performance from a stored procedure than an inline query because of how the optimizer handles inline queries vs. stored procedures; however, unless you are working on a high traffic application you will probably not notice the difference. The big difference tends to be with maintainability. As much as we would like our databases structures to be set in stone, they do tend to be somewhat fluid. Sometimes the database needs to be refactored because of performance, sometimes column names need to be changed, etc. Very often, queries need to be refactored for performance. In any event, when your queries are inline, it forces you to rebuild your application when the data layer changes. By using stored procedures, you are adding a layer of separation between your data base and your code. This helps insulate your code from database changes. Jim Conigliaro jconigliaro@ieee.org
-
This is a somewhat hotly debated issue among many of the developers I work with, however, the general concensus has been to use stored procedures rather than inline queries. You can get better performance from a stored procedure than an inline query because of how the optimizer handles inline queries vs. stored procedures; however, unless you are working on a high traffic application you will probably not notice the difference. The big difference tends to be with maintainability. As much as we would like our databases structures to be set in stone, they do tend to be somewhat fluid. Sometimes the database needs to be refactored because of performance, sometimes column names need to be changed, etc. Very often, queries need to be refactored for performance. In any event, when your queries are inline, it forces you to rebuild your application when the data layer changes. By using stored procedures, you are adding a layer of separation between your data base and your code. This helps insulate your code from database changes. Jim Conigliaro jconigliaro@ieee.org
Thanx for great replies :) Can u tell me .. what is the professional Approch.. Professional Developers use Inline Query (Ad hoc Query) or Stored Procedure. thanx once again Sajjad
-
Thanx for great replies :) Can u tell me .. what is the professional Approch.. Professional Developers use Inline Query (Ad hoc Query) or Stored Procedure. thanx once again Sajjad
It is a fuzzy area, which is why I hesitate to say that one is the "professional approach", so here are the guidelines my development teams use: - Unless you receive specific instructions to the contrary, always use stored procedures; it is the most efficient and most maintainable practice (and thus the most professional approach). - If your customer specifically requests that you do not use stored procedures (yes, some do have this constraint), then use inline queries. Because you are meeting your customer's constraints, this becomes the most professional approach. - If you strongly believe anyone (customer, hosting provider, etc.) will change the procedures, adversely affecting your system, use inline queries. Because you are guarding against probable down time, this would then be most professional approach. Jim Conigliaro jconigliaro@ieee.org -- modified at 7:16 Tuesday 23rd May, 2006
-
It is a fuzzy area, which is why I hesitate to say that one is the "professional approach", so here are the guidelines my development teams use: - Unless you receive specific instructions to the contrary, always use stored procedures; it is the most efficient and most maintainable practice (and thus the most professional approach). - If your customer specifically requests that you do not use stored procedures (yes, some do have this constraint), then use inline queries. Because you are meeting your customer's constraints, this becomes the most professional approach. - If you strongly believe anyone (customer, hosting provider, etc.) will change the procedures, adversely affecting your system, use inline queries. Because you are guarding against probable down time, this would then be most professional approach. Jim Conigliaro jconigliaro@ieee.org -- modified at 7:16 Tuesday 23rd May, 2006
thanx dude.... and if i used Access Database... so can access support the Stored Procedure
-
thanx dude.... and if i used Access Database... so can access support the Stored Procedure
It's been a while since I've worked with Access, at the time it did not support stored procedures. It was also an inherently dangerous database to use with a web based application because of the way it handles locks. Multiple people accessing the web application at the same time could be locked out. Jim Conigliaro jconigliaro@ieee.org