Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

"I've isolated the bug to a database query"

thedailywtf.com

Post reply on HN
Print view View original

161–170 of 173 posts

Re: "I've isolated the bug to a database query"

#161

Post by LewisCr »

TruthElixirX wrote: post #151
tom_b wrote: post #19

Earlier quoted context omitted.

"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…

"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." I am taking a database management class this semester. Its awful. We spent over half the semester drawing diagrams. Just last week we started working with actual SQL.

Have you been to DB-Class.org? It might make a good supplement to your real-life class.

Re: "I've isolated the bug to a database query"

#162

Post by salvadors »

protomyth wrote: post #64

A lot of the problems I have seen with queries (other than DBA issues) is the conflict between application developers and report writers. A lot of databases are designed for transactions and resources are not often available to do a proper reporting database or at least summary data. I have a very simple rule for myself - "if a user of the application is concerned about a certain attribute or state an element (e.g. p…

Is your point here that the data being stored is insufficient (e.g. you'd want an end date, not just an active flag; this doesn't cope at all with prices changing over time, bulk discounts, or different customers paying different rates; there's no concept of invoices, or whether payment is due based on calendar months or based on opening date; etc) or that you're ignoring all that sort of stuff just to keep the example simple (so assume everyone pays a fixed rate per unit, due weekly; someone can't close their account until they're paid up; etc) but that you'd still want a more complex schema so as to be able to more easily generate a "Who owes us money?" report?

If it's the former, then sure: you need to be able to model all these things properly. If it's the latter, then I'm not so sure. The SQL to create that sort of report is going to be non-trivial, but it shouldn't be overly complex for someone who knows what they're doing, and if you have the correct indexes it shouldn't take very long to run either.

If you want to start doing all sorts of fancy data warehouse slicing and dicing, you're usually better extracting daily (or more/less frequent depending on needs) dumps of your transactional database into a different structure more suitable for reporting, than in restructuring your 'live' database and having to deal with all the resulting denormalisation issues, etc.

Re: "I've isolated the bug to a database query"

#163

Post by gaius »

andymurd wrote: post #140
matwood wrote: post #57

Earlier quoted context omitted.

I've never understood the typical programmers aversion to query writing. Writing SQL is in general pretty easy (basic set theory). I'm guessing it's all about what people have experienced. I have been coding against RDBMSes since my first job while still in college. I have even read Celko for fun at one point :) IMHO, learning the hard parts of SQL only come from experience with particular RDMSes and dealing with lar…

One of the biggest hurdles that typical programmers must overcome is that SQL is a declarative language, and therefore quite different to the imperative languages they are used to.

Imperative programmers all secretly believe in a declarative style, that's why they compile with -O and hope the compiler's "query optimizer" will make it fast for them.

Re: "I've isolated the bug to a database query"

#164

Post by gaius »

funkah wrote: post #112
pork wrote: post #59

Reading the comments below, I get the impression that all the "good" DB people hang out on HN, not like those "other" incompetent nits out there who don't know what a join is. Hubris, people.

Maybe HNers are just better at keeping their mouths shut when they don't know anything about a topic.

I guess you're new around here ;-)

Re: "I've isolated the bug to a database query"

#165

Post by gaius »

lyudmil wrote: post #82
tom_b wrote: post #19

Earlier quoted context omitted.

"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 have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at. On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think…

The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL

Sigh... This again. Out here in the real world, we have databases with a hundred apps connected, with tens of thousands of concurrent users, and these apps are written in a dozen or more languages, there's COBOL, VBA, C++, Java, Python... There's green screens, desktop apps, websites, embedded devices...

The ONLY way to have consistent, enforced business logic, from ANY application in ANY language (even people executing SQL directly) is to do it in stored procedures. Doubles the number of places? Your way is 100x the number of places. Little websites with one application running on one database are not really representative of serious database use.

Re: "I've isolated the bug to a database query"

#166

Post by lyudmil »

gaius wrote: post #165
lyudmil wrote: post #82

Earlier quoted context omitted.

I have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at. On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think…

The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL Sigh... This again. Out here in the real world, we have databases with a hundred apps connected, with tens of thousands of concurrent users, and these apps are written in a dozen or more languages, there's COBOL, VBA, C++, Java, Python... There's g…

The ONLY way? I call bullshit on that too. Databases like the one you mentioned exist, but I still think if you have business rules you want to enforce across all applications connected to a database, you wrap that in a service and let the applications call the service. Clearly stored procedures aren't the ONLY way.

Re: "I've isolated the bug to a database query"

#167

Post by gaius »

lyudmil wrote: post #166
gaius wrote: post #165

Earlier quoted context omitted.

The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL Sigh... This again. Out here in the real world, we have databases with a hundred apps connected, with tens of thousands of concurrent users, and these apps are written in a dozen or more languages, there's COBOL, VBA, C++, Java, Python... There's g…

The ONLY way? I call bullshit on that too. Databases like the one you mentioned exist, but I still think if you have business rules you want to enforce across all applications connected to a database, you wrap that in a service and let the applications call the service. Clearly stored procedures aren't the ONLY way.

You're seriously proposing going back over 25+ years of applications and rewriting them all to talk to a "service" instead of the database? Over what protocol, CORBA, DCOM, SOAP, WSDL, some new thing? And what about third party apps that connect to the DB, have the vendors rewrite them too, to talk to your whizzy new "service"? And what language is your service written in, the fashionable one du jour, that in 10 years your successors will sneer at as "legacy" anyway? And it's as reliable and high-performance as your most mission-critical application, since now it's a single point of failure and a bottleneck?

Ho ho ho. Website experience doesn't translate to real database work.

Re: "I've isolated the bug to a database query"

#168

Post by lyudmil »

gaius wrote: post #167
lyudmil wrote: post #166

Earlier quoted context omitted.

The ONLY way? I call bullshit on that too. Databases like the one you mentioned exist, but I still think if you have business rules you want to enforce across all applications connected to a database, you wrap that in a service and let the applications call the service. Clearly stored procedures aren't the ONLY way.

You're seriously proposing going back over 25+ years of applications and rewriting them all to talk to a "service" instead of the database? Over what protocol, CORBA, DCOM, SOAP, WSDL, some new thing? And what about third party apps that connect to the DB, have the vendors rewrite them too, to talk to your whizzy new "service"? And what language is your service written in, the fashionable one du jour, that in 10 year…

I'm not proposing anything. My point is that there are other options.

As to the rest of your response, I don't mean to be rude but you're simply rambling. Obviously with a legacy system you work with what you've got, but the point is what you would choose if you had a brand new system and needed to make the call today. Choice of language depends on system requirements as we all know.

I really feel uncomfortable about all this posturing. I'm all for discussion, but if you honestly consider me a moron for saying what I have, you shouldn't have any interest in responding. Participating in a discussion merely to dismiss (and disparage) the other party is pointless and boring. Down-vote and move on.

Re: "I've isolated the bug to a database query"

#169

Post by gaius »

lyudmil wrote: post #168
gaius wrote: post #167

Earlier quoted context omitted.

You're seriously proposing going back over 25+ years of applications and rewriting them all to talk to a "service" instead of the database? Over what protocol, CORBA, DCOM, SOAP, WSDL, some new thing? And what about third party apps that connect to the DB, have the vendors rewrite them too, to talk to your whizzy new "service"? And what language is your service written in, the fashionable one du jour, that in 10 year…

I'm not proposing anything. My point is that there are other options. As to the rest of your response, I don't mean to be rude but you're simply rambling. Obviously with a legacy system you work with what you've got, but the point is what you would choose if you had a brand new system and needed to make the call today. Choice of language depends on system requirements as we all know . I really feel uncomfortable abou…

It depends on many factors, "system requirements" is so vague as to be meaningless. Conciseness of expressing the algorithm is one, sure, so is the need to interoperate with other systems, both bespoke and off the shelf, so is the existing skillset of the organization and the talent pool it hires from.

It must be nice to only work on greenfield projects, but as I keep saying, it is not experience that transfers to large scale development efforts, which span decades and continents. If you spread this kind of misinformation, you create more problems than you solve, when the "business logic" is in a hundred places and no-one knows what bit of the system does what. Sure SQL isn't the perfect language, but it is the de facto lingua franca. And there is no "the app" for the business logic to live in. There are a hundred apps, each of which is a piece of "the app".

And if we're pointing fingers, it was you who asserted that a DBA who sees the big picture (funny how every developer thinks theirs is the only app in prod, when they are really in a cast of thousands) doesn't know how to do their job. Show a little humility and you will learn.

Re: "I've isolated the bug to a database query"

#170

Post by protomyth »

salvadors wrote: post #162
protomyth wrote: post #64

A lot of the problems I have seen with queries (other than DBA issues) is the conflict between application developers and report writers. A lot of databases are designed for transactions and resources are not often available to do a proper reporting database or at least summary data. I have a very simple rule for myself - "if a user of the application is concerned about a certain attribute or state an element (e.g. p…

Is your point here that the data being stored is insufficient (e.g. you'd want an end date, not just an active flag; this doesn't cope at all with prices changing over time, bulk discounts, or different customers paying different rates; there's no concept of invoices, or whether payment is due based on calendar months or based on opening date; etc) or that you're ignoring all that sort of stuff just to keep the examp…

As stated, I believe all the information needed to generate the report is there. My point was that what is sufficient for an application is generally insufficient for basic reporting. So, when budgets get short, the database work to make queries by reporting easier is often ignored and complicated queries and processes become the norm. Those laying the problem solely at the feet of the DBA's are missing the other group that tends to make these queries necessary: App Developers. I once had an app developer tell me one type of invoice was impossible to make because some of the relationships and data were intrinsic to the application and would need to be modeled the same as the application. He wasn't far off.

There is a point between transactional and data warehousing that needs to be hit. Simple summaries or considering the question of "how do I retrieve everything in this state" will sometimes suffice. Building a system to get single transactions in an out tends to make routine report take all night or be impossible to get in a reasonable amount of time.

If your schema and app requires non-trivial SQL, then expect maintenance nightmares and lack of ability to scale.

Post reply on HN