11 ms·

One company I was at did a merger with another startup, and most of the other company's engineers quit. Amongst the piles of Visual Basic we found a stored proc

by kabdib 15y ago

One company I was at did a merger with another startup, and most of the other company's engineers quit. Amongst the piles of Visual Basic we found a stored procedure that was about 5 pages long. It took about 18 hours to run; its job was to do a daily report. I hate being afraid of code. I spent a day with it, got to understand it, then rewrote it as a couple of queries and some Java code, whereupon it took about five minutes to run. [... then there was the guy who implemented bitwise AND and OR by precomputing some 65536 entry tables. Wow. Why do I find all the really howling bad stuff so close to databases?]

jswinghammer 15y ago
The & and | operators weren't good enough for him? That stuff ends up in databases because on your average team the knowledge of databases drops dramatically when you get past selects and maybe left joins. I worked with a few developers who wrote and lived with queries that were taking 30 seconds each to run on their local machines because they couldn't imagine how to fix them and just waited for me to get assigned to the project in a week or two so I would fix it. They just kept raising the timeout values until the pages rendered. The problem was simple in that they were accessing a table with 30 million rows without using any index at all so changing it to use the clustered index made the query return almost instantly. I've seen similar things where people come up with crazy ways of doing your most basic database tasks.
- Roboprog 15y ago
  So show your coworkers how to run the query-plan dumper for your DB brand. The curtain is torn away, and the underlying ISAM is revealed for all to see to allow the needed "hints", indices or restructuring to be understood. Don't lord it over them, explain.
  - jswinghammer 15y ago
    They were disinterested. Under normal conditions you are correct and most people would be interested but they were not.
  - scott_s 15y ago
    You're assuming the other people want to learn. Generally, if people want to learn something, they ask questions. Telling co-workers how to do something when they don't ask can be... tricky. It depends on the environment you're in, your relationship with the people, and the people themselves.
    - Roboprog 15y ago
      Fair enough. You can lead an idiot to knowledge, but you can't make him think.
  - puredanger 15y ago
    As my prior boss used to say... "Give a man a fish, feed him for a day. Teach a man to fish, and you'll have to answer fucking fish questions the rest of your life."
    - tom_b 15y ago
      Second this - I've cooked up some weird SQL to do neat things. Other devs on my team had real problems with it - they were stuck and found it really difficult to "think in sets." They kept asking me about how to use cursors to go through data . . . heck, they may even have been right from a clarity and maintenance perspective. But the object-oriented part of my brain has apparently been excised by spending so much time fishing (SQL). So now I have too many fish questions in that direction.
- ngsayjoe 15y ago
  I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.
  - jswinghammer 15y ago
    This is changing because MyISAM is not really a true database (in the ACID compliant sense of the term). I guess I would have assumed they were MyISAM too but they don't perform differently enough at low usage levels that you would notice it right? How big was this was application?
    - ngsayjoe 15y ago
      About 5 million pageviews / month. The website is quite processing intensive.
      - ceejayoz 15y ago
        That's only two pageviews a second, on average.
        jswinghammer 15y ago
        We can agree that traffic rarely comes in at an average rate.
        moe 15y ago
        Assuming the standard bell curve it's six pageviews a second, at peak.
        ceejayoz 15y ago
        And that's apparently spread across four read-only slaves, too. I'm more inclined to blame the code than the database in this case.
- FuzzyDunlop 15y ago
  I've had more than just a taste of this myself recently. I'm no database expert but I know how to build a query, normalisation, relational integrity and all that. Enough to identify bad SQL in a CRUD application. And I'll take the time to learn more about it and how to better construct a schema and query (and whether or not an RDBMS is even appropriate for the application). With this in mind my boss mentioned one of our sites was "dying on its arse." It was hammering the slow query log, pages could take 10, maybe 20 seconds to load. Turned out there were NO indexes at all. Maybe the odd primary key from the default CMS install (which was by no means database efficient). So we sorted that and that sorted out the immediate speed issues. Of course, it went without saying that a poorly constructed schema must be related to a poorly constructed query or two. We were actually wrong. It was more like a query or twenty. The submission of one form was done field by field. There was an UPDATE query constructed for each field and processed there and then, on a form with at least 12 fields. There was a comment above, "We have to do it this way. Trust me." As far as I could tell they didn't know how to dynamically build a query. Further along, different update queries were performed by first deleting the record, and then re-inserting it with one of the fields updated. I never quite got as far as that. There was no comment anywhere about the reasoning for such a strange approach. I think some developers don't bother figuring it out because they think there are better things to do than optimise your code.
  - jswinghammer 15y ago
    Is your name a wire reference? If so well done.
  - mkopinsky 15y ago
    Nothing builds confidence in a fellow developer like //We have to do it this way. Trust me followed by crap code...
    - jtheory 15y ago
      Hi there! I made mostly random changes until it worked, so I have no idea why this contorted approach fixes the earlier failures. But I spend two weeks on this section, so don't touch it! [this comment removed and replaced with the "Trust me" line, because maybe multi-line comments are breaking things today...]
