News for hackers. Stuff that matters.
Re:"I've isolated the bug to a database query" by _delirium on Wednesday November 09, 2011 @11:48AM (#3216207)
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.)

Re:"I've isolated the bug to a database query" by kabdib on Wednesday November 09, 2011 @12:14PM (#3216347)
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.]

Re:"I've isolated the bug to a database query" by skittles on Wednesday November 09, 2011 @02:21PM (#3216962)
You are assuming that the Java code is query logic instead of pure business logic. Many people try to do too much in SQL.
Re:"I've isolated the bug to a database query" by astine on Wednesday November 09, 2011 @12:29PM (#3216422)
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.

Re:"I've isolated the bug to a database query" by einhverfr on Thursday November 10, 2011 @03:04AM (#3219372)
I'd agree. I'd also add that well written stored procs are often more readable than well written ad hoc queries.

The longest query I have ever written I thought was approaching unmaintainability at about 100 lines, but I can sit down and digest it without too much effort. I have however spent a week debugging a query that was three times that long. I think the time to understand starts increasing with the square of query length...... 100 lines may not take too long to understand, but 200 lines takes 4 times as long, 1000 lines takes 100 times as long, etc.....

At least my experience is that even well written SQL starts becoming a bear really fast when over 100 lines ling.

Re:"I've isolated the bug to a database query" by protomyth on Wednesday November 09, 2011 @06:17PM (#3218003)
Some databases really do loose their mind when the query contains many tables. Sybase was particularly a pain and pretty much required breaking up big queries. Sybase's optimizer for version 12.5 and earlier (cannot speak after that) often required forcing indexes and plans in stored procedures.