Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
Get a devDuck
Rubber duck debugging has never been so cute! Get your favorite coding language devDuckBuy Now
Search - "stored procedure"
I worked on a greenfield project a couple of years ago. The company had an old solution written in Omnis (heard of it? Yeah, me neither) with an SQL database. My team was to create a completely new web based system... on top of the old database, so the customers could keep their existing stuff.
The dba was an intelligent man, one of the nicest people I've met, and over the course of fifteen years he had made a remarkably terrifying monstrosity of a database. Some years before me they wanted to "future proof" the system and make it "easier to switch to new technologies". So they moved the entire business logic into the database...
I used a tool to create a visualization of said database when we started. It had no views, only tables and sprocs. Look at it! Tables and sprocs are rectangles (well, dots) and any connections are drawn in grey lines. There were no foreign keys, so a tables only visualization only yielded a collection of independent rectangles without a single line.
Now, the stored procedures were bloody MASSIVE. A single procedure that only registered a new interested party and attached them to a property had 2500+ lines and over 150 parameters.
Also, this dba added features and fixed bugs by logging into the respective customers production server and writing SQL.
That database is the stupidest thing I've ever seen a developer do.38
I have this guy who screams and keeps on slamming the table in a meeting room (there is only the project developer inside), about how important to LTRIM RTRIM in sql, combining multiple insert into 1 stored procedure, making a big deals of small feature since we’re on a tight schedule, bla bla bla
Worse retard ever
I almost punched him13
I'm convinced code addiction is a real problem and can lead to mental illness.
Dev: "Thanks for helping me with the splunk API. Already spent two weeks and was spinning my wheels."
Me: "I sent you the example over a month ago, I guess you could have used it to save time."
Dev: "I didn't understand it. I tried getting help from NetworkAdmin-Dan, SystemAdmin-Jake, they didn't understand what you sent me either."
Me: "I thought it was pretty simple. Pass it a query, get results back. That's it"
Dev: "The results were not in a standard JSON format. I was so confused."
Me: "Yea, it's sort-of JSON. Splunk streams the result as individual JSON records. You only have to deserialize each record into your object. I sent you the code sample."
Dev: "Your code didn't work. Dan and Jake were confused too. The data I have to process uses a very different result set. I guess I could have used it if you wrote the class more generically and had unit tests."
<oh frack...he's been going behind my back and telling people smack about my code again>
Me: "My code wouldn't have worked for you, because I'm serializing the objects I need and I do have unit tests, but they are only for the internal logic."
Dev:"I don't know, it confused me. Once I figured out the JSON problem and wrote unit tests, I really started to make progress. I used a tuple for this ... functional parameters for that...added a custom event for ... Took me a few weeks, but it's all covered by unit tests."
Me: "Wow. The way you explained the project was; get data from splunk and populate data in SQLServer. With the code I sent you, sounded like a 15 minute project."
Dev: "Oooh nooo...its waaay more complicated than that. I have this very complex splunk query, which I don't understand, and then I have to perform all this parsing, update a database...which I have no idea how it works. Its really...really complicated."
Me: "The splunk query returns what..4 fields...and DBA-Joe provided the upsert stored procedure..sounds like a 15 minute project."
Dev: "Maybe for you...we're all not super geniuses that crank out code. I hope to be at your level some day."
<frack you ... condescending a-hole ...you've got the same seniority here as I do>
Me: "No seriously, the code I sent would have got you 90% done. Write your deserializer for those 4 fields, execute the stored procedure, and call it a day. I don't think the effort justifies the outcome. Isn't the data for a report they'll only run every few months?"
Dev: "Yea, but Mgr-Nick wanted unit tests and I have to follow orders. I tried to explain the situation, but you know how he is."
<fracking liar..Nick doesn't know the difference between a unit test and breathalyzer test. I know exactly what you told Nick>
Dev: "Thanks again for your help. Gotta get back to it. I put a due date of April for this project and time's running out."
APRIL?!! Good Lord he's going to drag this intern-level project for another month!
After he left, I dug around and found the splunk query, the upsert stored proc, and yep, in about 15 minutes I was done.1
Guy I work with: Hey can I borrow you for a minute
Me: sure. What do you need?
Him: so this is a project me an the other dev worked on
Me thinking: Well I know he did it all and sent you the project so don't tell me you worked on it
Him: so we use it to do this and this and send an email to this new account I made because (2 minute explanation)
Me thinking: I don't care. Just tell me what your issue is! I already know what it is and does from what you told me the last time when you showed me. Which took an hour of my time.
Him: so he sent me this code which is called <Descriptive name> and in the method we have variables call <descriptive name> and it returns a <variable name>
Me thinking: You mother fucker! I don't give a shit what your method is named, what it the variable names are, and you don't need to read through every line of code to me! Just from the descriptive name you just said I know what it does! What the fuck is your issue!?
Him: we also have these other methods. This one is called <Descriptive name> which does...
Me: are you fucking seriously going to read me your code line by line and tell me what you named your variables AGAIN!?
Him: and we named this one <descriptive name>
Me: you mother fucker...
Him: and it calls this stored procedure. (Literally opens the stored procedure and shows me) and it is called...which has parameters called... And it is a select query that inserts
45 minutes later after he finishes explaining all 3 pages of his code and his 5 stored procedures that the other dev wrote...
Him: So anyway, back to this method. I need to know where to put this method. The other dev said to put it in this file, but where do you think I should put it in here? Should I place it after this last one or before it?
Me thinking: You fucking wasted my fucking time just to ask where to place your mother fucking method that the other dev sent to you in a project with only 3 files, all less than 500 lines of code with comments and regions that actually tell you what you should put there and 5 small stored procedures that were not even relevant to your issue! Why the fuck did you need to treat me as a rubber ducky which would fly away if you did have one because you didn't have an issue, you just didn't know where to put your fucking code! FUCK YOUR METHOD!
Me: Where ever you want
Him: Well I think it won't work if I placed it before this method.
I walked away after that. What a waste of time and an insult to my skills and really unchallenging. He's been coding for years and still can't understand anything code related. I'm tired if helping him. Every time he needs something he always has to read through and explain his shit just to ask me things like this. One time he asked me what to name his variable and another his project. More recently he asked why he couldn't get his project he found online to work. The error clearly stated he needed to use c# 7. His initial solution was to change his sql connection string. 😑
He should just go back to setting up computers and fixing printers. At least then he would never be in the office to bug me or the other dev with things like this.7
Fucking hell, the devs before write a query that pulls 30ish column for a report. When I break it down MANUALLY, since it’s a spaghetti on top of another spaghetti, you only need 6. Fuck you, did you dropped your head when you was a kid? Fuck sake, and every query is written in stored procedure even though you’re using an entity libraries19
An application based on a single MySQL stored procedure that contained all the application business logic inside of it (plus a poor webapp that simply called it). The stored procedure had 97 (yes, NINETY SEVEN) parameters... and about half of them were boolean flag used for enabling/disabling another parameter. I think that Uncle Bob could follow you holding an AK-47 if he saw that. The saddest part is that the shit was written by a guy having a PhD in computer science, and he knew that was bad, but the boss asked him to do it in that way. The guy left the company before I joined it and I had to maintain that crap. Guys, the first time I saw it I thought that should be a joke. Code generated by decompilers was easier to read, maybe even Brainfuck. I tried complaining with the boss but she said that the system was wonderful and very efficient. This was one of the reasons I moved to another company after some months.3
Most satisfying bug I've fixed?
Fixed a n+1 issue with a web service retrieving price information. I initially wrote the service, but it was taken over by a couple of 'world class' monday-morning-quarterbacks.
The "Worst code I've ever seen" ... "I can't believe this crap compiles" types that never met anyone else's code that was any good.
After a few months (yes months) and heavy refactoring, the service still returned price information for a product. Pass the service a list of product numbers, service returns the price, availability, etc, that was it.
After a very proud and boisterous deployment, over the next couple of days the service seemed to get slower and slower. DBAs started to complain that the service was causing unusually high wait times, locks, and CPU spikes causing problems for other applications. The usual finger pointing began which ended up with "If PaperTrail had written the service 'correctly' the first time, we wouldn't be in this mess."
Only mattered that I initially wrote the service and no one seemed to care about the two geniuses that took months changing the code.
The dev manager was able to justify a complete re-write of the service using 'proper development methodologies' including budgeting devs, DBAs, server resources, etc..etc. with a projected year+ completion date.
My 'BS Meter' goes off, so I open up the code, maybe 5 minutes...tada...found it. The corresponding stored procedure accepts a list of product numbers and a price type (1=Retail, 2=Dealer, and so on). If you pass 0, the stored procedure returns all the prices.
Code basically looked like this..
public List<Prices> GetPrices(List<Product> products, int priceTypeId)
foreach (var item in products)
List<int> productIdsParameter = new List<int>();
List<Price> prices = dataProvider.GetPrices(productIdsParameter, 0);
foreach (var price in prices)
if (price.PriceTypeID == priceTypeId)
prices = dataProvider.GetPrices(productIdsParameter, price.PriceTypeID);
* Omitting the other 'WTF?' code to handle the zero price type
I removed the double stored procedure call, updated the method signature to only accept the list of product numbers (which it was before the 'major refactor'), deployed the service to dev (the issue was reproducible in our dev environment) and had the DBA monitor.
The two devs and the manager are grumbling and mocking the changes (they never looked, they assumed I wrote some threading monstrosity) then the DBA walks up..
DBA: "We're good. You hit the database pretty hard and the CPU never moved. Execution plans, locks, all good to go."
<dba starts to walk away>
DevMgr: "No fucking way! Putting that code in a thread wouldn't have fix it"
Me: "Um, I didn't use threads"
Dev1: "You had to. There was no way you made that code run faster without threads"
Dev2: "It runs fine in dev, but there is no way that level of threading will work in production with thousands of requests. I've got unit tests that prove our design is perfect."
Me: "I looked at what the code was doing and removed what it shouldn't be doing. That's it."
DBA: "If the database is happy with the changes, I'm happy. Good job. Get that service deployed tomorrow and lets move on"
Me: "You'll remove the recommendation for a complete re-write of the service?"
DevMgr: "Hell no! The re-write moves forward. This, whatever you did, changes nothing."
DBA: "Hell yes it does!! I've got too much on my plate already to play babysitter with you assholes. I'm done and no one on my team will waste any more time on this. Am I clear?"
Seeing the dev manager face turn red and the other two devs look completely dumbfounded was the most satisfying bug I've fixed.5
Got really pissed off writing a stored procedure the other day because the reason behind it is absolute bullshit.
Gave sproc to QA for peer review before release.
QA: why are the variables called @FuckThisShit and @ThisIsBollocks?
Whoops, guess I was more angry than I thought 😂3
I'm coming off a lengthy staff augmentation assignment awful enough that I feel like I need to be rehabilitated to convince myself that I even want to be a software developer.
They needed someone who does .NET. It turns out what they meant was someone to copy and paste massive amounts of code that their EA calls a "framework." Just copy and paste this entire repo, make a whole ton of tweaks that for whatever reason never make their way back into the "template," and then make a few edits for some specific functionality. And then repeat. And repeat. Over a dozen times.
The code is unbelievable. Everything is stacked into giant classes that inherit from each other. There's no dependency inversion. The classes have default constructors with a comment "for unit testing" and then the "real" code uses a different one.
It's full of projects, classes, and methods with weird names that don't do anything. The class and method names sound like they mean something but don't. So after a dozen times I tried to refactor, and the EA threw a hissy fit. Deleting dead code, reducing three levels of inheritance to a simple class, and renaming stuff to indicate what it does are all violations of "standards." I had to go back to the template and start over.
This guy actually recorded a video of himself giving developers instructions on how to copy and paste his awful code.
Then he randomly invents new "standards." A class that reads messages from a queue and processes them shouldn't process them anymore. It should read them and put them in another queue, and then we add more complication by reading from that queue. The reason? We might want to use the original queue for something else one day. I'm pretty sure rewriting working code to meet requirements no one has is as close as you can get to the opposite of Agile.
I fixed some major bugs during my refactor, and missed one the second time after I started over. So stuff actually broke in production because I took points off the board and "fixed" what worked to add back in dead code, variables that aren't used, etc.
In the process, I asked the EA how he wanted me to do this stuff, because I know that he makes up "standards" on the fly and whatever I do may or may not be what he was imagining. We had a tight deadline and I didn't really have time to guess, read his mind, get it wrong, and start over. So we scheduled an hour for him to show me what he wanted.
He said it would take fifteen minutes. He used the first fifteen insisting that he would not explain what he wanted, and besides he didn't remember how all of the code he wrote worked anyway so I would just have to spend more time studying his masterpiece and stepping through it in the debugger.
Being accountable to my team, I insisted that we needed to spend the scheduled hour on him actually explaining what he wanted. He started yelling and hung up. I had to explain to management that I could figure out how to make his "framework" work, but it would take longer and there was no guarantee that when it was done it would magically converge on whatever he was imagining. We totally blew that deadline.
When the .NET work was done, I got sucked into another part of the same project where they were writing massive 500 line SQL stored procedures that no one could understand. They would write a dozen before sending any to QA, then find out that there was a scenario or two not accounted for, and rewrite them all. And repeat. And repeat. Eventually it consisted of, one again, copying and pasting existing procedures into new ones.
At one point one dev asked me to help him test his procedure. I said sure, tell me the scenarios for which I needed to test. He didn't know. My question was the equivalent of asking, "Tell me what you think your code does," and he couldn't answer it. If the guy who wrote it doesn't know what it does right after he wrote it and you certainly can't tell by reading it, and there's dozens of these procedures, all the same but slightly different, how is anyone ever going to read them in a month or a year? What happens when someone needs to change them? What happens when someone finds another defect, and there are going to be a ton of them?
It's a nightmare. Why interview me with all sorts of questions about my dev skills if the plan is to have me copy and paste stuff and carefully avoid applying anything that I know?
The people are all nice except for their evil XEB (Xenophobe Expert Beginner) EA who has no business writing a line of code, ever, and certainly shouldn't be reviewing it.
I've tried to keep my sanity by answering stackoverflow questions once in a while and sometimes turning evil things I was forced to do into constructive blog posts to which I cannot link to preserve my anonymity. I feel like I've taken a six-month detour from software development to shovel crap. Never again. Lesson learned. Next time they're not interviewing me. I'm interviewing them. I'm a professional.9
Today I come across something interresting in SQL Server.
I was optimizing a report query and in the SSMS windows runned in 10 seconds for 3000 rows.
Put it to a stored procedure took me 5 minutes for getting 100 rows.
I was like WTF?
After some research I found out that the problem was that I was using the Stored Procedure parameters in the query.
Created local variables for the parameters and poof... 10 seconds again.
So if you are creating Stored Procedures in SQL Server DO NOT USE THE PARAMETERS FROM THE PROCEDURE. CREATE LOCAL VARIABLES.6
Just pushed a 400+ line stored procedure in production. It's fucking magic and is probably never gonna be touched because no one will understand it and because "it works".5
Why would someone in their right mind change a piece of code that was working fine (and it wasn't a complete mumbo jumbo in styling, logic, transparency, was in line with how the project was coded etc..) to use dapper just to call a stored procedure?! Dafaq is wrong with people!?
Or is it just me & I'm overreactig again?!
I hate when people add more stuff to projects that are already overbloated with fw/techologies to do something so trivial.. like adding 6827646 js fw so you can use one function in each (or are simply still there because they didn't know how to use it and left it in project) which could easily be achieved with our own fw we use...wtf?! O.o
// me cringing, cussing etc..5
Not an enemy yet, but I’ve just debated with a senior dev that said stored procedure is faster, safer, and works better than entity framework
I agree with faster (only a bit) but the rest is just bs23
Today was a manic-depressive kind of day. Spent the morning helping some developers with getting their code to run a stored procedure to drop old partitions, but it wasn't working on their end. It was a fairly simple proc. But working with partitions is a little like working with an array. I figured out that they were passing the wrong timestamp, and needed to add +1 to delete the right partition. Got that sorted out, and things were good. Lunch time.
After lunch I did some busy work, and then the PO comes up at about 2PM and says he's assigned some requests to me. The first was just attaching some scripts. Easy. The second, the user wants a couple of schemas exported ... at 6PM. I've been in the office since 6:45AM.
While I'm setting up some commands to run for the data export, a BA walks up and asks if I'm filling in for another DBA who is out for a few weeks. Yep. There's a change request that hasn't been assigned, and he normally does the work. I ask when it's due. Well, the pre-implementation was supposed to be done in the morning, but it wasn't, and we're in the implementation window ... half way through. I bring up the change task, and look at. Create new schema and users. That's all it says. The BA laughs. I tell I need more to go on. 10 minutes later he sends an email with the information. There's only two hours left in the window, and I can only use half of it, because the production guys have to their stuff, and we're in their window. Now I'm irritated, because I'm new to Oracle, and it's an unforgiving mistress. Fortunately, another DBA says he'll do it, so that we can get it done in time. But can't work it either, because Dev DBAs don't have access to QA, and the process required access for this task. Gets shelved until the access issue is resolved. It's now after 4:15PM. I'm going to in traffic with that 6PM deadline.
I manage to get home and to the computer by 5:45PM. Log in. Start VPN. Box pops on screen. Java needs to update. I chose skip update. Box pops up again. It won't let me log in until Java is current. Passed.
I finally get logged in, and it's 6:10PM. I'm late getting the job started. I pull up Putty and log into the first box, and paste my pre-prepared command in the command line and hit error. Command not found. I'm tired, so it's a moment to sink in. I don't have time for this.
I log into DBArtisan and pull up the first data base, use the wizard to set the job, and off it goes. Yay. Bring up the second database, and have enter the connect info. Host not found. Wut? Examine host name. Yep, it's correct. Try a different method. Host not found. Go back to Putty. Log in. Past string. Launch. Command not found. Now my brain is quitting on me. Why now? It's after 6:30PM. Fiddle with some settings, reset $Oracle home. Try again. Yay. It works. I'm done. It's after 7PM.
There is nothing like technology to snatch the euphoria of a success away from you. It's a love-hate thing, but I wouldn't trade it for anything else. I'm done. Good night.3
about 6 years ago I was working for a large consulting company on a government project. I put in a change for a stored procedure that hard coded the partition to 0, except 0 didn't exist on production, just on test. several thousand government employees couldn't access it for a day. 😞
Wrote a SQL stored procedure today to do a complicated query. Decided to make it so that I could pass multiple records into the stored procedure in comma separated format, but the damned thing would only pull the first record. The query worked fine outside the procedure but it wouldn't pull anything more than the first record. After deleting and recreating and spending 30 minutes trying to figure out what was wrong I realized I changed the length of the wrong parameter. Set the correct one to varchar max and it was all good. 30 minutes of my life I will never get back.🐘💨1
Weeks ago, a change went into production. For some reason, we can't implement our own changes or create new databases in production, we have to have a whole different department do it. This would be great except for one thing:
THEY CAN'T THINK FOR THEMSELVES. I've had to tell them how to run scripts I wrote. I've had to tell them how to fix problems that arise.
Back to that script ran three weeks ago or so. It didn't add permissions to allow me, the system and application developer to see the stored procedure, much less run it. Application can't run it. Thankfully the application works without it.
Fast forward to tonight. My change that I'm attempting to implement is the creation of the stored procedure, because nothing could see it, I assumed it didn't exist... reasonable, right? Database folks tells me it exists. They then tell me they can't give me nor the application permissions because it doesn't ask for it in the change plan.
Excuse me.... WHAT FUCKING WORLD DOES IT MAKE SENSE TO CREATE SOMETHING AND HIDE IT FROM THE CREATOR LET ALONE THE APPLICATION SO IT CAN'T USE IT?! FUCKING THINK. WHY WOULD I WASTE MY FUCKING TIME TO TALK TO YOU OFFSHORE PIECES OF SHIT AT 10PM WHEN I'D RATHER PLAY VIDEO GAMES.
I'm so fucking done with enterprises. Someone with reasonable job security at a startup, please hire me. You will probably pay me more fucking money than this company does anyway.
Now on to my second change of the night. Thankfully I don't have to rely on anyone outside of me... so I won't be wasting my fucking time.
A dev found a bug I created where I set a SQL parameter name to @OrderID instead of the expected @Order. The standard is @OrderID, there is one stored proc where it's @Order.
Oops...I didn't catch it because the integration test didn't cover that area of the code. My mistake...I should have checked...I take complete responsibility for the screw up.
He let me know by email..
"When refactoring, from now on check the stored procedure parameters, there are a few that don't follow the standard."
I was like "from now on..."? ...wow....bold comment from someone responsible for code that doesn't check for nulls, doesn't log errors, and relies on exceptions for flow control. You wouldn't even have known about the error if I didn't modify your code to log the error (the try..except returned false)
I really wanted to reply ...
"Fixed. From now on, when you come across those easily found bugs, go head and fix it, write a test, and move on. Don't send a condescending email to me, my boss, your boss, all the DBAs, and the entire fracking order processing team. Thanks."
But..I thanked him for finding and letting me know...we're a team..blah blah blah..
A few highlights from this week's training on a new BI tool...
Their recommend way for dealing with dates is JOINing the date on their massive date_conversion table, which has "nearly every possible date" as rows and columns like "is_this_month". The table is updated with a provided stored procedure that we're supposed to call from a cron job nightly. Can't I get some Moment.js up in here? Please? Holy shit.
The stylesheets are in a proprietary format. The reference is not public, however I've been assured I can "fully customize" it... Can I get some CSS instead please? I'm dying here.
There's a REST API but it's "too hard to use REST", so they recommend embedding reports using iFrames instead of tapping an API.
I feel like I've stepped in to a portal to 1995.2
During one of our 'pop-up' meetings last week.
Ralph: "The test code the developers are checking in is a mess. They don't know what they are doing."
var foo = SomeLibrary.GetFoo();
Fred: "Ha ha..someone should talk to HR about our hiring practices. These people are literally driving the company backwards."
Me: "I think unit testing is complete waste of time."
- You could almost see the truck hit the wall and splatter watermelon everwhere..took Ralph and Fred a couple of seconds to respond
Fred: "Uh..unit testing is industry best practice. There is scientific evidence that prove testing reduces bugs and increases code quality"
Ralph: "Over 90% of our deployments are rolled back because of bugs. Unit testing will eliminate that."
Me: "Sorry, I disagree."
- Stepping on kittens wouldn't have gotten a worse look from Fred and Ralph
Fred: 'Pretty sure if you ask any professional developer, they'll tell you unit testing and code coverage reduces bugs.'
Me: "I'm not asking anyone else, I'm asking you. Find one failed deployment, just one, over the past 6 months that unit testing or code coverage would have prevented."
- good 3 seconds of awkward silence.
Ralph: "Well, those rollbacks are all mostly due to server mis-configurations. That's not a fair comparison."
Me: "I'm using your words. Unit tests reduces bugs and lack of good tests is the direct reason why we have so many failed deployments"
Boss: "Yea, Ralph...you and Fred kinda said that."
Fred: "No...we need to write good tests. Not this mess."
Me: "Like I said, show me one test you've written that would have prevented a rollback. Just one."
Ralph: "So, what? We do nothing?"
Me: "No, we have to stop worshiping this made up 80% code coverage idol. If not, developers are going to keep writing useless test code just to meet some percent. If we wrote device drivers or frameworks for other developers maybe, but we write CRUD apps. We execute a stored procedure or call a service. This 80% rule doesn't fit for code we write."
Fred: "If the developers took their head out of their ass.."
Me: "Hey!..uh..no, they are doing exactly what they are being told. Meet the 80% requirement, even if doesn't make sense."
Ralph: "Nobody told them to write *that* code."
Boss: "My gosh, what have you and Fred been complaining about for the past hour?"
- Ralph looks at his monitor and brilliantly changes the subject
Ralph: "Oh my f-king god...Trump said something stupid again ..."
At that point I put my headphones on went back to what I was doing. I'm pretty sure Fred and Ralph spent the rest of the day messaging back-n-forth, making fun of me or some random code I wrote 3 years ago (lots of typing and giggling). How can highly educated grown men (one has a masters in CS) get so petty and insecure?6
Architect: "Inline sql is just as performant as a stored procedure and since it is in code its safer and easier to maintain."
Me, inside my head: "I bet I could do the pencil trick on him from 'The Dark Knight' and it wouldn't hurt him as much as suck the world into the small hole in the front of his head since it is clearly a vacuum which was meant to destroy the earth. This is an obvious plant by the lizard people as a test to see if we could identify them. Killing him would be a..."
Architect: "I mean isn't it still a best practice."
Me, out loud and deadpan: "No, that is wrong and it was never a best practice. "
Me, inside my head: "Crisis averted."4
I thought I'd seen the worst possible code.
Until I saw this stored procedure. It was forming a string of JSON by concatenation of double quotes and queries in between.
No wonder it took upwards of 200 seconds to insert just one record.2
So back to the stories of the gentleman with his master's degree who's job I wasn't qualified for. Hope you all enjoy this gem I found in his stored procedure.
Select distinct *
Select * from a inner join b on a.id=b.id
Select * from a full join b on a.id=b.id
Where b.id is null
An inner join unioned to a full join where you exclude null values in right table creates a.....left join you fucking idiot!5
I've now spent 4 hours trying to understand this piece of shit stored procedure.
3475 lines of pure shit with 3 levels down nested sub-selects.
Is there ever any reason for SINGLE Hibernate SQL query/template to join like 10 different tables, do math, and come out to like 30 lines?
This is not a stored procedure, it's a single SELECT2
What's your thoughts on stored procedures(of DBs)?
What are the pros and the cost you found or perceived?
When they are opportune?
Overusing them more than a programming language is an abuse?
I was introduced to a software started initially by economy\finance people which knew a little bit programming, nonetheless their doing became messy though time and at a certain point hired a team of 4 people(from my company) to deal with it, but the approach of the two programmers to build most of the framework on calling stored procedures or queries makes me want to puke, there are almost no layers of separation of concern in place x_x6
I wrote a stored procedure and declared the input as varchar instead of varchar(100). everything seemed to be working. later on we noticed that the procedure only saved the first character of the input (a user form). unfortunately we found out first when the monthly form reports where issued. a whole month of incomplete forms from our users. the client wasn't happy.2
I have a few projects on the go at work at the moment which could be successful, but only time will tell:
1. We have a requirement to monitor or SQL servers for any long running queries (anything that runs longer than 3 minutes). Company didn’t want to pay for enterprise grade solution so as the only SQL Developer I created a small system that involves a database, 2 tables a stored procedure and scheduled job. It goes off every 10 minutes queries some system tables etc and write the results to the tables. Still waiting for it to be deployed to one of the test servers. I have plans for a web front end in the future.
2. My company currently use source safe for version control. They’ve lost the admin password so only 1 person can log in. I’m running he project to plan the migration to GitLab. It’s getting close to completion and soon someone is going to be tasked with creating 100s or projects etc.
3. We use an ERP system which is huge with thousands of tables, but no FKs or anything like that. The current data dictionary is a spreadsheet, as a side project I’m creating a web app so that this information is easily available and searchable.
All 3 projects have the potential to be successful, for my team at least, but stuck waiting for other people to do their stuff first.
Hmm most incompetent co-worker? That would be the guy with a degree in IT who couldn't create a stored procedure without needing a team leader to tell him which keys to press. We were not his first employer and allegedly he had experience...
Perhaps this would be fine, learning curve and all that, for the first few weeks but when a simple select statement was still causing problems alarm bells rang loud. He got attached to the test team for a week before being sat down with the boss....
Finding a stored procedure to copy code out of because I didn’t have the brainpower or willpower to write the same code again.
Ironically, finding the sProc probably took more time than actually rewriting it.....1
When i wrote my first data structure (linked list) in c.
When I first learnt and used the concept of subqueries.
Also in my first job when i was debugging a shitty 2k plus lines stored procedure for days to realize that it was giving a wrong output just because a single variable was unassigned (null)
I need advice fellow developers, am I stubborn?
So I lost an argument in my team regarding constant vs variable directly in a method for stored procedure names.
I separated names of procedures into their own StoredProcedureConstants file because it makes it very easy to see all procedures used in a project and refactor their names if necessary. Argument against was that you loose time creating a constant. Am I silly if I am alergic to seeing quotation marks stuff without its designated purpose throughout the code?
Their way is adding var procedureName = "cc.storeProcedureName" directly in a method. I just can't find my peace with it. To me this is a magic string.
Am I being unreasonable?3
Spent several weeks on a stored procedure and its a masterpiece. Works perfectly and looks amazing with well commented code @rowseyej helped a little.2
Put a stored procedure into production which exclusively only used synonyms to another server’s database, and didn’t actually use any of its own DB’s data..
Didn’t want to add all the scaffolding of a second connection to the app. Oh well!
I hate you legacy code.
I don't want to edit à 3k+ lines long SQL code 😭
I hate the time when people used to use stored procedures...3
I just fucking rage quit SSMS...
Was trying to call a long named stored procedure and started typing the name when suddenly, autocomplete pops up with the name of the procedure.
I'm like hell yeah, I press tab to complete it, it appends the name to what I already wrote...
I delete it, start writing again, pops up again, I press down to select it and press enter, it appends it again.
Who the fuck QA'd that?1
When I use a stored procedure called AdminSetSettingGently I think about this:
I got a report of a relatively simple WinForms app created by a senior (!!) developer who left just as it was released taking 3 minutes to load.
Step through it.. Narrow it down to one stored procedure.
Open said query, every join is a left join.
None needed to be a left join.
Change them all to inners, app now loads in 5 seconds.
Left Joins: For when people can't be assed to learn SQL basics.
If you are falling to stored procedures for every performance optimizations
Concludes You haven't started well..
System.OutOfMemoryException was thrown trying to execute a stored procedure in the database. I think it might be time for some optimizations again. ... but no before coffee
The application I work on starts throwing timeout errors for about every third user. Lead developer cannot figure out what happened. DBA is out of town and cannot be reached. I do a quick Google search and run the stored procedure sp_updatestats. Timeouts stop and there is a big performance boost on the application. Everyone congratulates me on fixing the problem, and now I'm reading up on MS SQL Server Statistics and wondering about what other magical tools everyone else knows about that me and my team are clueless on...
Why a dropdownlist depending on a dropdownlist from a complex type(Stored Procedure) from EF CF approach is that hard to handle in MVC1