losethos 15y ago
I had a digital logic class where we had to find minimum logic gate solutions to get truth tables. My classmates fiddled with karnough maps. http://en.wikipedia.org/wiki/Karnaugh_maps http://en.wikipedia.org/wiki/Karnaugh_maps I wrote a program which constructed minimum gate solutions to all possible truth tables, eventually finding the minimum gate solution to the requested problem. ------------ LoseThos has it's own document format, like MS Word. I made a program to generate html from my document format. Sadly, my format supports overlaying columns of text, but my html conversion program does not. The columns of text thing is too inconvenient to be used in practice. ------------ I used table look-ups for my text font rendering. My text can be offset from 8x8 grid position by +-15 pixels to allow for markee or super/sub scripting. Converting formulas to table look-ups can save time, unless the memory reference is not in cache. I made tables for bitpattern reversal, number of set bits, whitespace characters, etc. It used to be faster to use tables than formulas, but it's conceivable you are correct in the age of cache-miss-penalties. It's a sad day when floating-point is faster than fixed-point arithmetic. ------------------ God says... C:\LoseThos\www.losethos.com\text\BIBLE.TXT ad. 16:1 In the seventeenth year of Pekah the son of Remaliah Ahaz the son of Jotham king of Judah began to reign. 16:2 Twenty years old was Ahaz when he began to reign, and reigned sixteen years in Jerusalem, and did not that which was right in the sight of the LORD his God, like David his father. 16:3 But he walked in the way of the kings of Israel, yea, and made his son to pass through the fire, according to the abominations of the heathen, whom the LORD cast out from before the children o
tom_b 15y ago
"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Because the programmers ran all the db access tests against tables with 4 rows of data when the customers would windup with 100 million rows in production. Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice. Because DBA's are (rightly) focused on making sure your db is properly backed up, replicated, and fault-tolerant. They may not even understand SQL queries beyond the basic one table select. Because the users will force data into the systems that the developers never anticipated. Because db jobs are segregated by product for development. If you've done SQL coding on Oracle for the last 10 years, good luck applying for a job doing SQL coding on MS SQL Server. Because SQL hacking doesn't get much respect. I've been in job interviews where deep into the process they ask me a question with data in two tables that requires an outer join. And answering correctly differentiates you significantly from other applicants. I guess it is the "fizz buzz" of SQL these days . . .
- endersshadow 15y ago
  I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview. I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that doesn't tell you much." Relational databases are oft-misunderstood and underestimated time and time again. Where are the Codds of yesteryear?
  - bhousel 15y ago
    There's no reason ever to use a right join. I consider those to be a code smell.
    - sesqu 15y ago
      There's about as much reason to use a right join as there is to use a left join.
