- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Because talking to an auditor or reading any major security compliance guideline to validate is so difficult?
Admin
CAPTCHA: acsi - the ANSI SQL committee got all acsi about the idea of bind parameters in FROM clauses.
Admin
Code with proper parameters where the engine doesn't have to try to sniff it out that is true. If you're going to go that far though, just make it into a stored procedure at that point, doesn't make sense not to.
Admin
This was explained here in much detail.
Admin
"That's not a target. That's Church." "Target locked" "What? No. Target unlock. Unlock! Please help me nice lady."
Admin
But at the other end of the Talent Ladder (7), the incompetence can be astounding even compared to the wobbliest software engineer. A couple of steps up from the bottom rung, you may as well be dealing with a robot. He can enter and run the commands he is given, without question or comprehension, but that is all. Setting up a piece of kit is akin to driving the Mars Rover. Transmit an instruction from Mission Control and 20 minutes later you get back a grainy, blurred report of what actually happened. If you directed it down a ravine, there it will be in bits. Same with Sys Admin Robot Boy. If you tell him to run a query which deletes every single record from the main transaction table, that is exactly what he will do. If you told him to squirt mayonnaise in his hair and go and sit in the broom cupboard, he would have done that too, so long as you had filled in an ITILS-compliant change ticket for same and had it approved by the appropriate authorities.
At the absolute bottom of the Talent Barrel, the chap won’t even be able to copy and paste reliably. What you get, in effect, is a hopelessly inept copy typist and the results can be catastrophic. You have, say, been testing the system for a day or two and getting all sorts of inexplicable errors. One of the developers then figures out what is wrong: only about a third of the upgrade script has been run. No-one can work out why this should be until someone chances to open said script in Notepad. It is just over three screens long. The piece that was run by Clueless Database Administrator is precisely that which is visible when you first open the file (8). And now you pine for the return of Sys Admin Robot Boy.
Admin
Systems Managers.
Scum. Bullying, bureaucratic, arrogant, idle, shirking, duplicitous, pig-ignorant thugs, every man jack of ‘em, almost without exception. Three out of a hundred do not confirm to this pattern and are instead charming but fabulously slippery spivs who are so Teflon-coated, oily and elusive that getting accountability, guilt or a piece of work to stick to them is as difficult as coating a live eel with a layer of greased jelly using tweezers.
But your mainstream bullet-headed redneck Data Centre despot has no use for such subtlety. He doesn’t avoid conflict, he looks for it. He revels in it. He loves to say No. And he will say No, any time you want to do anything on his precious bleeding server farm. You want a new server built. No – the capacity planning process hasn’t identified a need. You want to install some software. No – it isn’t included on the approved build list. You want to move files from one server to another. No – Data Protection concerns. You want to add one line to a config file. No – no test cases, no backout plan, no evidence whatsoever of adherence to Change Management best practice. You want a new firewall rule so that our server can talk to someone else’s. You know, so we can exchange data so we can maybe do work and perhaps eventually get paid. Nay, Nay and Thrice Nay – connection across the internet ? To a third-party server ? Alien data entering and polluting the essential purity of our sanctified Data Centre ? Are you stark staring mad ? Have you not read the 308 blessed pages of our Corporate Information Management Protocol ?
Admin
+1
Admin
Oh man, that brings me back.
Admin
Admin
Admin
The A is for ADMIN not ANALYST.
Admin
"...there is a procedure for everything, and everything has a procedure. The fundamental premise is that everything should be automated."
Sounds like heaven to me. Can I go work there right now?
Admin
Or an embedded system without a file system.
Admin
And this is unusual in just what way? I've never worked anyplace where DBA's actually reviewed designs, code, tablespace assignments, or etc blah. They're concerned with making sure the backups run, and...um...let me get back to you on that...
Admin
Because mandating that all SQL be written in sproc form causes more problems than it solves. For most modern SQL servers the perf difference is negligible (assuming parameterized queries). However you rapidly increase the surface area of your API. A single table will end up with tens of sprocs ranging from "GetFooById" to "GetFooOrderedByBarThenBaz" and "GetFooJoinedOnQuxByQuxId" etc etc. Sure...in theory they are more "secure". But the ability to audit your access becomes a case of auditing access to your 2000 sprocs on your 10 table database.
Then when your new developer joins, they need to learn the API you have created that is the huge number of sprocs (as opposed to some kind of common Structured Query Language, that consists of known keywords like "SELECT" and "WHERE"). Naturally they find that the sproc doesn't QUITE do what they want, and they add another...causing another storm of auditing to occur.
Finally you find that you have to add a column to your tables...cue going through each and every one of those 2000 sprocs!
Admin
These compliance requirements are there for a reason, and nonensense, I've seen plenty of environments flourish this way. You do not need a new sproc just for adding a row, just like consolidating indexes, you also consolidate stored procs. If you need a different column, you add it. In edge cases of extreme data being fetched back with joins that simply don't work well, a rearchitecture might be needed. Whoever is mandating a new stored proc per row or minor difference is doing it wrong. Who would do that??
Admin
I thought this was a site for enterprise level developers with tons of experience. I really don't care what someone does with their webapp or mobile app, it doesn't pay the bills.
Who the hell would use Oracle for 'everything'? That would be insane, those people have issues. A database inside an app that has 1 system user that doesn't get called from the outside ever? I really don't care what happens with that, no one has ever paid me enough money to even look at it because there isn't much impact on a minor system. Sure, someone could be slow loading their next Tinder matches, but the developers can usually fix those after some work.
There's always edge cases where the concept is 'who cares? No one is going to really use your code anyways so why optimize' which is fine, but that isn't what pays the bills.
If it's a critical multitenant environment and not some phone app or 1 guy, or it's publicly accessible, depending on the DB engine, it's getting stored procedures.
The day it becomes the developers job is to manage performance, security, and availability of the instances and databases, including signing off on auditors documentation, then they can set your own non-best practices but otherwise, we'll have to follow what has been demonstrated and accepted as the best practices.
CAPTCHA: minim The developers will do the minim needed to get their code into prod.
Admin
Hint: that Cortex-M I mentioned isn't running some random mobile app. It's the kind of thing that'll be running a mission-critical embedded system, out in the middle of Donner Pass, that has to work, even if the power line to it fell down last week and the data radio that's your only IP connectivity is barely able to get a signal from the base station because the antenna is half buried in snow and the power's throttled all the way back to conserve the batteries that are now your only power source. Oh, and did I mention that those batteries have to last you yet another week before a crew can restore AC power?
Do you want to be the DBA for that system, especially if it means going out and spending a few days in the Donner Pass, in a raging snowstorm, babysitting your database?
P.S. Yes, the place I work for has field equipment out in the Donner Pass.
CAPTCHA: ludus - You're ludus to think that enterprise-critical applications always live in cozy datacenters.
Admin
That's how it was for me at my company. I discovered that "DBA" for us meant "database Administrator", not "Database Analyst".
Admin
Avoiding the wall of quote text, and I repeat: " If it's a critical multitenant environment and not some phone app or 1 guy, or it's publicly accessible, depending on the DB engine, it's getting stored procedures. "
What you described sounds like a 'edge case' and not a multi-tenant environment publicly accessible site with thousands of concurrent sessions exposed to all the script kiddies in the world with sensitive data.
SQLLite wasn't designed so much as a network service but as a simple program, probably a dll or two, that runs in your application. Your business logic is now in the app instead of the RDBMS. EVEN THEN though, you can still create UDFs in python, powershell, etc. to act as a way of maintaining the logic. Clearly, if a RDBMS doesn't support stored procedures, then it's not going to be mandated to be used.
There's more RDBMS systems out there than both of our fingers combined, so obviously no one is talking in absolutes but on this site sometimes that's missed since we're so tempted to present a edge case scenario to try to prove something wrong, such as a RDBMS that doesn't use procedures.
What the heck does the backup power have to do with stored procedures anyways? Ok, it requires backup power for a whole week. Pretty irrelevant to the conversation. What does things having to be in data centers have to do with anything? I think your focus shouldn't be on data centers and power, but whether the RDBMS you're using has a internal DB engine which let's network users connect instead of SQLite which is simply available as part of the app.
Admin
Let's flip over to the other hand: would you rather the entire app simply lived in the DB? It's most certainly possible...
Admin
IMO a DB engine should be responsible for storage of data, and retrieval of data, with some manipulation. Outside of that, as a rule of thumb, I would prefer the logic to be in the app. It's worked well for me thus far, but open to other thoughts.
Admin
Where do you live? My company is hiring.
Admin
I sincerely thank you for the offer, I'm flattered, but I'm really happy at my current gig in So Cali.
However, if you need any kind of ammo, I'll be happy to look up posts and writings from the real authorities of the database and design community on these best practices so you can refer to them when speaking with your DBAs.
It will have to be a really extreme case for them to seriously demand a new stored proc per column difference. If that is the case, it might be a badly designed system or you guys just seriously blow through massive amounts of data that absolutely needs ACID compliance and a relational model.
Admin
I'm not a DBA. I'm a developer. I'm just saying Bob doesn't know anything about databases.
There's no such thing as DBAs. These are just coders that can't write code and now they babysit data. Enormous waste of money.
Admin
Crap, I'm in SoCal, too.
Admin
Actually, if you start having sproc proliferation problems of that sort, wouldn't it be better to start refactoring your stored procs? I have a hard time believing that you couldn't write a stored proc that was parameterized on its result columnset...
Admin
Aww, WorldClass is having a World Class hissy fit today. The ironic thing is you do not understand that your code and lack of foresight into operations is the reason DBAs need to exist in the first place, and continue to get paid more than developers do by a nice sum, industry wide.
In short: Don't suck so hard that the company has to hire admins, and pay them more than they pay you because you're not up to snuff buddy. Your salary isn't my fault, it's your fault.
Admin
DBA thinks DBAs should control part of the business logic of the program using stored procedures. Very surprising. Application developer who has to deal with the code management wants to keep all the code in one repository and maintain a single point of control for code. Also very surprising.
The question for me is, at what actual size of application does the overhead in interface documentation and opportunity for error inherent in sprocs become worth the effort in terms of Taylorising the application? How many developers do you need to change this particular lightbulb?
My own view is that a developer who can't deal with SQL injections shouldn't be allowed near anything where they can happen, and that the argument about partitioning code in the db or the application is irrelevant. But I've never worked on a system large enough to test that view.
Admin
Agreed that a dev who doesn't grok bind params has no business anywhere near SQL. One approach to resolving the bind of "who controls the stored procs?" is to have the "master" copies of your stored procs live as .sql files in your repository, and then use your schema deployment system to push them out alongside your DDL. That way, everything lives together under the control of the app team (and their attached DB dev, if you're in an organization that provides one), yet the system is flexible enough to deal with whatever partitioning between app and DB you use.
As to 'what size app should you start using sprocs on?' it really depends on what your app does. If you're doing simple CRUD operations on data you own the schema for, stored procs are simply extra overhead. They're quite worthwhile for encapsulating complex DML or procedures that require a mix of DDL and DML; also, they may be a better choice than a view for encapsulating a complicated query, depending on how fast the data changes. Some operations are best not done in stored procs though: it's probably not very nice to your DBA to make him buy expensive licenses for the extra cores he just had to add to the DB server due to your CPU-hogging stored procs! (Or in other words, leave complicated computations, etal to the app server.)
Admin
Admin
Agree, CRUD stored procedures are a waste of time, prepared statements can do this fine, or even an ORM (which just uses prepared statements). However with the complicated computations, it depends. If the computations are more efficiently handled in set based approach, a stored procedure will beat an app server most times, use less CPU cycles and have a runtime that is far less. Any form of string processing has no place in a database. Let the app developers handle the single object, string processing, simple validation, single row processing but the dev DBAs should handle the set processing. On the other hand, let the app devs handle the large data sets and I make money fixing up the stuff ups. :)
Admin
Mr. AHole DBA, a few more years experience and you'll change your tune. Auditors don't demand stored procedures for everything, competent ones that is... I work with auditors a lot. Writing procs for CRUD, is mind numbing, slow and boring, let the frontend monkeys do that in their ORMs. more important to get the database design correct, something that should not be left to app developers, unless you want to pay me to fix up the mess they will create. Some of the reasons you have given for using procs are wrong, unless you're using SQL Server 2000, there is no difference between procs and prepared statements performance wise. Where there is a difference is when a frontend monkey pulls a large number of rows out of the database, does some processing (usually row by row) that can be replaced with a set approach and sends the results back to the database. The worst I have seen is an 11 hour process, written by very good developers (who had no idea of set theory) turn in a 1 minute process with a stored procedure. App developers tend to write horrid stored procedures, especially in PL/SQL, PL/SQL really has a lot to answer for as it lets developers write truly bad code, arrays and for loops in a database, that is a WTF. I shouldn't complain, fixing bad database code means I can have shiny things.
Admin
I cringe when I see "best practice". It shows that the person using the term is unfamiliar with ITIL...
Admin
Quite untrue good sir, I'm ITIL V3 certified. The concept of 'best practices' aren't made obsolete by ITIL, more so that they evolve over time under the continual service improvement cycle. :)
Admin
In my experience for those cases, then do stored procs just for manageability sake. Yes, there isn't a performance issue with properly parameterized queries vs stored proc, but it's more about management.
I could also grant EXECUTE permissions to indvl stored procs in a DB, of course you could handle that on your app if you'd like but it does provide an additional step for you and 1 less level of control for the DBA who might get odd requests here and there. We could change the internal code of the stored procedure and not change the output/inputs so it helps the developers from having to go through a whole new rebuild of code to change 1 item. That's pretty nice.
However, if you do use an abstraction layer such as an ORM and it fits a good business need, I'm not going to get in the way of it. Yea, it'll make my job a pain in the butt sometimes but so what, the world isn't tailored to me, it's tailored to the business requirements and what the 'right' approach is. There aren't too many cases where you will write properly parameterized queries that you wouldn't benefit from turning them into a stored proc IMO, and it will make your life a lot easier with auditors whom the business gets to pick.
I can see your point(s) and largely agree. I let the developers have dominion over their code unless it's a security/performance/manageability issue. One problem in large shops is you get 'new' or 'blemished' developers who do write garbage but that comes with the job, and as you mentioned also gives us job security.
Cheers.
Admin
If you believe that adding a clustered index is a panacea I have a bridge to sell you. Clustered indexes are useful but only when you actually use them the right way. Sure, if you are doing a transactional application which does 1-row key-based queries, makes sense. For analytics, your clustering key on the surrogate ID is useless and just slows down my full table scans.
Admin