|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Store procedure vs Direct statement ???Dear all,
Is there any restriction or rules when you must you Store procedure call within application code compare to direct SQL statement. For sure the first reason is data security as all querry are executed on the server. But is there some cases where store procedure should be avoid ? For my case if data security is a must, the first thing would be to always use strore procedure Any comments are welcome Regards Serge Serge,
While developing an application it is in my idea complete madness to start with the SP. Just my thought, Cor Show quote "serge calderara" <sergecalder***@discussions.microsoft.com> schreef in bericht news:8BD20597-8DD5-4B66-ABF1-5B8C58041549@microsoft.com... > Dear all, > > Is there any restriction or rules when you must you Store procedure call > within application code compare to direct SQL statement. > > For sure the first reason is data security as all querry are executed on > the > server. > > But is there some cases where store procedure should be avoid ? > For my case if data security is a must, the first thing would be to > always > use strore procedure > > Any comments are welcome > > Regards > Serge Hi Cor,
Why it is so madness according to you ? I browse many places over here, talking about sensitive data when use in a web application. First simple recommandation was to use store procedure for easy manitenance nad security issue. I did not mentionned that it is fully secure, but a starting point I would love to hear your opinion on that then !! Regards Serge Show quote "Cor Ligthert [MVP]" wrote: > Serge, > > While developing an application it is in my idea complete madness to start > with the SP. > > Just my thought, > > Cor > > "serge calderara" <sergecalder***@discussions.microsoft.com> schreef in > bericht news:8BD20597-8DD5-4B66-ABF1-5B8C58041549@microsoft.com... > > Dear all, > > > > Is there any restriction or rules when you must you Store procedure call > > within application code compare to direct SQL statement. > > > > For sure the first reason is data security as all querry are executed on > > the > > server. > > > > But is there some cases where store procedure should be avoid ? > > For my case if data security is a must, the first thing would be to > > always > > use strore procedure > > > > Any comments are welcome > > > > Regards > > Serge > > > serge,
Just that it is easier to do it dynamic. You don't have to contact your Admin. As it is ready the change to a SP is than simple. One character can cost a lot of time you know if you have to talk about it if that replacement for that ill Admin can do it for you. However just my thought, Cor Serge,
All sql is executed on the server, so stored procedures don't have any security advantage over dynamic sql in that instance. If you use parameters in your dynamic sql then you will gain the same security benefits as using a stored procedure in terms of sql injection. I think the decision is one of design philosopy as opposed to security or performance. Kerry Moorman Show quote "serge calderara" wrote: > Dear all, > > Is there any restriction or rules when you must you Store procedure call > within application code compare to direct SQL statement. > > For sure the first reason is data security as all querry are executed on the > server. > > But is there some cases where store procedure should be avoid ? > For my case if data security is a must, the first thing would be to always > use strore procedure > > Any comments are welcome > > Regards > Serge One big thing can be performance. If you have a huge query with lots of
joins, the Stored Procedure is going to execute quicker. Stored Procedures are compiled, whereas a query is first compiled and then executed. I understand Cor's opinion completely, I have had the same trouble. I try to stick to dynamic statements for update / delete / insert, and use Stored Procedures when returning big amounts of data. And yes, try to stick to using parameters. Hendrik,
I don't believe stored procedures are compiled in some way different than dynamic sql. Here is an article that discusses theses issues: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx Kerry Moorman Show quote "Hendrik" wrote: > One big thing can be performance. If you have a huge query with lots of > joins, the Stored Procedure is going to execute quicker. Stored > Procedures are compiled, whereas a query is first compiled and then > executed. > > I understand Cor's opinion completely, I have had the same trouble. I > try to stick to dynamic statements for update / delete / insert, and > use Stored Procedures when returning big amounts of data. And yes, try > to stick to using parameters. > > Hendrik wrote:
> One big thing can be performance. If you have a huge query with lots Only DB2 compiles procs and only procs written in non-SQL languages. > of joins, the Stored Procedure is going to execute quicker. Stored > Procedures are compiled, whereas a query is first compiled and then > executed. Performance of procs is only higher in data-processing procs where transfering the data back to the caller to do the processing is slower than the SQL statements processing the data. For CRUD it doesn't make a difference. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ Stored procedure do have a security advantage.
Let's say you have a stored procedure called InsertCustomer, that takes some parameters, and adds a customer. Ok, great. You can have a sql account that has access to this stored procedure, but nothing else. If you instead allow your app to do dynamic sql, then your sql account needs rights to the Customer table. As soon as someone gets access to your application and can alter the sql or something like that, they can now do anything to the Customer table that they want - like delete all customers, etc. So, in this sense a stored procedure is more secure. Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:1C52B90F-571C-4004-9DA7-0FCF8F6263ED@microsoft.com... > Serge, > > All sql is executed on the server, so stored procedures don't have any > security advantage over dynamic sql in that instance. > > If you use parameters in your dynamic sql then you will gain the same > security benefits as using a stored procedure in terms of sql injection. > > I think the decision is one of design philosopy as opposed to security or > performance. > > Kerry Moorman > > > "serge calderara" wrote: > >> Dear all, >> >> Is there any restriction or rules when you must you Store procedure call >> within application code compare to direct SQL statement. >> >> For sure the first reason is data security as all querry are executed on >> the >> server. >> >> But is there some cases where store procedure should be avoid ? >> For my case if data security is a must, the first thing would be to >> always >> use strore procedure >> >> Any comments are welcome >> >> Regards >> Serge Marina,
My comment to the OP was that stored procedures did not have a security advantage over dynamic sql because of executing on the server, since dynamic sql also executes on the server. In response to your example of stored procedures having a security advantage over dynamic sql because of the ability to control access, only granting the account insert permission on the Customer table would seem to have the same effect as only granting the account access to an InsertCustomer stored procedure. Or maybe I'm missing something? Kerry Moorman Show quote "Marina Levit [MVP]" wrote: > Stored procedure do have a security advantage. > > Let's say you have a stored procedure called InsertCustomer, that takes some > parameters, and adds a customer. Ok, great. You can have a sql account that > has access to this stored procedure, but nothing else. > > If you instead allow your app to do dynamic sql, then your sql account needs > rights to the Customer table. As soon as someone gets access to your > application and can alter the sql or something like that, they can now do > anything to the Customer table that they want - like delete all customers, > etc. > > So, in this sense a stored procedure is more secure. > > "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message > news:1C52B90F-571C-4004-9DA7-0FCF8F6263ED@microsoft.com... > > Serge, > > > > All sql is executed on the server, so stored procedures don't have any > > security advantage over dynamic sql in that instance. > > > > If you use parameters in your dynamic sql then you will gain the same > > security benefits as using a stored procedure in terms of sql injection. > > > > I think the decision is one of design philosopy as opposed to security or > > performance. > > > > Kerry Moorman > > > > > > "serge calderara" wrote: > > > >> Dear all, > >> > >> Is there any restriction or rules when you must you Store procedure call > >> within application code compare to direct SQL statement. > >> > >> For sure the first reason is data security as all querry are executed on > >> the > >> server. > >> > >> But is there some cases where store procedure should be avoid ? > >> For my case if data security is a must, the first thing would be to > >> always > >> use strore procedure > >> > >> Any comments are welcome > >> > >> Regards > >> Serge > > > On Wed, 29 Mar 2006 15:41:03 -0800, Kerry Moorman
<KerryMoor***@discussions.microsoft.com> wrote: Show quote >Marina, Yes, you are missing something.> >My comment to the OP was that stored procedures did not have a security >advantage over dynamic sql because of executing on the server, since dynamic >sql also executes on the server. > >In response to your example of stored procedures having a security advantage >over dynamic sql because of the ability to control access, only granting the >account insert permission on the Customer table would seem to have the same >effect as only granting the account access to an InsertCustomer stored >procedure. > >Or maybe I'm missing something? > >Kerry Moorman > > When you give a user access to a stored proc you give it only to the SP, not the table it accesses. When you give a user access to a table they have the ability to write their own SQL and access the table with it (perhaps with a query building tool such as the one in MS Access). When you give users permission to access tables you must be aware you have given them access not just from your application, but from any SQL they send to the server, from anywhere. The only thing a user can do with a SP (if they know the name of it) is execute it. You have controlled what the SP can do to your database. Power users at a site are notorious for cranking up Access, connecting to a database and using Access to get to data on the databases. If you have given one of those folks delete, update or insert permission on a table, watch out, they can change your data from outside your application. Even if they only have select permission they can cause you trouble. Suppose one of them does a select * from a ten million row table. I recon you might see a performance hit from something like that. I agree with the poster who said they don't like dealing with the admin, but in a sensible development environment you will have permission to create what ever you need on the DEV server. After development is complete and you go live the creation of database objects is a maintenance operation that can be developed and tested on the DEV server before going into production. In that environment the admin becomes a tester, tweaker and mover. Sorry about the long winded answer ;o) Good luck with your project, Otis Mukinfus http://www.arltex.com http://www.tomchilders.com Otis,
You answered a different question than the one I asked. I asked: "Only granting the account insert permission on the Customer table would seem to have the same effect as only granting the account access to an InsertCustomer stored procedure. Or maybe I'm missing something?" You answered: "Yes, you are missing something. .. .. .. If you have given one of those folks delete, update or insert permission on a table, watch out, they can change your data from outside your application." But my scenario stated that the only permission the user would have on the table is insert, just like they only have access to a stored procedure for insert. I still don't see where I am missing anything with regard to the specific scenario that I was addressing. Kerry Moorman Show quote "Otis Mukinfus" wrote: > On Wed, 29 Mar 2006 15:41:03 -0800, Kerry Moorman > <KerryMoor***@discussions.microsoft.com> wrote: > > >Marina, > > > >My comment to the OP was that stored procedures did not have a security > >advantage over dynamic sql because of executing on the server, since dynamic > >sql also executes on the server. > > > >In response to your example of stored procedures having a security advantage > >over dynamic sql because of the ability to control access, only granting the > >account insert permission on the Customer table would seem to have the same > >effect as only granting the account access to an InsertCustomer stored > >procedure. > > > >Or maybe I'm missing something? > > > >Kerry Moorman > > > > > Yes, you are missing something. > > When you give a user access to a stored proc you give it only to the SP, not the > table it accesses. When you give a user access to a table they have the ability > to write their own SQL and access the table with it (perhaps with a query > building tool such as the one in MS Access). > > When you give users permission to access tables you must be aware you have given > them access not just from your application, but from any SQL they send to the > server, from anywhere. > > The only thing a user can do with a SP (if they know the name of it) is execute > it. You have controlled what the SP can do to your database. > > Power users at a site are notorious for cranking up Access, connecting to a > database and using Access to get to data on the databases. If you have given > one of those folks delete, update or insert permission on a table, watch out, > they can change your data from outside your application. Even if they only have > select permission they can cause you trouble. Suppose one of them does a select > * from a ten million row table. I recon you might see a performance hit from > something like that. > > I agree with the poster who said they don't like dealing with the admin, but in > a sensible development environment you will have permission to create what ever > you need on the DEV server. After development is complete and you go live the > creation of database objects is a maintenance operation that can be developed > and tested on the DEV server before going into production. In that environment > the admin becomes a tester, tweaker and mover. > > Sorry about the long winded answer ;o) > Good luck with your project, > > Otis Mukinfus > http://www.arltex.com > http://www.tomchilders.com > On Thu, 30 Mar 2006 07:36:03 -0800, Kerry Moorman
<KerryMoor***@discussions.microsoft.com> wrote: Show quote >Otis, The difference is:> >You answered a different question than the one I asked. I asked: > >"Only granting the account insert permission on the Customer table would >seem to have the same effect as only granting the account access to an >InsertCustomer stored procedure. > >Or maybe I'm missing something?" > >You answered: > >"Yes, you are missing something. >. >. >. >If you have given one of those folks delete, update or insert permission on >a table, watch out, they can change your data from outside your application." > >But my scenario stated that the only permission the user would have on the >table is insert, just like they only have access to a stored procedure for >insert. > >I still don't see where I am missing anything with regard to the specific >scenario that I was addressing. > >Kerry Moorman > > > With a stored procedure you give the user insert permission when using the stored procedure, but by no other means. They can't do an insert from Access, etc. because they do not have permission to access the table in any way except through the sp. If you give them insert permission on the table they can insert data at any time from any place. You have no way of controlling when or what they insert. I suspect your viewpoint on this matter may be why you don't like going to the admin to get things done. Good luck with your project, Otis Mukinfus http://www.arltex.com http://www.tomchilders.com Otis Mukinfus wrote:
Show quote > On Thu, 30 Mar 2006 07:36:03 -0800, Kerry Moorman Define admin role and general role in your db and perhaps other roles> <KerryMoor***@discussions.microsoft.com> wrote: > > > Otis, > > > > You answered a different question than the one I asked. I asked: > > > > "Only granting the account insert permission on the Customer table > > would seem to have the same effect as only granting the account > > access to an InsertCustomer stored procedure. > > > > Or maybe I'm missing something?" > > > > You answered: > > > > "Yes, you are missing something. > > . > > . > > . > > If you have given one of those folks delete, update or insert > > permission on a table, watch out, they can change your data from > > outside your application." > > > > But my scenario stated that the only permission the user would have > > on the table is insert, just like they only have access to a stored > > procedure for insert. > > > > I still don't see where I am missing anything with regard to the > > specific scenario that I was addressing. > > > > Kerry Moorman > > > > > > > The difference is: > > With a stored procedure you give the user insert permission when > using the stored procedure, but by no other means. They can't do an > insert from Access, etc. because they do not have permission to > access the table in any way except through the sp. > > If you give them insert permission on the table they can insert data > at any time from any place. You have no way of controlling when or > what they insert. > > I suspect your viewpoint on this matter may be why you don't like > going to the admin to get things done. if you want more granuality give access rights to roles on your tables add users to the roles in question. allow users to connect to the db with the user id you provided. So, say my user isn't in the admin role, so I for example can only select on given tables. How is that going to allow me to insert anything in the tables? it doesn't. You, as well as other pro-proc-for-crud-people, completely ignore the fact that if I have a pr_InsertCustomer proc, what's the difference between me calling the proc or me executing an insert into table ... statement? Nothing. Frans -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ On Fri, 31 Mar 2006 00:43:22 -0800, "Frans Bouma [C# MVP]"
<perseus.usenetNOSPAM@xs4all.nl> wrote: > So, say my user isn't in the admin role, so I for example can only Ah, Frans, now you are beginning name calling ("pro-proc-for-crud-people"). I>select on given tables. How is that going to allow me to insert >anything in the tables? it doesn't. > > You, as well as other pro-proc-for-crud-people, completely ignore the >fact that if I have a pr_InsertCustomer proc, what's the difference >between me calling the proc or me executing an insert into table ... >statement? Nothing. > > Frans didn't think you were such a person. Too bad. Anyway this thread has run it's course. It's become like the 'what is best C# or Java?' discussions. Good luck with your project, Otis Mukinfus http://www.arltex.com http://www.tomchilders.com Otis Mukinfus wrote:
Show quote > On Fri, 31 Mar 2006 00:43:22 -0800, "Frans Bouma [C# MVP]" ah, nittpicking on a detail to avoid the real discussion, eh? ;). > <perseus.usenetNOSPAM@xs4all.nl> wrote: > > > So, say my user isn't in the admin role, so I for example can only > > select on given tables. How is that going to allow me to insert > > anything in the tables? it doesn't. > > > > You, as well as other pro-proc-for-crud-people, completely ignore > > the fact that if I have a pr_InsertCustomer proc, what's the > > difference between me calling the proc or me executing an insert > > into table ... statement? Nothing. > > > > Frans > > > Ah, Frans, now you are beginning name calling > ("pro-proc-for-crud-people"). I didn't think you were such a person. > Too bad. But, weren't you propagating procs for crud? That's what you were doing, IMHO. > Anyway this thread has run it's course. It's become like the 'what Looking at some replies in this thread, including yours, I don't think> is best C# or Java?' discussions. it's a discussion about 'which is better' between equivalent technologies, at least not according to some in this discussion (mind you: allthough I wrote that dreaded article 'Stored procedures are bad, m'kay?', I don't think it's that black and white: use what you think is best, however use the right argumentation why you use what you use. 'a proc is more secure' is a myth in 99% of the proc-using systems, simply because the procs don't accept tokens which aren't known to the human who wants to execute them through QA. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ Marina Levit [MVP] wrote:
> Stored procedure do have a security advantage. That's not going to work, the app accessing the proc (why else have a> > Let's say you have a stored procedure called InsertCustomer, that > takes some parameters, and adds a customer. Ok, great. You can have a > sql account that has access to this stored procedure, but nothing > else. proc?) still needs to know the account and has to connect to the db using that account. > No it's not. My app instead needs access to your insertcustomer and> If you instead allow your app to do dynamic sql, then your sql > account needs rights to the Customer table. As soon as someone gets > access to your application and can alter the sql or something like > that, they can now do anything to the Customer table that they want - > like delete all customers, etc. > > So, in this sense a stored procedure is more secure. deletecustomer proc. So I can then hack the connection string out and connect directly to the db and execute pr_DeleteCustomer by passing a random ID. pooff... What's the difference? nothing. The only way to do this properly is by passing in a token to every proc and check that token inside the DB. With manually access I don't know the token. However even that can be hacked out if I want to, as the token has to be passed by an application as well. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ You'll want to dig deep into how sql server processes
commands and what the sql provider for .net really does. The last half of this article will be a bit of an eye opener and give you a good start to research this more in the sql server documentation. The .net framework could was run using Lutz's reflector against a 1.1 assembly. You'll want to review the section that starts here. This down at the lowest level prior to the provider submitting the byte arrays to sql server. System.Data.SqlClient.TdsParser.TdsExecuteRPC http://www.eggheadcafe.com/articles/adonet_exec_stored_procedures_sqlhelper.asp -- Show quoteRobbe Morris - 2004-2006 Microsoft MVP C# Earn money answering .NET questions http://www.eggheadcafe.com/forums/merit.asp "serge calderara" <sergecalder***@discussions.microsoft.com> wrote in message news:8BD20597-8DD5-4B66-ABF1-5B8C58041549@microsoft.com... > Dear all, > > Is there any restriction or rules when you must you Store procedure call > within application code compare to direct SQL statement. > > For sure the first reason is data security as all querry are executed on > the > server. > > But is there some cases where store procedure should be avoid ? > For my case if data security is a must, the first thing would be to > always > use strore procedure > > Any comments are welcome > > Regards > Serge AT first , I was just asking a simple question.
I did not know that it raise so many missunderstanding and different point of view. But the good thing once again, is that we learn from experience of others. Why I come to this was the simple fact that I was in front of that situation when preparing my MCAD, and I have to say that I was quite embarassed to get a clear view of when using SP instaed of dynamic SQL. And most answer I get was in 2 words "Security" , "Performance". Then it seems that it is not the case according to some article point here and comments. Now in order to get a conclusion to my initial question, let say that you are in front of an audiance who would ask you to define rules or simple guide lines which might help to go to SP or Dynamic integration, what will be the answer then ? COmplexe querry = SP for performance ? SImple querry = dynamic ? serge Show quote "serge calderara" wrote: > Dear all, > > Is there any restriction or rules when you must you Store procedure call > within application code compare to direct SQL statement. > > For sure the first reason is data security as all querry are executed on the > server. > > But is there some cases where store procedure should be avoid ? > For my case if data security is a must, the first thing would be to always > use strore procedure > > Any comments are welcome > > Regards > Serge On Thu, 30 Mar 2006 11:40:02 -0800, serge calderara
<sergecalder***@discussions.microsoft.com> wrote: Show quote >AT first , I was just asking a simple question. Sere,>I did not know that it raise so many missunderstanding and different point >of view. >But the good thing once again, is that we learn from experience of others. > >Why I come to this was the simple fact that I was in front of that situation >when preparing my MCAD, and I have to say that I was quite embarassed to get >a clear view of when using SP instaed of dynamic SQL. And most answer I get >was in 2 words "Security" , "Performance". Then it seems that it is not the >case according to some article point here and comments. > >Now in order to get a conclusion to my initial question, let say that you >are in front of an audiance who would ask you to define rules or simple guide >lines which might help to go to SP or Dynamic integration, what will be the >answer then ? > > COmplexe querry = SP for performance ? > SImple querry = dynamic ? > >serge > >"serge calderara" wrote: > >> Dear all, >> >> Is there any restriction or rules when you must you Store procedure call >> within application code compare to direct SQL statement. >> >> For sure the first reason is data security as all querry are executed on the >> server. >> >> But is there some cases where store procedure should be avoid ? >> For my case if data security is a must, the first thing would be to always >> use strore procedure >> >> Any comments are welcome >> >> Regards >> Serge I agree with those who say that sp's do not necessarily improve performance. In these days of so many folks hell bent on breaching security, I design my data access for security. Good luck with your project, Otis Mukinfus http://www.arltex.com http://www.tomchilders.com serge,
As always, there is no easy answer. From my experience, it is best to restrict access, as much as possible to SP/UDF usage. With SQL 2005, we have the added benefit of CLR-based (C#, etc.) code directly on the SQL Server. Performance: SP/UDF usage CAN greatly improve performance, if coded well. They allow you to accept parameters and run small optimizations that are not possible with dynamically built queries passed from the client code (in speed, anyway.) The Execution Path of the SP/UDF is determined on first run and tweeked by the server thereafter for some automated speed adjustments. This means that a dynamic query has to rely on the server's first-guess only of the best path to run. A SP/UDF has the benefit of further improvements over time. Security: I also do a lot of security checking from within the SP/UDFs to verify that the user has the right to run the select/update/insert/delete command. Sometimes these commands are driven by data states or conditions which lend themselves well to SP/UDF integration. I have a highly distributed data application in the development channel right now that relies heavily on this method. With it, I am able to tweek out great performance gains and have tight control on security. Things that client-based queries just cannot deliver. Good luck on your project. Randy Show quote "serge calderara" wrote: > AT first , I was just asking a simple question. > I did not know that it raise so many missunderstanding and different point > of view. > But the good thing once again, is that we learn from experience of others. > > Why I come to this was the simple fact that I was in front of that situation > when preparing my MCAD, and I have to say that I was quite embarassed to get > a clear view of when using SP instaed of dynamic SQL. And most answer I get > was in 2 words "Security" , "Performance". Then it seems that it is not the > case according to some article point here and comments. > > Now in order to get a conclusion to my initial question, let say that you > are in front of an audiance who would ask you to define rules or simple guide > lines which might help to go to SP or Dynamic integration, what will be the > answer then ? > > COmplexe querry = SP for performance ? > SImple querry = dynamic ? > > serge > > "serge calderara" wrote: > > > Dear all, > > > > Is there any restriction or rules when you must you Store procedure call > > within application code compare to direct SQL statement. > > > > For sure the first reason is data security as all querry are executed on the > > server. > > > > But is there some cases where store procedure should be avoid ? > > For my case if data security is a must, the first thing would be to always > > use strore procedure > > > > Any comments are welcome > > > > Regards > > Serge Randy wrote:
> Performance: Is it really so? To my understanding, SQL Server caches execution plan for> SP/UDF usage CAN greatly improve performance, if coded well. They allow you > to accept parameters and run small optimizations that are not possible with > dynamically built queries passed from the client code (in speed, anyway.) > The Execution Path of the SP/UDF is determined on first run and tweeked by > the server thereafter for some automated speed adjustments. This means that > a dynamic query has to rely on the server's first-guess only of the best path > to run. any query, whether it is encapsulated in stored procedure or is just in-place. It's true that procedures get cached (true they can go through a recompile
for a large number of reasons as well, which is a performance hit). Probably the best help here is to take a look at the execution plan for dynamic sql versus the execution plan for a stored procedure. A stored procedure generates an actual execution plan, but dynamic sql doesn't (at least last time I checked it didn't --- checking from within query analyzer) or at least it is very basic. Stored procedures are simply faster than ad-hoc queries. For example, how do you cache a query plan that is dynamic? Which index is it going to use? Would it be better to look at the heap instead (in other words, run a table scan). Remember, things like the order of the columns in a query, how the order by clause is constructed and what criteria you are using all effect performance of a query. It's usually better to have a sql guy develop the sql code and have programmers develop the ui, in this way the sql code can be tuned appropriately. This is a big advantage for the SQL Server model. However, having said that, if you are building a small application and aren't concerned with performance, and you don't want the overhead that comes with managing your stored procedures (permissions, performance tuning, etc...), then just use ad-hoc queries. At any rate, that has been my experience. You can use SQL profiler to answer almost all the questions that have been presented in this discussion thus far. Hope this helps, Bob Show quote "Sericinus hunter" wrote: > Randy wrote: > > > Performance: > > SP/UDF usage CAN greatly improve performance, if coded well. They allow you > > to accept parameters and run small optimizations that are not possible with > > dynamically built queries passed from the client code (in speed, anyway.) > > The Execution Path of the SP/UDF is determined on first run and tweeked by > > the server thereafter for some automated speed adjustments. This means that > > a dynamic query has to rely on the server's first-guess only of the best path > > to run. > > Is it really so? To my understanding, SQL Server caches execution plan for > any query, whether it is encapsulated in stored procedure or is just in-place. > BobD wrote:
> It's true that procedures get cached (true they can go through a recompile The same is true for ad-hoc queries.> for a large number of reasons as well, which is a performance hit). > Probably the best help here is to take a look at the execution plan for I don't understand this. Can you elaborate? Any query needs an execution> dynamic sql versus the execution plan for a stored procedure. A stored > procedure generates an actual execution plan, but dynamic sql doesn't (at > least last time I checked it didn't --- checking from within query analyzer) > or at least it is very basic. plan in order to be executed. Why would this plan be different for ad-hoc query? > Stored procedures are simply faster than ad-hoc queries. Why? Both are just chunks of SQL code.> For example, how do you cache a query plan that is dynamic? What is dynamic query plan? Query gets compiled, execution plan getscached, then executed. I see no problems with this. > Which index is it going to use? Would This is all true, but not relevant. If you type in a similar query which only> it be better to look at the heap instead (in other words, run a table scan). > Remember, things like the order of the columns in a query, how the order by > clause is constructed and what criteria you are using all effect performance > of a query. differs by, say, ORDER BY clause, it is a different query and it will cause a new plan be generated and cached. But if you only change parameter values, the SQL Server will reuse already existing plan. The only thing I can imagine in favor of stored procedures is that when the SQL Server is looking for a cached plan, it uses the procedure name, while for ad-hoc query it needs to parse the query and calculate some sort of a key to use for available plan lookup. This might be faster, but I don't know how the engine works in this respect. > It's usually better to have a sql guy develop the sql code and I am not arguing that. In fact, I am for stored procedures with both hands.> have programmers develop the ui, in this way the sql code can be tuned > appropriately. This is a big advantage for the SQL Server model. However, this argument about ad-hoc queries not being cached became so popular, that I just wanted to clear this up. Hi there,
If you get a chance, try some of the things I suggested. I recognize your point of view, but before you refute the point, please do actually check out what I said. Try opening up query analyzer and checking out the query plan or using profiler to capture it that way. There's a ton of information on how to use these tools to answer the questions you are debating, just take the time to look into it and I think you'll find the answers you are looking for. Here's some help on using profiler to that end: http://www.sql-server-performance.com/rd_query_tuning.asp Also, here is a quote from sql-server-performance.com: "One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications. " http://www.sql-server-performance.com/stored_procedures.asp Another quote from the same website: "As you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them." If you work with large databases, you'll begin to see the advantages of stored procedures. Just my humble opinion, take it for what it's worth. Cheers mate, Bob Show quote "Sericinus hunter" wrote: > BobD wrote: > > It's true that procedures get cached (true they can go through a recompile > > for a large number of reasons as well, which is a performance hit). > > The same is true for ad-hoc queries. > > > Probably the best help here is to take a look at the execution plan for > > dynamic sql versus the execution plan for a stored procedure. A stored > > procedure generates an actual execution plan, but dynamic sql doesn't (at > > least last time I checked it didn't --- checking from within query analyzer) > > or at least it is very basic. > > I don't understand this. Can you elaborate? Any query needs an execution > plan in order to be executed. Why would this plan be different for ad-hoc > query? > > > Stored procedures are simply faster than ad-hoc queries. > > Why? Both are just chunks of SQL code. > > > For example, how do you cache a query plan that is dynamic? > > What is dynamic query plan? Query gets compiled, execution plan gets > cached, then executed. I see no problems with this. > > > Which index is it going to use? Would > > it be better to look at the heap instead (in other words, run a table scan). > > Remember, things like the order of the columns in a query, how the order by > > clause is constructed and what criteria you are using all effect performance > > of a query. > > This is all true, but not relevant. If you type in a similar query which only > differs by, say, ORDER BY clause, it is a different query and it will cause a new > plan be generated and cached. But if you only change parameter values, the SQL > Server will reuse already existing plan. > The only thing I can imagine in favor of stored procedures is that when the > SQL Server is looking for a cached plan, it uses the procedure name, while > for ad-hoc query it needs to parse the query and calculate some sort of a key > to use for available plan lookup. This might be faster, but I don't know > how the engine works in this respect. > > > It's usually better to have a sql guy develop the sql code and > > have programmers develop the ui, in this way the sql code can be tuned > > appropriately. This is a big advantage for the SQL Server model. > > I am not arguing that. In fact, I am for stored procedures with both hands. > However, this argument about ad-hoc queries not being cached became so popular, > that I just wanted to clear this up. > BobD wrote:
> Hi there, I have a procedure with two selects in it, the second being from> > If you get a chance, try some of the things I suggested. I recognize your > point of view, but before you refute the point, please do actually check out > what I said. Try opening up query analyzer and checking out the query plan or > using profiler to capture it that way. table type UDF which in turn is one select from a join of six tables. I have executed this procedure and then cut-and-paste its code in a separate window, including the code for the UDF, executed this one too. The execution plans for both are identical, except the second one is divided into individual SELECT nodes, while the first one is under the single EXECUTE node. Is it what you suggested me try? > Also, here is a quote from sql-server-performance.com: "One of the biggest No doubt about this one.> advantages of using stored procedures over not using stored procedures is the > ability to significantly reduce network traffic. [...] > Another quote from the same website: "As you know, one of the biggest And this is at least debatable. There are a lot of places on the net> reasons to use stored procedures instead of ad-hoc queries is the performance > gained by using them." with long threads of the holy war on the subject. Show quote > If you work with large databases, you'll begin to see the advantages of > stored procedures. Just my humble opinion, take it for what it's worth. > > Cheers mate, > Bob This debate has gone on for years. The SQL Server team at MS has done what it can to make ad-hoc queries perform better and yes, they compile to the same execution plans as SPs. Those plans (the compiled code if you will) are cached in the same memory as SP plans. Yes, there are aspects of SPs that make them more efficient as I outline below. However, the real difference is not always in execution performance but in developer productivity. My research has found that the vast majority (over 90%) of the shops out there use SPs over ad-hoc queries. They use them because:
a.. SPs can be changed insitu without recoding, retesting and redeploying an application. b.. The require discipline within the organization to ensure that changes made to the database and its schema are done in an orderly fashion. c.. They are more secure as DBAs rarely permit direct access to base tables but do grant limited rights to SPs. d.. They encourage developers to use Command object Parameter lists that deal with a litany of security and formatting issues. e.. They find that OUTPUT parameters are far more efficient than rowsets (returned by a SELECT). f.. They find that now that SPs can be written in VB.NET or C# some very tough jobs can be done on the server instead of on the client. This saves transporting data in bulk to the client for this processing--again improving performance. I could go on (and I do in my books), but you get the idea. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "BobD" <B***@discussions.microsoft.com> wrote in message news:ADD2EEEB-6E58-44C4-8F56-9A002117B664@microsoft.com... > Hi there, > > If you get a chance, try some of the things I suggested. I recognize your > point of view, but before you refute the point, please do actually check out > what I said. Try opening up query analyzer and checking out the query plan or > using profiler to capture it that way. There's a ton of information on how to > use these tools to answer the questions you are debating, just take the time > to look into it and I think you'll find the answers you are looking for. > > Here's some help on using profiler to that end: > http://www.sql-server-performance.com/rd_query_tuning.asp > > Also, here is a quote from sql-server-performance.com: "One of the biggest > advantages of using stored procedures over not using stored procedures is the > ability to significantly reduce network traffic. And the more network traffic > that can be reduced, the better the overall performance of your SQL > Server-based applications. > " > http://www.sql-server-performance.com/stored_procedures.asp > > Another quote from the same website: "As you know, one of the biggest > reasons to use stored procedures instead of ad-hoc queries is the performance > gained by using them." > > If you work with large databases, you'll begin to see the advantages of > stored procedures. Just my humble opinion, take it for what it's worth. > > Cheers mate, > Bob > > > "Sericinus hunter" wrote: > >> BobD wrote: >> > It's true that procedures get cached (true they can go through a recompile >> > for a large number of reasons as well, which is a performance hit). >> >> The same is true for ad-hoc queries. >> >> > Probably the best help here is to take a look at the execution plan for >> > dynamic sql versus the execution plan for a stored procedure. A stored >> > procedure generates an actual execution plan, but dynamic sql doesn't (at >> > least last time I checked it didn't --- checking from within query analyzer) >> > or at least it is very basic. >> >> I don't understand this. Can you elaborate? Any query needs an execution >> plan in order to be executed. Why would this plan be different for ad-hoc >> query? >> >> > Stored procedures are simply faster than ad-hoc queries. >> >> Why? Both are just chunks of SQL code. >> >> > For example, how do you cache a query plan that is dynamic? >> >> What is dynamic query plan? Query gets compiled, execution plan gets >> cached, then executed. I see no problems with this. >> >> > Which index is it going to use? Would >> > it be better to look at the heap instead (in other words, run a table scan). >> > Remember, things like the order of the columns in a query, how the order by >> > clause is constructed and what criteria you are using all effect performance >> > of a query. >> >> This is all true, but not relevant. If you type in a similar query which only >> differs by, say, ORDER BY clause, it is a different query and it will cause a new >> plan be generated and cached. But if you only change parameter values, the SQL >> Server will reuse already existing plan. >> The only thing I can imagine in favor of stored procedures is that when the >> SQL Server is looking for a cached plan, it uses the procedure name, while >> for ad-hoc query it needs to parse the query and calculate some sort of a key >> to use for available plan lookup. This might be faster, but I don't know >> how the engine works in this respect. >> >> > It's usually better to have a sql guy develop the sql code and >> > have programmers develop the ui, in this way the sql code can be tuned >> > appropriately. This is a big advantage for the SQL Server model. >> >> I am not arguing that. In fact, I am for stored procedures with both hands. >> However, this argument about ad-hoc queries not being cached became so popular, >> that I just wanted to clear this up. >> William (Bill) Vaughn wrote:
> This debate has gone on for years. The SQL Server team at MS has done depends on how you produce your ad-hoc queries of course. Any o/r> what it can to make ad-hoc queries perform better and yes, they > compile to the same execution plans as SPs. Those plans (the compiled > code if you will) are cached in the same memory as SP plans. Yes, > there are aspects of SPs that make them more efficient as I outline > below. However, the real difference is not always in execution > performance but in developer productivity. My research has found that > the vast majority (over 90%) of the shops out there use SPs over > ad-hoc queries. mapper can produce queries which often outperform CRUD procs and also offer big development speed gains over hand-written procs for each table. > They use them because: this is specific to the situation they're used in. Often procs can't> a.. SPs can be changed insitu without recoding, retesting and > redeploying an application. be changed without altering the calling code as well, which means changes at multiple places, and worse: done by different people not working together (in a lot of cases), which often means delays, politics and who controls what (is proc signature change leading calling code changes or vice versa?). Furthermore, altering a proc is often argued as a good thing and an advantage but in reality this is a risky business: a proc can be called from a lot of routines in your application. So altering the proc is often not possible, so a NEW proc is required. This adds maintenance nightmares because you then have to document which parts of the application calls which proc (if there are more apps using the same procs, it's even more trickier). > b.. The require discipline within the organization to ensure that what has this to do with stored procedures? > changes made to the database and its schema are done in an orderly > fashion. > c.. They are more secure as DBAs rarely permit direct yeah, pr_DeleteCustomer(@customerID) is really more secure... when do> access to base tables but do grant limited rights to SPs. people stop bringing up this myth over and over again? If Joe uses an application X and X logs into the DB using Joe's account (or whatever account build in, doesn't matter) and X offers Joe to delete a customer in the scope of the application, Joe can delete the customer (or any customer) from the db using the proc called by X via a normal query toolkit and the same user as X uses, as X simply calls a proc. Joe can do: EXEC pr_DeleteCustomer(10) and customer with id 10 is removed. How is pr_DeleteCustomer prevent Joe from doing that? > d.. They encourage developers to use Command object Parameter lists You think?> that deal with a litany of security and formatting issues. I do using(DataAccessAdapter adapter = new DataAccessAdapter()) { adapter.SaveEntity(myCustomer); } and it saves myCustomer, its orders, each order detail row, related product row and other rows in the right order, syncs FK's etc. etc. With Dyn. sql. Where are the parameters? I don't see them. And the code above is database agnostic code. I can create a simple factory for my DataAccessAdapter object which makes the code above save the whole graph in sqlserver or oracle or other db. And on top of that: with insert and update statements (which one applies) which can often be faster than a proc call, simply because they only update / insert the fields which have to be updated. So, let's say your customer table has 10 fields, your order table has 10 fields. Your update procs for customer and order then either accept ALL field values and simply update all fields, OR accept nullable parameters and do COALESCE voodoo, OR contain if statements (a no-go, but some people still write them) Now tell me, can you beat those 4 lines of code (2 actually) with your procs and call-code behind the scenes, fully debugged and tested? Is that more productive? Then I didn't even throw in automatic concurrency code added to the query, when necessary. Besides that, how are you going to call procs without parameter lists? > e.. They find sadly for every context in the application where a value from the db> that OUTPUT parameters are far more efficient than rowsets (returned > by a SELECT). is required, a new scalar or output parameter proc has to be written to feed the app with data required. With an application of say 200 tables or more, this will likely run into the 1000+ procs (with the crud ones, the select all/one/filter ones). I'm sure you will agree with me that if you have a proc tier with 1000+ procs maintained by group A and the actual application maintained by group B, it will get messy over time. > f.. They find that now that SPs can be written in And finally we come to the one point where procs are the best option:> VB.NET or C# some very tough jobs can be done on the server instead > of on the client. This saves transporting data in bulk to the client > for this processing--again improving performance. I could go on (and > I do in my books), but you get the idea. data processing in long running queries. What I always find funny is that in debates between dyn. sql and procs this one point isn't used by the pro-proc (let me call the group favoring procs in the debate the 'pro proc people') people, or at least later on in the discussion (if ever). And data-processing is one of the reasons why you could opt for a proc for a given piece of functionality. (for the readers who wonder why: the roundtrips of transfering data back/forth to the client app to do processing there is often slower than doing the processing in interpreted (!) SQL code. With C#/VB.NET code on the server, it's even more clear that long-running processing code can help). One caveat: C#/VB.NET code maintenance on the server isn't that friendly. Bill, what I find weird in your plea, if I may call it that, is that you apparently ignore the downsides of procs which can hurt productivity and maintainability of an application that much that procs are not that good of a choice for CRUD operations. I especially make that distinction: CRUD vs dataprocessing, because it makes the discussion more clearer: most of these debates go about CRUD, not dataprocessing. With CRUD procs, you run into problems when your app needs these kind of things: - fetch n rows of X filtered on related tables Y and Z using parameters A, B ... - fetch n rows of X, all these n rows' related Y and Z rows - fetch rows of X, filter them and order them on field a AND then: - fetch rows of X, filter them and order them on field b etc. of course you can write special procs for these situations, but with a database of a regular enterprise size, you quickly run into the problem that you have to write a lot of custom procs. The reason for that is that it highly depends on the application's context in which the data is required how the actual resultset looks like and on which set logic it is based. This thus leads to DBA <-> developer discussions, which is overhead, can lead to conflicts (often does) and thus delays. Coming back to 'developer productivity' which was your main point at the start of your post, I don't see it. On the contrary, it's not there, it takes longer, EVEN with hand-written queries in code. Anyone who has done development of n-tier apps which call procs (I have) knows that if your app needs a change and the change requires different data, the proc-change is a roadblock: you either have to maintain a proc AND call code yourself (if you may alter the procs) OR have to request a proc change or a new proc, if the proc can't be changed. I've to add that I also don't think hand-writing raw SQL in your application is a good way to do data-access, however today that's also not necessary anymore. With the large amount of good, free (or commercial) o/r mapping frameworks out there, for every development team there's a solution available which fits their budget and frees them from writing raw SQL in their apps AND gives them full development productivity: they don't have to wait for the DBA to write the proc they want. They don't have to debate with the DBA for hours to convince him/her that the proc is necessary, they don't have to wait till the proc is updated to bring the call code to the proc in production and they don't have to wait till the DBA has tested the proc code through and through: their SQL _is_ tested. DBA's are necessary, also with o/r mapper using teams. Though instead working against them (in a lot of organisations the DBA is placed in another section of the organisation as the development team(s)) they should work together, after all they work for the same organisation. The DBA should consult the team when to use a join-based query over relationships and when to use a subquery for example (most o/r mappers out there (except dlinq and a few others) offer choices what to create: a subquery or a join, for example in 1:n fetches with filters on the n side). I don't have the illusion that the 'use procs because <insert myth list here>' arguments will go away soon or even will go away in the future. What I do hope though is that people are more realistic about the various options they have when it comes to data-access: by using the real pro/con's of each option and use the techniques available per functionality aspect. I think that debate is much more healthier AND more valuable to developers than the bickering over which side is right and which side is wrong. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ Frans Bouma [C# MVP] wrote:
[...] Thanks, Frans. Excellent post, very solid and balanced point of view. Frans,
I am full expactation waiting on the answer from Bill on this. (You know that I am in this on your side so I can not do that). However I think that it is time that this discussion is once well done. Cor Show quote "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> schreef in bericht news:xn0ekytla5ukpc002@news.microsoft.com... > William (Bill) Vaughn wrote: > >> This debate has gone on for years. The SQL Server team at MS has done >> what it can to make ad-hoc queries perform better and yes, they >> compile to the same execution plans as SPs. Those plans (the compiled >> code if you will) are cached in the same memory as SP plans. Yes, >> there are aspects of SPs that make them more efficient as I outline >> below. However, the real difference is not always in execution >> performance but in developer productivity. My research has found that >> the vast majority (over 90%) of the shops out there use SPs over >> ad-hoc queries. > > depends on how you produce your ad-hoc queries of course. Any o/r > mapper can produce queries which often outperform CRUD procs and also > offer big development speed gains over hand-written procs for each > table. > >> They use them because: >> a.. SPs can be changed insitu without recoding, retesting and >> redeploying an application. > > this is specific to the situation they're used in. Often procs can't > be changed without altering the calling code as well, which means > changes at multiple places, and worse: done by different people not > working together (in a lot of cases), which often means delays, > politics and who controls what (is proc signature change leading > calling code changes or vice versa?). > > Furthermore, altering a proc is often argued as a good thing and an > advantage but in reality this is a risky business: a proc can be called > from a lot of routines in your application. So altering the proc is > often not possible, so a NEW proc is required. This adds maintenance > nightmares because you then have to document which parts of the > application calls which proc (if there are more apps using the same > procs, it's even more trickier). > >> b.. The require discipline within the organization to ensure that >> changes made to the database and its schema are done in an orderly >> fashion. > > what has this to do with stored procedures? > >> c.. They are more secure as DBAs rarely permit direct >> access to base tables but do grant limited rights to SPs. > > yeah, pr_DeleteCustomer(@customerID) is really more secure... when do > people stop bringing up this myth over and over again? If Joe uses an > application X and X logs into the DB using Joe's account (or whatever > account build in, doesn't matter) and X offers Joe to delete a customer > in the scope of the application, Joe can delete the customer (or any > customer) from the db using the proc called by X via a normal query > toolkit and the same user as X uses, as X simply calls a proc. Joe can > do: > EXEC pr_DeleteCustomer(10) > and customer with id 10 is removed. How is pr_DeleteCustomer prevent > Joe from doing that? > >> d.. They encourage developers to use Command object Parameter lists >> that deal with a litany of security and formatting issues. > > You think? > > I do > using(DataAccessAdapter adapter = new DataAccessAdapter()) > { > adapter.SaveEntity(myCustomer); > } > > and it saves myCustomer, its orders, each order detail row, related > product row and other rows in the right order, syncs FK's etc. etc. > With Dyn. sql. Where are the parameters? I don't see them. And the code > above is database agnostic code. I can create a simple factory for my > DataAccessAdapter object which makes the code above save the whole > graph in sqlserver or oracle or other db. > > And on top of that: with insert and update statements (which one > applies) which can often be faster than a proc call, simply because > they only update / insert the fields which have to be updated. So, > let's say your customer table has 10 fields, your order table has 10 > fields. Your update procs for customer and order then either accept ALL > field values and simply update all fields, OR accept nullable > parameters and do COALESCE voodoo, OR contain if statements (a no-go, > but some people still write them) > > Now tell me, can you beat those 4 lines of code (2 actually) with your > procs and call-code behind the scenes, fully debugged and tested? Is > that more productive? > > Then I didn't even throw in automatic concurrency code added to the > query, when necessary. > > Besides that, how are you going to call procs without parameter lists? > >> e.. They find >> that OUTPUT parameters are far more efficient than rowsets (returned >> by a SELECT). > > sadly for every context in the application where a value from the db > is required, a new scalar or output parameter proc has to be written to > feed the app with data required. > > With an application of say 200 tables or more, this will likely run > into the 1000+ procs (with the crud ones, the select all/one/filter > ones). I'm sure you will agree with me that if you have a proc tier > with 1000+ procs maintained by group A and the actual application > maintained by group B, it will get messy over time. > >> f.. They find that now that SPs can be written in >> VB.NET or C# some very tough jobs can be done on the server instead >> of on the client. This saves transporting data in bulk to the client >> for this processing--again improving performance. I could go on (and >> I do in my books), but you get the idea. > > And finally we come to the one point where procs are the best option: > data processing in long running queries. > > What I always find funny is that in debates between dyn. sql and procs > this one point isn't used by the pro-proc (let me call the group > favoring procs in the debate the 'pro proc people') people, or at least > later on in the discussion (if ever). And data-processing is one of the > reasons why you could opt for a proc for a given piece of > functionality. (for the readers who wonder why: the roundtrips of > transfering data back/forth to the client app to do processing there is > often slower than doing the processing in interpreted (!) SQL code. > With C#/VB.NET code on the server, it's even more clear that > long-running processing code can help). One caveat: C#/VB.NET code > maintenance on the server isn't that friendly. > > Bill, what I find weird in your plea, if I may call it that, is that > you apparently ignore the downsides of procs which can hurt > productivity and maintainability of an application that much that procs > are not that good of a choice for CRUD operations. I especially make > that distinction: CRUD vs dataprocessing, because it makes the > discussion more clearer: most of these debates go about CRUD, not > dataprocessing. > > With CRUD procs, you run into problems when your app needs these kind > of things: > - fetch n rows of X filtered on related tables Y and Z using parameters > A, B ... > - fetch n rows of X, all these n rows' related Y and Z rows > - fetch rows of X, filter them and order them on field a > AND then: > - fetch rows of X, filter them and order them on field b > etc. > > of course you can write special procs for these situations, but with a > database of a regular enterprise size, you quickly run into the problem > that you have to write a lot of custom procs. The reason for that is > that it highly depends on the application's context in which the data > is required how the actual resultset looks like and on which set logic > it is based. This thus leads to DBA <-> developer discussions, which is > overhead, can lead to conflicts (often does) and thus delays. > > Coming back to 'developer productivity' which was your main point at > the start of your post, I don't see it. On the contrary, it's not > there, it takes longer, EVEN with hand-written queries in code. Anyone > who has done development of n-tier apps which call procs (I have) knows > that if your app needs a change and the change requires different data, > the proc-change is a roadblock: you either have to maintain a proc AND > call code yourself (if you may alter the procs) OR have to request a > proc change or a new proc, if the proc can't be changed. > > I've to add that I also don't think hand-writing raw SQL in your > application is a good way to do data-access, however today that's also > not necessary anymore. With the large amount of good, free (or > commercial) o/r mapping frameworks out there, for every development > team there's a solution available which fits their budget and frees > them from writing raw SQL in their apps AND gives them full development > productivity: they don't have to wait for the DBA to write the proc > they want. They don't have to debate with the DBA for hours to convince > him/her that the proc is necessary, they don't have to wait till the > proc is updated to bring the call code to the proc in production and > they don't have to wait till the DBA has tested the proc code through > and through: their SQL _is_ tested. > > DBA's are necessary, also with o/r mapper using teams. Though instead > working against them (in a lot of organisations the DBA is placed in > another section of the organisation as the development team(s)) they > should work together, after all they work for the same organisation. > The DBA should consult the team when to use a join-based query over > relationships and when to use a subquery for example (most o/r mappers > out there (except dlinq and a few others) offer choices what to create: > a subquery or a join, for example in 1:n fetches with filters on the n > side). > > I don't have the illusion that the 'use procs because <insert myth > list here>' arguments will go away soon or even will go away in the > future. What I do hope though is that people are more realistic about > the various options they have when it comes to data-access: by using > the real pro/con's of each option and use the techniques available per > functionality aspect. I think that debate is much more healthier AND > more valuable to developers than the bickering over which side is right > and which side is wrong. > > FB > > -- > ------------------------------------------------------------------------ > Lead developer of LLBLGen Pro, the productive O/R mapper for .NET > LLBLGen Pro website: http://www.llblgen.com > My .NET blog: http://weblogs.asp.net/fbouma > Microsoft MVP (C#) > ------------------------------------------------------------------------ Cor Ligthert [MVP] wrote:
> Frans, The sad thing is: there shouldn't be any dispute over what's to be> > I am full expactation waiting on the answer from Bill on this. > > (You know that I am in this on your side so I can not do that). > > However I think that it is time that this discussion is once well > done. used. I mean: it's as silly as 'use VB.NET!' 'NO!! Use C#' "No idiots! Use Java!!!111".... If it was 1996 today, and you weren't in posession of an expensive toplink license, then yes, Bill would have a good point, as the tooling to do dyn. sql without even writing a single SQL statement wouldn't have been available. However the points of the past don't apply anymore. > Cor FB-- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ Frans Bouma [C# MVP] wrote:
Show quote > Cor Ligthert [MVP] wrote: It is not about that. It is about the fact that very wrong arguments> >> Frans, >> >> I am full expactation waiting on the answer from Bill on this. >> >> (You know that I am in this on your side so I can not do that). >> >> However I think that it is time that this discussion is once well >> done. > > The sad thing is: there shouldn't be any dispute over what's to be > used. I mean: it's as silly as 'use VB.NET!' 'NO!! Use C#' "No idiots! > Use Java!!!111".... are so widely spread that people simply take them as axioms and don't allow even a slightest doubt about it. As to what to use, every specific situation will dictate the way to go. One just need to understand the implications, and discussions like this are very useful in this respect. Sericinus hunter wrote:
Show quote > Frans Bouma [C# MVP] wrote: True, though in a way I can understand some of those arguments,> > Cor Ligthert [MVP] wrote: > > > >> Frans, > > > > >> I am full expactation waiting on the answer from Bill on this. > > > > >> (You know that I am in this on your side so I can not do that). > > > > >> However I think that it is time that this discussion is once well > >> done. > > > > The sad thing is: there shouldn't be any dispute over what's to be > > used. I mean: it's as silly as 'use VB.NET!' 'NO!! Use C#' "No > > idiots! Use Java!!!111".... > > It is not about that. It is about the fact that very wrong > arguments are so widely spread that people simply take them as axioms > and don't allow even a slightest doubt about it. because they weren't always false, however today most of them are a little dated because of the excessive progress made in tooling since the past decade or so. I mean, years ago, procs were compiled though today they're not (at least not in most modern databases, if you write your procs in C or other non-SQL language in DB2, you get a compilation), for example. > As to what to use, every specific situation will dictate the way ... up till the point when the bitterness enters the conversation and> to go. One just need to understand the implications, and discussions > like this are very useful in this respect. the discussion becomes nasty. But I agree with you that it depends on the situation and with the aspects of the situation you should choose the way you want to work with data though do it based on reality-checked facts, not myths. I'm pessimistic about if we'll ever get there. What I wrote in my other posting about politics is IMHO the core reason why this problem is still a problem in a lot of organisations. The DBAs are in one part of the organisation, the developers in another part. There's often a lot of distrust towards the other group and of course, it's understandable that if you take away from the DBAs the responsibility of writing procs and make them 'system administrators', it will make them angry or at least it would be logical if they get upset. However that doesn't have to be the case. If the organisations make the DBA part of the development team, make the DBA the consultant for the developers what to do best and make them work closely together, it might be that what you describe perfectly is indeed within reach and these discussions can be a thing of the past. A thing that's often forgotten for example is the role of the DBA in writing functions instead of procs. This is a .NET oriented newsgroup, so people here work in an OO environment. If the DBA is working together with the development team (as a real teammember) and writes functions to do longrunning filters, the functions then can be used by the developers in O/R mapping scenarios (I use the term o/r mapping, but any OO <-> db layer will do) and you'll have the best of both worlds: dataprocessing logic written in the db, and also the flexible way of working with data in an OO environment with compile time checked queries etc. etc. Even though this discussion ended the same as all the other discussions of the same topic ended: with bitterness and a bad feeling, I hope the reader now has a good set of options to make a proper decision. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ Frans,
> I'm pessimistic about if we'll ever get there. What I wrote in my I once lost a full point in an AMBI examination (by telling that I knew it > other posting about politics is IMHO the core reason why this problem > is still a problem in a lot of organisations. The DBAs are in one part > of the organisation, the developers in another part. but not believing forever in it). It was from 8 to 7 points so not that bad. However, in that kind of rules "seperation of accounts" , the DBA should never be alowed to build the sprocs. The only thing that he may do is install them. An administrator can never have the disposal about anything. Just my thought, Cor You're right Frans, and the millions upon millions of DBAs and serious IT
developers all over the world are wrong. Right. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message news:xn0el088135iyl000@news.microsoft.com... > Cor Ligthert [MVP] wrote: > >> Frans, >> >> I am full expactation waiting on the answer from Bill on this. >> >> (You know that I am in this on your side so I can not do that). >> >> However I think that it is time that this discussion is once well >> done. > > The sad thing is: there shouldn't be any dispute over what's to be > used. I mean: it's as silly as 'use VB.NET!' 'NO!! Use C#' "No idiots! > Use Java!!!111".... > > If it was 1996 today, and you weren't in posession of an expensive > toplink license, then yes, Bill would have a good point, as the tooling > to do dyn. sql without even writing a single SQL statement wouldn't > have been available. > > However the points of the past don't apply anymore. > >> Cor > > FB > > -- > ------------------------------------------------------------------------ > Lead developer of LLBLGen Pro, the productive O/R mapper for .NET > LLBLGen Pro website: http://www.llblgen.com > My .NET blog: http://weblogs.asp.net/fbouma > Microsoft MVP (C#) > ------------------------------------------------------------------------ Bill,
> You're right Frans, and the millions upon millions of DBAs and serious IT Probably the USA would not exist in its current way if Columbus would have > developers all over the world are wrong. Right. > listen to those who discussed with him in the same way. Those millions then who told that the world was flat were told that the world was flat. Every other opinion was without any proof made ridiculous or been told that it was heretic. I have seen this more as reactions by some regulars in this newsgroup. However, I had truly not expected it from you. It is Easter so take an egg, and do what Columbus did to show that an egg can stand right up although everybody had told that it was impossible. Happy Easter. Cor William (Bill) Vaughn wrote:
> You're right Frans, and the millions upon millions of DBAs and Oh, so all O/R mapper users across the world aren't serious IT> serious IT developers all over the world are wrong. Right. developers? If this is your argument, then I indeed think the discussion is over. FB -- ------------------------------------------------------------------------ Lead developer of LLBLGen Pro, the productive O/R mapper for .NET LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------  |
|||||||||||||||||||||||
Other interesting topics