I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea [modified]
-
Hi,I've got a doozy. I am the only developer at a fairly large company with a very small IT department. I was asked to write a small application (sends out notifications when contracts are about to expire - slightly more complicated than that.) Our previous programmer had been in a word terrible, and since taking charge I had explained enough to my well-meaning but non-technical that he understands vaguely the benefits of OOP, doing things in a modern programming language, and library reuse. He came to me with a concern today - our VP, another well meaning guy but someone who hasn't programmed since the early days of COBOL - wants everything to be done in PL/SQL stored procedures to 'keep as much of our programming in one place' and to have 'fewer applications to maintain' (wtf, right?). My boss could not convince him otherwise and asked me to create a powerpoint presentation on the issue. I am planning on the following slides: Benefits of using a modern programming langauge: Library Reusability (domain object libraires) Maintenance Clarity Maintenance Testing (automated tests) Maintnenace People Cost (nobody programs in PL/SQL! where are they going to find people?) Tools available (debuggers, etc.) N-Tier Architecture Why People Use Stored Procedures - for each one the premise, why its not exactly correct, and why it doesn't apply to us --User authorizations to use certain ones (we have one user with all authorizations enabled) --Speed (our bottleneck is overuse of ajax, plus its not really true) --Security SQL/Injection (parameterized sql and sometimes our SPs build sql as text and then eval it - sigh) Any advice for doing this presentation? I am really worried that if I have to do this in PL/SQL this is going to become a trend and I will have to quit.
modified on Monday, August 4, 2008 8:16 PM
I, like a lot of people, use stored procs extensively, mostly on reasonably large databases. Some of the reasons: Network performance - why push extra data over the network (sp name + parameters as opposed to long sql statement) Reuse, maintainability and consistency - if you have two applications doing the same query (perhaps with complex joins) and you need to change the query you change (and release) 1 proc, not 2 applications. You also ensure that all versions of the query are consistant and performance tuned - different developers have different skills, not everyone is good at sql. Performance - I know that paramterised queries are cahced etc, but there are more opportunities for tuning with stored procs (some people may dispute that, thats their opinion) There are other pros and cons, and everyone has differing opinions. I am possibly biased in that I have been contracting (mostly at large international financials) for many years, and all PRODUCTION applications have to access the database using stored procedures. This simplifies upgrades and bug fixes as you roll out a stored procedure to 1 server (possibly replicated) rather than several applications to users around the globe.
Bob Ashfield Consultants Ltd
-
Hi,I've got a doozy. I am the only developer at a fairly large company with a very small IT department. I was asked to write a small application (sends out notifications when contracts are about to expire - slightly more complicated than that.) Our previous programmer had been in a word terrible, and since taking charge I had explained enough to my well-meaning but non-technical that he understands vaguely the benefits of OOP, doing things in a modern programming language, and library reuse. He came to me with a concern today - our VP, another well meaning guy but someone who hasn't programmed since the early days of COBOL - wants everything to be done in PL/SQL stored procedures to 'keep as much of our programming in one place' and to have 'fewer applications to maintain' (wtf, right?). My boss could not convince him otherwise and asked me to create a powerpoint presentation on the issue. I am planning on the following slides: Benefits of using a modern programming langauge: Library Reusability (domain object libraires) Maintenance Clarity Maintenance Testing (automated tests) Maintnenace People Cost (nobody programs in PL/SQL! where are they going to find people?) Tools available (debuggers, etc.) N-Tier Architecture Why People Use Stored Procedures - for each one the premise, why its not exactly correct, and why it doesn't apply to us --User authorizations to use certain ones (we have one user with all authorizations enabled) --Speed (our bottleneck is overuse of ajax, plus its not really true) --Security SQL/Injection (parameterized sql and sometimes our SPs build sql as text and then eval it - sigh) Any advice for doing this presentation? I am really worried that if I have to do this in PL/SQL this is going to become a trend and I will have to quit.
modified on Monday, August 4, 2008 8:16 PM
Stored procedures are great, but they are not magical. You might sometimes get a slight speed benefit from using stored procedures, but the potential is very limited. You can easily get much more speed from other changes. If you want to keep as much of the programming as possible in one place, the database is definitely not the place. Robust and maintainable code is better written in a compiled and object oriented language. Your stored procedures should query data, and little more.
Despite everything, the person most likely to be fooling you next is yourself.
-
Just use LINQ :)
xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008)Amen to that my brother :laugh:
Harvey Saayman - South Africa Junior Developer .Net, C#, SQL
you.suck = (you.passion != Programming) -
Frans bouma has something to say about stored procedures here[^]. It's worth reading.
All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia How to use google | Ask smart questions
Thanks, I've been looking for that article for a while. Read it several months ago.
-
Just use LINQ :)
xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008)I'm wondering whether or not Linq is the new embedded-SQL, I might be interested if so.
-
I, like a lot of people, use stored procs extensively, mostly on reasonably large databases. Some of the reasons: Network performance - why push extra data over the network (sp name + parameters as opposed to long sql statement) Reuse, maintainability and consistency - if you have two applications doing the same query (perhaps with complex joins) and you need to change the query you change (and release) 1 proc, not 2 applications. You also ensure that all versions of the query are consistant and performance tuned - different developers have different skills, not everyone is good at sql. Performance - I know that paramterised queries are cahced etc, but there are more opportunities for tuning with stored procs (some people may dispute that, thats their opinion) There are other pros and cons, and everyone has differing opinions. I am possibly biased in that I have been contracting (mostly at large international financials) for many years, and all PRODUCTION applications have to access the database using stored procedures. This simplifies upgrades and bug fixes as you roll out a stored procedure to 1 server (possibly replicated) rather than several applications to users around the globe.
Bob Ashfield Consultants Ltd
Ashfield wrote:
reasonably large databases
It's the statement that matters, not the size of the database.
Ashfield wrote:
as opposed to long sql statement
You only need to do that once per application run.
Ashfield wrote:
1 proc, not 2 applications
One DLL, not two EXEs.
Ashfield wrote:
users around the globe
Web Service?
-
Stored procedures are great, but they are not magical. You might sometimes get a slight speed benefit from using stored procedures, but the potential is very limited. You can easily get much more speed from other changes. If you want to keep as much of the programming as possible in one place, the database is definitely not the place. Robust and maintainable code is better written in a compiled and object oriented language. Your stored procedures should query data, and little more.
Despite everything, the person most likely to be fooling you next is yourself.
I gave you a 5 even though I don't agree with
Guffa wrote:
stored procedures should query data
-
I'm wondering whether or not Linq is the new embedded-SQL, I might be interested if so.
I have personally made it my mission to get rid of all but needed stored procs by using LINQ. And guess what, tomorrow I can swap to Oracle* or MySQL* and use the same code! * Given they have working LINQ query providers.
xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008) -
Ashfield wrote:
reasonably large databases
It's the statement that matters, not the size of the database.
Ashfield wrote:
as opposed to long sql statement
You only need to do that once per application run.
Ashfield wrote:
1 proc, not 2 applications
One DLL, not two EXEs.
Ashfield wrote:
users around the globe
Web Service?
PIEBALDconsult wrote:
It's the statement that matters, not the size of the database.
Not totallly, you can get anyway with any old crap on small databases, but I only mentioned database size as background.
PIEBALDconsult wrote:
You only need to do that once per application run.
What, even a paramaterised query?
PIEBALDconsult wrote:
One DLL, not two EXEs.
Still have to deploy to all users
PIEBALDconsult wrote:
Web Service?
Performance? Sorry, eveyone is entitled to their opinion, I am merely giving my two pennth based on my experience. Other people, as I said, have different opinions and I am fully aware that the financial industry differs in working practices from other industries.
Bob Ashfield Consultants Ltd
-
Hi,I've got a doozy. I am the only developer at a fairly large company with a very small IT department. I was asked to write a small application (sends out notifications when contracts are about to expire - slightly more complicated than that.) Our previous programmer had been in a word terrible, and since taking charge I had explained enough to my well-meaning but non-technical that he understands vaguely the benefits of OOP, doing things in a modern programming language, and library reuse. He came to me with a concern today - our VP, another well meaning guy but someone who hasn't programmed since the early days of COBOL - wants everything to be done in PL/SQL stored procedures to 'keep as much of our programming in one place' and to have 'fewer applications to maintain' (wtf, right?). My boss could not convince him otherwise and asked me to create a powerpoint presentation on the issue. I am planning on the following slides: Benefits of using a modern programming langauge: Library Reusability (domain object libraires) Maintenance Clarity Maintenance Testing (automated tests) Maintnenace People Cost (nobody programs in PL/SQL! where are they going to find people?) Tools available (debuggers, etc.) N-Tier Architecture Why People Use Stored Procedures - for each one the premise, why its not exactly correct, and why it doesn't apply to us --User authorizations to use certain ones (we have one user with all authorizations enabled) --Speed (our bottleneck is overuse of ajax, plus its not really true) --Security SQL/Injection (parameterized sql and sometimes our SPs build sql as text and then eval it - sigh) Any advice for doing this presentation? I am really worried that if I have to do this in PL/SQL this is going to become a trend and I will have to quit.
modified on Monday, August 4, 2008 8:16 PM
I use store procedures a lot, never thought it´s a bad idea. It makes my work better organized. Good Luck:-D