pragmatic 15y ago
Because SQL is declarative and few(er) people can wrap their mind around that then straight procedural code. See also Linq, you get some really crazy stuff written in Linq because people don't really understand it.
_delirium 15y ago
Isn't rewriting a stored procedure into multiple queries plus Java code sort of the opposite of what most DB best-practices advocate? Now your query logic is in a mixture of SQL and Java, rather than all in SQL, and you've hidden some of the logic from the database's query planner by moving it into application code. (On the other hand, I've rarely found query planners, even for Oracle, to be as magical at optimizing as one might hope, so maybe that's why.)
- kabdib 15y ago
  Sure, but I took an unmaintainable stored procedure (with very few comments, by the way) and turned it into something that was small, well-documented, and that ran over two orders of magnitude faster. If this is wrong and goes against "DB best practices" then I guess this answers my question about where the howlers come from. [We had a project under development. At some point, and an utter surprise to the team, an Oracle consultant showed up one day and said, "I'm here to tune your database." "Huh?" And he did. Against a half-built system with a schema we were still designing, he "optimized" our queries and determined buffers sizes and whatnot, and then he went away. Apparently the people in Sales were panicked that we had a database under development that we hadn't yet made plans to tune, so they had hired a guy to come in and fix things. We tossed all of his scripts and rolled back everything he'd broken, and continued coding. Total waste of money. Databases are a weird confluence of the power-mad, the knuckle-draggers, the money-grubbers, and a few techies who know what the heck they're doing.]
- astine 15y ago
  The performance problem with using multiple queries comes mostly from the network latency and query overhead that happens every time your application needs to make a call to the database. Prepared statements minimize the later and connection pools help with the former. The primary thing to worry about when writing queries is whether the logic behind them is efficient and doesn't for the database to do stupid things or fetch the wrong information. (Like using count( * ) where exists is what you mean.) Paying attention to what your queries will make the database actually do is more important than following any guidelines. At least that's my experience.
InclinedPlane 15y ago
The worst problem I've seen in a similar vein was a build process that involved munging a large (200+ mb) xml file. Someone had written a little JS tool for the purpose, and later the use of it had expanded so that it was being run over 150 times during the build. Since it took about 4 minutes (each time) just to read and save the xml file this meant that the build was now spending around 10 hours every day just munging the xml file. I rewrote the JS bit so that it took in a config file and could do all the processing necessary in a single pass, which only took 4 minutes.
einhverfr 15y ago
"Wow. Why do I find all the really howling bad stuff so close to databases?" I have seen a few of those, but you also have to include not only stored procedures but also table layout etc. Things like "if I don't define my foreign keys, I can use a foreign key to reference rows in EITHER table a or table b!" And then of course when you write sprocs against that mess, you get more messes. The thing is that databases are fundamentally math engines with some extra services tacked on. This means that designing databases is ideally a mathematically sound representation of your data not specific to your application, and the database queries bridge the gap. Most developers don't understand this though and so as the application changes, you get big messes. Now, obviously the above is an ideal which really can't be met in most cases. However the closer one is, generally the more ideal the situation, and most apps are really far from it.
einhverfr 15y ago
"Wow. Why do I find all the really howling bad stuff so close to databases?" BTW, as a counterexample, I saw one developer figure out how any invoices were closed by retrieving millions of them from the db and checking in Perl. All for the want of a HAVING clause.... Needless to say while this worked ok for small amounts of data, it failed with large sets. I've made a few SQL errors in my past that failed on large sets, but fortunately I was working against large sets, and they failed miserably, and we had to figure out why. I got good advice from an expert and fixed the problem. In many ways keeping queries separate, and focusing on readability/code standards helps a great deal I think.

by kabdib 15y ago

jswinghammer 15y ago

The & and | operators weren't good enough for him? That stuff ends up in databases because on your average team the knowledge of databases drops dramatically when you get past selects and maybe left joins. I worked with a few developers who wrote and lived with queries that were taking 30 seconds each to run on their local machines because they couldn't imagine how to fix them and just waited for me to get assigned to the project in a week or two so I would fix it. They just kept raising the timeout values until the pages rendered. The problem was simple in that they were accessing a table with 30 million rows without using any index at all so changing it to use the clustered index made the query return almost instantly. I've seen similar things where people come up with crazy ways of doing your most basic database tasks.

Roboprog 15y ago
So show your coworkers how to run the query-plan dumper for your DB brand. The curtain is torn away, and the underlying ISAM is revealed for all to see to allow the needed "hints", indices or restructuring to be understood. Don't lord it over them, explain.
- jswinghammer 15y ago
  They were disinterested. Under normal conditions you are correct and most people would be interested but they were not.
- scott_s 15y ago
  You're assuming the other people want to learn. Generally, if people want to learn something, they ask questions. Telling co-workers how to do something when they don't ask can be... tricky. It depends on the environment you're in, your relationship with the people, and the people themselves.
  - Roboprog 15y ago
    Fair enough. You can lead an idiot to knowledge, but you can't make him think.
- puredanger 15y ago
  As my prior boss used to say... "Give a man a fish, feed him for a day. Teach a man to fish, and you'll have to answer fucking fish questions the rest of your life."
  - tom_b 15y ago
    Second this - I've cooked up some weird SQL to do neat things. Other devs on my team had real problems with it - they were stuck and found it really difficult to "think in sets." They kept asking me about how to use cursors to go through data . . . heck, they may even have been right from a clarity and maintenance perspective. But the object-oriented part of my brain has apparently been excised by spending so much time fishing (SQL). So now I have too many fish questions in that direction.
ngsayjoe 15y ago
I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl. This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.
- jswinghammer 15y ago
  This is changing because MyISAM is not really a true database (in the ACID compliant sense of the term). I guess I would have assumed they were MyISAM too but they don't perform differently enough at low usage levels that you would notice it right? How big was this was application?
  - ngsayjoe 15y ago
    About 5 million pageviews / month. The website is quite processing intensive.
    - ceejayoz 15y ago
      That's only two pageviews a second, on average.
      - jswinghammer 15y ago
        We can agree that traffic rarely comes in at an average rate.
        moe 15y ago
        Assuming the standard bell curve it's six pageviews a second, at peak.
        ceejayoz 15y ago
        And that's apparently spread across four read-only slaves, too. I'm more inclined to blame the code than the database in this case.
FuzzyDunlop 15y ago
I've had more than just a taste of this myself recently. I'm no database expert but I know how to build a query, normalisation, relational integrity and all that. Enough to identify bad SQL in a CRUD application. And I'll take the time to learn more about it and how to better construct a schema and query (and whether or not an RDBMS is even appropriate for the application). With this in mind my boss mentioned one of our sites was "dying on its arse." It was hammering the slow query log, pages could take 10, maybe 20 seconds to load. Turned out there were NO indexes at all. Maybe the odd primary key from the default CMS install (which was by no means database efficient). So we sorted that and that sorted out the immediate speed issues. Of course, it went without saying that a poorly constructed schema must be related to a poorly constructed query or two. We were actually wrong. It was more like a query or twenty. The submission of one form was done field by field. There was an UPDATE query constructed for each field and processed there and then, on a form with at least 12 fields. There was a comment above, "We have to do it this way. Trust me." As far as I could tell they didn't know how to dynamically build a query. Further along, different update queries were performed by first deleting the record, and then re-inserting it with one of the fields updated. I never quite got as far as that. There was no comment anywhere about the reasoning for such a strange approach. I think some developers don't bother figuring it out because they think there are better things to do than optimise your code.
- jswinghammer 15y ago
  Is your name a wire reference? If so well done.
- mkopinsky 15y ago
  Nothing builds confidence in a fellow developer like //We have to do it this way. Trust me followed by crap code...
  - jtheory 15y ago
    Hi there! I made mostly random changes until it worked, so I have no idea why this contorted approach fixes the earlier failures. But I spend two weeks on this section, so don't touch it! [this comment removed and replaced with the "Trust me" line, because maybe multi-line comments are breaking things today...]

losethos 15y ago

I had a digital logic class where we had to find minimum logic gate solutions to get truth tables. My classmates fiddled with karnough maps. http://en.wikipedia.org/wiki/Karnaugh_maps http://en.wikipedia.org/wiki/Karnaugh_maps I wrote a program which constructed minimum gate solutions to all possible truth tables, eventually finding the minimum gate solution to the requested problem. ------------ LoseThos has it's own document format, like MS Word. I made a program to generate html from my document format. Sadly, my format supports overlaying columns of text, but my html conversion program does not. The columns of text thing is too inconvenient to be used in practice. ------------ I used table look-ups for my text font rendering. My text can be offset from 8x8 grid position by +-15 pixels to allow for markee or super/sub scripting. Converting formulas to table look-ups can save time, unless the memory reference is not in cache. I made tables for bitpattern reversal, number of set bits, whitespace characters, etc. It used to be faster to use tables than formulas, but it's conceivable you are correct in the age of cache-miss-penalties. It's a sad day when floating-point is faster than fixed-point arithmetic. ------------------ God says... C:\LoseThos\www.losethos.com\text\BIBLE.TXT ad. 16:1 In the seventeenth year of Pekah the son of Remaliah Ahaz the son of Jotham king of Judah began to reign. 16:2 Twenty years old was Ahaz when he began to reign, and reigned sixteen years in Jerusalem, and did not that which was right in the sight of the LORD his God, like David his father. 16:3 But he walked in the way of the kings of Israel, yea, and made his son to pass through the fire, according to the abominations of the heathen, whom the LORD cast out from before the children o

tom_b 15y ago

"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Because the programmers ran all the db access tests against tables with 4 rows of data when the customers would windup with 100 million rows in production. Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice. Because DBA's are (rightly) focused on making sure your db is properly backed up, replicated, and fault-tolerant. They may not even understand SQL queries beyond the basic one table select. Because the users will force data into the systems that the developers never anticipated. Because db jobs are segregated by product for development. If you've done SQL coding on Oracle for the last 10 years, good luck applying for a job doing SQL coding on MS SQL Server. Because SQL hacking doesn't get much respect. I've been in job interviews where deep into the process they ask me a question with data in two tables that requires an outer join. And answering correctly differentiates you significantly from other applicants. I guess it is the "fizz buzz" of SQL these days . . .

endersshadow 15y ago
I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview. I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that doesn't tell you much." Relational databases are oft-misunderstood and underestimated time and time again. Where are the Codds of yesteryear?
- bhousel 15y ago
  There's no reason ever to use a right join. I consider those to be a code smell.
  - sesqu 15y ago
    There's about as much reason to use a right join as there is to use a left join.

pragmatic 15y ago

Because SQL is declarative and few(er) people can wrap their mind around that then straight procedural code. See also Linq, you get some really crazy stuff written in Linq because people don't really understand it.

_delirium 15y ago

Isn't rewriting a stored procedure into multiple queries plus Java code sort of the opposite of what most DB best-practices advocate? Now your query logic is in a mixture of SQL and Java, rather than all in SQL, and you've hidden some of the logic from the database's query planner by moving it into application code. (On the other hand, I've rarely found query planners, even for Oracle, to be as magical at optimizing as one might hope, so maybe that's why.)

kabdib 15y ago
Sure, but I took an unmaintainable stored procedure (with very few comments, by the way) and turned it into something that was small, well-documented, and that ran over two orders of magnitude faster. If this is wrong and goes against "DB best practices" then I guess this answers my question about where the howlers come from. [We had a project under development. At some point, and an utter surprise to the team, an Oracle consultant showed up one day and said, "I'm here to tune your database." "Huh?" And he did. Against a half-built system with a schema we were still designing, he "optimized" our queries and determined buffers sizes and whatnot, and then he went away. Apparently the people in Sales were panicked that we had a database under development that we hadn't yet made plans to tune, so they had hired a guy to come in and fix things. We tossed all of his scripts and rolled back everything he'd broken, and continued coding. Total waste of money. Databases are a weird confluence of the power-mad, the knuckle-draggers, the money-grubbers, and a few techies who know what the heck they're doing.]
astine 15y ago
The performance problem with using multiple queries comes mostly from the network latency and query overhead that happens every time your application needs to make a call to the database. Prepared statements minimize the later and connection pools help with the former. The primary thing to worry about when writing queries is whether the logic behind them is efficient and doesn't for the database to do stupid things or fetch the wrong information. (Like using count( * ) where exists is what you mean.) Paying attention to what your queries will make the database actually do is more important than following any guidelines. At least that's my experience.

InclinedPlane 15y ago

The worst problem I've seen in a similar vein was a build process that involved munging a large (200+ mb) xml file. Someone had written a little JS tool for the purpose, and later the use of it had expanded so that it was being run over 150 times during the build. Since it took about 4 minutes (each time) just to read and save the xml file this meant that the build was now spending around 10 hours every day just munging the xml file. I rewrote the JS bit so that it took in a config file and could do all the processing necessary in a single pass, which only took 4 minutes.

einhverfr 15y ago

"Wow. Why do I find all the really howling bad stuff so close to databases?" I have seen a few of those, but you also have to include not only stored procedures but also table layout etc. Things like "if I don't define my foreign keys, I can use a foreign key to reference rows in EITHER table a or table b!" And then of course when you write sprocs against that mess, you get more messes. The thing is that databases are fundamentally math engines with some extra services tacked on. This means that designing databases is ideally a mathematically sound representation of your data not specific to your application, and the database queries bridge the gap. Most developers don't understand this though and so as the application changes, you get big messes. Now, obviously the above is an ideal which really can't be met in most cases. However the closer one is, generally the more ideal the situation, and most apps are really far from it.

einhverfr 15y ago

"Wow. Why do I find all the really howling bad stuff so close to databases?" BTW, as a counterexample, I saw one developer figure out how any invoices were closed by retrieving millions of them from the db and checking in Perl. All for the want of a HAVING clause.... Needless to say while this worked ok for small amounts of data, it failed with large sets. I've made a few SQL errors in my past that failed on large sets, but fortunately I was working against large sets, and they failed miserably, and we had to figure out why. I got good advice from an expert and fixed the problem. In many ways keeping queries separate, and focusing on readability/code standards helps a great deal I think.