People can write queries that large without formatting? or was that the result of some query generation application?
"I've isolated the bug to a database query"
21–30 of 173 posts
Re: "I've isolated the bug to a database query"
#22Im sure 12 pages of Java code doing this procedurally instead is much more maintainable.
Re: "I've isolated the bug to a database query"
#23Earlier quoted context omitted.
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.
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."
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.
Re: "I've isolated the bug to a database query"
#24I'd love to see an EXPLAIN on that. ;)
* 1. row * id: 1 select_type: SIMPLE table: lots_of_em type: not_good possible_keys: none key: none key_len: n/a ref: NULL rows: googol filtered: 0 Extra: You're screwed, Do not pass go.
This is an old link, but you just reminded me of: http://howfuckedismydatabase.com
Re: "I've isolated the bug to a database query"
#25While we're sharing horror stories... I once encountered a stored procedure that returned HTML in a result set. It literally created the UI of a webpage. It returned several columns of HTML that the app would place in strategic parts of the page. Well, as years went by, the app required a more innovative and web 2.0 UI. Rather than remove the HTML from the sproc, more columns were returned with more HTML, Javascript…
Sounds like Oracle's Portal product?
I would guess Oracles mod_plsql
Re: "I've isolated the bug to a database query"
#26Just so folks know, there are tools that will decompose queries and make nice little pictures out of them. With something like this, you'd have to use it just to get started. Once you've visually decomposed it, you'd physically decompose it by splitting it inside-out. Then proceed to understand and debug inside-outwards. Not fun, but not impossible. Just a huge pain in the ass. Making it more fun would be a database…
I've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?
Re: "I've isolated the bug to a database query"
#27This is kinda impressive.
Re: "I've isolated the bug to a database query"
#28One 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 fiv…
"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 ti…
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?
Re: "I've isolated the bug to a database query"
#29Sorry, I call BS.
I might have just been lucky enough to always work at professional companies and startups where this kind of stuff can never happen.
But something tells me there is no reasonable way an SQL query can grow to these proportions.
Re: "I've isolated the bug to a database query"
#30Sorry, I call BS. I might have just been lucky enough to always work at professional companies and startups where this kind of stuff can never happen. But something tells me there is no reasonable way an SQL query can grow to these proportions.
Why not? It's probably computer-generated. You can actually see the first page, and if you look at it, it uses nested SELECT statements. No one said it was smart, but it certainly is reasonable.