stored procedures
-
At my previous job we were using MySQL ( X| ) and when we finally upgraded to a version that supports stored procedures there was an incentive from some developers to use them, but the boss didn't let it happen. He was afraid the DBA would play with the stored procedures and break something.
Nemanja Trifunovic wrote:
He was afraid the DBA would play with the stored procedures and break something.
Wow, at my company it always the DBA is afraid of others breaking something.
My .NET Business Application Framework My Home Page My Younger Son & His "PET"
-
Xiangyang Liu ??? wrote:
I think they were trying to making their product portable (between different databases).
But correct me if I'm wrong most Databases out there (the big ones at least) all use SP's?
up until v5, MySQL didn't have any stoProc support.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
Yes, and I left shortly afterwards. The reasoning was that the database might one day be changed from say SQL Server to something like Oracle or Access, and stored procedures might no longer work on the new system. In actuality, even very simple queries can vary greatly between different DBMS's, so you're still going to have to modify everything - only now it's all hard-coded into all your applications and is a total PITA. For example, the following query in SQL Server will not work in PostGres:
select top 1 * from Customers order by CustomerIDYou have to turn it into something like this:select * from Customers order by CustomerID limit 1If you want to have a completely database agnostic application, you're going to need to use a middle layer to translate calls between your application and the database. We are using DevForce from IdeaBlade[^], and it works really well. Of course, we have no plans to change the underlying database but if we had to, then it would be a hell of a lot easier with DevForce managing everything! To summarize, IMHO there is no reason not to use stored procedures in a system that supports them, and there are many benefits to be gained when you do use them.The StartPage Randomizer - The Windows Cheerleader - Twitter
-
hopingToCode wrote:
But correct me if I'm wrong most Databases out there (the big ones at least) all use SP's?
Can you take source code of stored procedures for one database, say SQL Server, and use on another, say Oracle?
My .NET Business Application Framework My Home Page My Younger Son & His "PET"
-
Nemanja Trifunovic wrote:
He was afraid the DBA would play with the stored procedures and break something.
Wow, at my company it always the DBA is afraid of others breaking something.
My .NET Business Application Framework My Home Page My Younger Son & His "PET"
Xiangyang Liu ??? wrote:
Wow, at my company it always the DBA is afraid of others breaking something
Well, it often works both ways - everybody is afraid of other people breaking something :)
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
In a previous life we used SQL Server CE, and it didn't support stored procedures. We also made the decision to reuse the embedded CE C# code on the desktop platform (write once, use in both places). So we ended up with a desktop system that couldn't use stored procedures either.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
Maybe "General IT discussions" or "General Database discussions" would be a more appropriate forum. "They" the company? No. Well, wait, maybe... Rather than get into a dissertation on why I personally don't write stored procedures, I'll simply answer the question: There was one company that not only didn't have referential integrity on the production databases, but wouldn't even allow me to write functions in the database because, "the metadata would bog down the database". I assume that if the database supported stored procedures, they wouldn't use them either (this was in 2001, using RDB on OpenVMS). I knew that having the functions I wanted would greatly improve the performance of what I was writing, so I had my program create the functions it needed and drop them when it was done. :-D
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
Perhaps they read this blog entry[^]. :)
Jon Sagara Some see the glass as half-empty, some see the glass as half-full. I see the glass as too big. -- George Carlin .NET Blog | Personal Blog | Articles
-
Perhaps they read this blog entry[^]. :)
Jon Sagara Some see the glass as half-empty, some see the glass as half-full. I see the glass as too big. -- George Carlin .NET Blog | Personal Blog | Articles
Oh, good, thanks. I lost that link when I got laid off. There was another one too.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
-
Perhaps they read this blog entry[^]. :)
Jon Sagara Some see the glass as half-empty, some see the glass as half-full. I see the glass as too big. -- George Carlin .NET Blog | Personal Blog | Articles
I think its about control more than anything. The last place I was at did this and it was dreadful to read through the code. They eventually moved some code to the DB so it wouldn't time out. Here it is a bit weird. I think it's a control issue again.
-
As far as I know only the most basic syntax will be portable such as select ... from ... where ... Proprietary T-SQL syntax from Microsoft is not compatible with PL\SQL.
You are right but not only the syntax, also the code organization is completely different. In Oracle using a top-level functions and procedures /outside the packages/ is considered like very bad practice.
The narrow specialist in the broad sense of the word is a complete idiot in the narrow sense of the word. Advertise here – minimum three posts per day are guaranteed.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
My current employer refuses to use stored procedures - despite me literally begging. Their reason - the existing developers (2 of) haven't used them before, and don't understand / know how to write them. They also seem to think that it wouldn't be possible to write stored procedures to cover certain cases - where they are building SQL on the fly - but in 99% of cases this is because the are as thick as 2π_E_/v
___________________________________________ .\\axxx (That's an 'M')
-
My current employer refuses to use stored procedures - despite me literally begging. Their reason - the existing developers (2 of) haven't used them before, and don't understand / know how to write them. They also seem to think that it wouldn't be possible to write stored procedures to cover certain cases - where they are building SQL on the fly - but in 99% of cases this is because the are as thick as 2π_E_/v
___________________________________________ .\\axxx (That's an 'M')
Maxxx_ wrote:
My current employer refuses to use stored procedures - despite me literally begging.
Isn't SPs are faster than queries in code? And also you can save some round trips from code to db.
Be careful, there is no Undo Button(Ctrl+Z) in life.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
Not a SQL guy, but at my last two companies there wasn't an absolute ban, just a "keep them to a minimum and make sure they're really justified" rule. The issue being that stored procedures have big impacts on scalability. Why have the server do something the client could be doing?
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
-
Maxxx_ wrote:
My current employer refuses to use stored procedures - despite me literally begging.
Isn't SPs are faster than queries in code? And also you can save some round trips from code to db.
Be careful, there is no Undo Button(Ctrl+Z) in life.
crudeCodeYogi wrote:
sn't SPs are faster than queries in code
No - not necessarily.
crudeCodeYogi wrote:
you can save some round trips from code to db
Sometimes. My summary of the pros and cons is this: Comparing SP with simply hand-coding the SQL in the application, there's little doubt that SPs are preferable - they can be tested independently, tweaked for efficiency etc. without an application change. There can be run-time efficiencies in using SPs but these are (generally) not significant. Using some sort of automatic SQL generation layer in the application seems to be the 'solution' to not using stored procedures. I guess this is fine (I imagine the layer using reflection to look at an object, converting each property into a field on the insert - possibly using attributes to determine the column names and any special functionality pertinent to that field. However - developing this layer is, I think, complex and time consuming. If you re-use the layer then I guess it can save development time - but I like to split projects between developers, so my DBA can develop stored procs - the interface is agreed between the software developer and the DBA - then I don't care how he does it - as long as the Stored Proc sores and retrieves the information I need, efficiently. My fear of 'automatic' sql generation is in the exceptions that come along - leading to a more complex layer, harder to debug if something goes wrong. With a SP, i can easily identify bottlenecks, and have them easily looked at independently of the application. I can then oftentimes modify the SPs on the production system without the need to redeploy my application.
___________________________________________ .\\axxx (That's an 'M')
-
Not a SQL guy, but at my last two companies there wasn't an absolute ban, just a "keep them to a minimum and make sure they're really justified" rule. The issue being that stored procedures have big impacts on scalability. Why have the server do something the client could be doing?
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
Joe Woodbury wrote:
Why have the server do something the client could be doing?
That depends on what the "something" is. I'd turn that around; "why have the client do something the server could be doing?" Reduce database round-trips (if that's the corredt term). I've seen too many systems that are slow because the server simply passes back all the data and makes the client filter it (and then perhaps perform an update). In general, I believe in having the server do as much as possible; and that doesn't even require stored procedures.
-
Has anyone come across a job where they actively refuse to use stored procedures? I'm trying to understand what the rational is.
The book Architecting Applications for the Enterprise[^] (Esposito/Saltarello) includes a chapter 'To SP or Not to SP' with some actual aspects on this topic. It analyzes some myths about Stored Procedures: - Stored Procedures are faster than SQL code - Stored Procedures are more secure than SQL code - Stored Procedures can be used to fend off SQL injection - Stored Procedures can be used to reduce brittleness of SQL code Their advice phases to my experiences: use Stored Procedures for CRUD operations on a per-table basis. For large solutions you can use Stored Procedures, in association with Views, to provide another abstraction layer. Cheers, Jani
-
Joe Woodbury wrote:
Why have the server do something the client could be doing?
That depends on what the "something" is. I'd turn that around; "why have the client do something the server could be doing?" Reduce database round-trips (if that's the corredt term). I've seen too many systems that are slow because the server simply passes back all the data and makes the client filter it (and then perhaps perform an update). In general, I believe in having the server do as much as possible; and that doesn't even require stored procedures.
PIEBALDconsult wrote:
"why have the client do something the server could be doing?"
Because it has an impact on scalability. At my last two companies, excessive use of stored procedures caused large impacts on performance on the back end. At my previous company, the main architect pointed out that had he allowed some of the stored procedures to be used that were tested, it would have cost the company hundreds of thousands in expanding the server farm (they served up terabytes of data.) In other words, the slight performance hit from the client perspective was minor compared to the performance hit on the SQL servers.
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
-
PIEBALDconsult wrote:
"why have the client do something the server could be doing?"
Because it has an impact on scalability. At my last two companies, excessive use of stored procedures caused large impacts on performance on the back end. At my previous company, the main architect pointed out that had he allowed some of the stored procedures to be used that were tested, it would have cost the company hundreds of thousands in expanding the server farm (they served up terabytes of data.) In other words, the slight performance hit from the client perspective was minor compared to the performance hit on the SQL servers.
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
Joe Woodbury wrote:
excessive use of stored procedures caused large impacts on performance
My point is that you can have the statements execute on the server without using stored procedures.
