Comment On Reporting All Through The Night

First!

Yayyy!  I made a cross-join!  Yayyy!

Some consultants need their hands cut off to prevent their disease from infecting more systems.  Seriously.

Cartisian Products are a bitch, ain't they!

select

    *

into

    #workingset

from

    products, orders, pageviews

Bah, real men query every table separately then piece it all together in the code!

 

Nothing at all amazing about horribly inefficient joins. Anyone can
(and often does) do that. The only WTF here is that the "consultants"
were believed without question for so long... I mean, was there really nobody with any experience at all who'd look at the DB size and  say "hey, the reports can't be that complex!"

27,210,205,000 rows is a LOT of rows... I mean really.... that is a LOT. How did this happen? Why did it happen? I NEED MORE INFO! LOL!!!

I sure hope Adam got a raise.

As a consultant myself, I have to wonder whose these people are that manage to get hired without a clue. Seriously - is it just because consulting companies are outsourcing themselves to the lowest bidder?

Please wait for the next day. The reporting engine is still too busy to give you more info.

The real WTF here (tm) is that they didn't add more RAM to the system.  Surely every IT guy/gal knows that a reporting engine needs a lot of RAM.

Or, if you want to be picky, you could optimize the query to do "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE} WHERE {FIELD_NAME} LIKE "%Paula%" OR {START_DATE} LIKE '%Brillant%'.

If René Descartes was never born, this would not have been an issue.

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

John Smallberries:

If René Descartes was never born, this would not have been an issue.

bwahahahahahahahahaha.
Btw such l33t consultants should know that theres a tool called 'execution plan' in sql server or explain in postgres etc to realize whats going on with the queries! morrons!

So, my immediate response to this is... WTF costs 100% if this estimated cost was a mere 35%!?

== so the consultants said, the reports are incredibly complex and couldn't possibly be completed in the short time you want.

== the consultants said, the reports are incredibly complex and require a lot of resources to run, so we will run them on their own server.

==  the consultants said, the reports are incredibly complex and will need to be run as a nightly batch job.

== the consultants were not hired. This time, they sought the help of Adam Courtney, who was one of their own.

== the consultants indeed did an amazing feat.

 

the site is great, but reading stories about 'High Paid Consultants' i see an envy...

i want to think that i'm mistaken

 

Anonymous:

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

how about actually doing the inner joins?

Select col1, col2, col 3
from a inner join b on a.c4 = b.c4
inner join c on a.c4 = c.c4
inner join d on a.c4 = d.c4
?
I don't know maybe it's just me but actually have a real join as opposed to the where clauses helps.

John Smallberries:

If René Descartes was never born, this would not have been an issue.

Yeah, not to mention that "I think, therefore I am" crap and mind/body dualism. Screw you Descartes.

I love consultants. <3

I don't see anything unexpected here

Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

 

aaaaarrrrghhh... the googles!!! they do nothing!!

why is people using "where" to join?

"Bait and hook" ... the software is the bait, the servers are the hook.

This is what happens when you hire consultants from "Servers 'R' Us"

 

No, it's because to be a successful consultant you only need to be smarter than your smartest client.  Find a dumb guy who's got good sales and you can suck at his teet for a while.  And this stupidity is bolstered by proper misinformation.  I'll bet this relationship could have continued onward to big iron and week-long batches if Adam hadn't gone and screwed it up.

The irony here is that the consultant who fucks things up and gets paid to come back, multiple times, to fix it at high rates, will inevitably be more successful than one who does it right the first time for cheap.

Last place I worked we had a bunch of servers set up by "guys across the hall" who had their own IT consultancy (we were a software firm).  Their hard work presented us with a) A broken active directory, web and file server b) A seperate mail server, running a beta version of Exchange that ran out (!) while I was working there c) A Novell Netware server, whose sole task was acting as a summarily crappy firewall that left the development server in a "DMZ" d) a tape drive, complete with backup software, that close as we could tell never once backed up anything properly, ever, but was programmed to spit out its tape every morning.

None of these machines had any virus protection, so all of them had viruses.

The only consolation was that we very rarely paid them.

BlackTigerX:

Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

 

aaaaarrrrghhh... the googles!!! they do nothing!!

why is people using "where" to join?

Oh! Oh! Pick me, you must!

People is using "where" to join because no "what" there is!

Anonymous:

So, my immediate response to this is... WTF costs 100% if this estimated cost was a mere 35%!?

QFT.

Not a big WTF here, but you would think that the consultants would at *least* go back and check their query to see if they could speed it up rather than suggesting going to a report server, and then to an over night cron....but maybe that's why I'm not a highly paid consultant.

That's also assuming that the consultants can write basic SQL and recognize that they incorrectly joined a few tables.

This should be an effing axiom.

"If your row count is best represented in scientific notation, you should probably recheck your design."

Even in a situation where you have hundreds of thousands of transactions coming in a day, you need to do the reports, and move on. I worked in a system were we got more than a million data inputs a day in a single table. The solution was to make a new table for every days transactions, and as the tables aged we'd consolidate them into summaries, and remove the originals to hard storage. Smooth as clockwork. Reports never took more than a half an hour to run except at end of year closing, where we had to run a year-end summary, and that barely took an hour.

rbriem:

BlackTigerX:

Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

 

aaaaarrrrghhh... the googles!!! they do nothing!!

why is people using "where" to join?

Oh! Oh! Pick me, you must!

People is using "where" to join because no "what" there is!

ha!

Anonymous:

I've seen this type of stuff before.

This is simplifed of course.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

Off course they got back duplicate rows, so the programmer changed it to be

select unique col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4;

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

Another possibility is that the query accidentally had a cartesian product which was being masked by a union statement. Such errors are very easy to make and would not have been easily noticed in early testing phases. Making such errors is quite understandable and hardly constitutes a WTF. Being unable to find and correct such errors, however, is another matter entirely.

What always makes me nervous is finding a query written by someone else that contains unions and distinct. I start reasoning as follows: either the union / distinct is masking duplicates or it is not. If it is not, I should switch to union all / remove distinct to improve performance. If it is, I should switch and fix to improve performance. Therefore, I should switch to improve performance. Then another part of my brain kicks in and says "dont fix it if it aint broke".

Anonymous:

The irony here is that the consultant who fucks things up and gets paid to come back, multiple times, to fix it at high rates, will inevitably be more successful than one who does it right the first time for cheap.

My son has a mystifyingly successful "consultant" friend who tried to help him repair his laptop ... all he had to do was install Windows. This friend called *me* for advice on how to get it to work.

I pity his customers, and wish I had his mailing list ...

 

Anonymous:

The only consolation was that we very rarely paid them.

That reminds me ... you still owe me ...

When I worked as an intern I was still figuring out the magical join
clause.  I experimented until I understood them fully.  One
of my experiments used a full outer join on two tables, one of which
had ~100,000 records.  While the query ran I went to the gas
station to fill up my car.  When I got back (15 minutes) the query
was just finishing and I had a couple million rows returned. 
Yikes I thought. 



Full outer joins are a bad thing children...

OneFactor:

Another possibility is that the query accidentally had a cartesian product which was being masked by a union statement. Such errors are very easy to make and would not have been easily noticed in early testing phases. Making such errors is quite understandable and hardly constitutes a WTF. Being unable to find and correct such errors, however, is another matter entirely.

What always makes me nervous is finding a query written by someone else that contains unions and distinct. I start reasoning as follows: either the union / distinct is masking duplicates or it is not. If it is not, I should switch to union all / remove distinct to improve performance. If it is, I should switch and fix to improve performance. Therefore, I should switch to improve performance. Then another part of my brain kicks in and says "dont fix it if it aint broke".

Indeed. My rule of thumb is if I find myself using DISTINC or UNION (not ALL), I'm probably doing something wrong, and should re-think my where clause.

By the way, Sybase really needs the SQLServer "join" syntax so where clauses can be less WTF-ery in and of themselves.

Anonymous:

== so the consultants said, the reports are incredibly complex and couldn't possibly be completed in the short time you want.

== the consultants said, the reports are incredibly complex and require a lot of resources to run, so we will run them on their own server.

==  the consultants said, the reports are incredibly complex and will need to be run as a nightly batch job.

== the consultants were not hired. This time, they sought the help of Adam Courtney, who was one of their own.

== the consultants indeed did an amazing feat.

 

the site is great, but reading stories about 'High Paid Consultants' i see an envy...

i want to think that i'm mistaken

Maybe the consultants were on a fixed bid contract and would not get paid for fixing their queries. On a fixed bid contract, your solution to every performance problem is telling the client to buy more hardware or run the job less often. I charge/get paid hourly so my solution to every database performance problem is looking at my queries, staring at the explain plan, and fiddling around till it runs faster. Clients may prefer fixed bid for many reasons though.

I think the bit about the fancy pants consultants comes from the expectation (created by the sales department) that Consultants with high charge out rates will be creating better code than low paid employees. Salesmen often overpromise in order to close a deal. I like to underpromise and overdeliver if I cannot give a good estimate (like Scotty from Star Trek) which is why I'll never be make it as a salesman.

Anonymous:

== so the consultants said, the reports are incredibly complex and couldn't possibly be completed in the short time you want.

.
.

== the consultants indeed did an amazing feat.

 

the site is great, but reading stories about 'High Paid Consultants' i see an envy...

i want to think that i'm mistaken

 

Well, I worked as internal "IT manager" (that meaned "Everyone's slave") and programmer in a place where day after day after day external programmers came to "refine" the "exceptional do-it-all make-a-coffee" management program. Well, consider that it manage ANY aspect of the factory, from bookeeping to production. The server had Win2003 and MySQL.

This just to make you aware of the critical level of this "wonder".....

The wonder wich I'm talking of (it's an industrial application, not an "ad-hoc" solution) had an internal scripting language very similar to basic (and to this point it could also be ok) with "advanced" features to access  databases and modify the native interface of the program.
Some features of this beautiful scripting thing:
- NO functions, only labels, goto and gosub;
- multi-dimensional arrays limited to 3 dimensions;
- an array could had up to 64000 values - IN TOTAL!!! So, you couldn't have had arr(100,100,10);
- no variable types - just numbers (var) and strings (var$);
- proprietary developement environment, 72 cols * 19 rows (no kidding). A single row could have been long up to 400 chars, if i remember well. I finished my right and left cursor keys;
- copy/CTRL-C and paste/CTRL-V? Are you kidding? I don't remember well, but F3 starts block/F5 ends block/F7 paste;
- and, just to make everyone happy, ALL CODE IN CAPITAL LETTERS.

This should have introduced you to the horror.....

When you add to this gems like:

ANGSUPSIN$ = "|"
RIGSUP$="-"
ANGSUPDES$="|"
PRINT ANGSUPSIN$+RIGSUP$+RIGSUP$+RIGSUP$+RIGSUP$+ANGSUPDES$

and so on to make a rectangle (substitute |- with graphical symbols), you begin to get the keep of it.

Some exaples of variables the consultant geniuses used in nearly every program:
A$
XA$
XXXA$
XXAXX$
Naturally, without any comment.

I'll post later some other examples....

Samuele

Hmm... are you sure these reports do not use Crystal Reports?

WTF Batman:

Indeed. My rule of thumb is if I find myself using DISTINC or UNION (not ALL), I'm probably doing something wrong, and should re-think my where clause.

By the way, Sybase really needs the SQLServer "join" syntax so where clauses can be less WTF-ery in and of themselves.

Yes!  that is a great rule of thumb.  Too many people just add "DISTINCT" to the beginning of a SELECT because the results have duplicates to "fix" it.  And 9 times out a 10 a UNION should be a UNION ALL (which is also much more efficient).

That's ANSI join syntax, not SQL Server, by the way.

Alex Papadimoulis:

And with the careful removal of several misplaced joins, Adam had brought the reporting system up again.

What no performance numbers!? "brought the reporting system up again" isn't so great if it still takes 23 hours to run the report. I was expecting to get to the end and read it now takes 5 minutes or something.


DD

sinistral:

Yayyy!  I made a cross-join!  Yayyy!

If you know what you are doing, Cross Joins are a *very* powerful tool.

see: http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

Anonymous:

Anonymous:

All was fine when system was very small, but as data got added the report that had this query ran slower, and slower, and slower.  This is how the SQL was fixed.

select col1, col2, col3 from a, b, c, d where a.c4 = b.c4 and b.c4 = c.c4 and c.c4 = d.c4;

 

how about actually doing the inner joins?

Select col1, col2, col 3
from a inner join b on a.c4 = b.c4
inner join c on a.c4 = c.c4
inner join d on a.c4 = d.c4
?
I don't know maybe it's just me but actually have a real join as opposed to the where clauses helps.

I agree 100%.  Anyone still not using the join syntax should be fired right now.

Anonymous:

When I worked as an intern I was still figuring out the magical join clause.  I experimented until I understood them fully.  One of my experiments used a full outer join on two tables, one of which had ~100,000 records.  While the query ran I went to the gas station to fill up my car.  When I got back (15 minutes) the query was just finishing and I had a couple million rows returned.  Yikes I thought. 

Full outer joins are a bad thing children...

I think I need full outer joins about once per project. Unfortunately, one of those projects was on Oracle 8i. I was young and naive and thought that putting the + sign on both sides of the equls sign would do the trick - ah the bliss of ignorance. I felt so dirty doing a left join and union alling with a right join and copy-pasting the query text. Each time I had to modify the query, I had to change the same crap in two places. Cringe.

I remember asking about upgrading to Oracle 9 which supported full joins and the ansi syntax. Then I learned that they would have to upgrade their unix operating systems to support Oracle 9. Then I learned that there were lots of other older apps running on the same server and that upgrading the OS could make the older apps blow up. Then I learned that upgrading the OS on the test server would be bad because then other apps on other production servers would become out of sync with "the test server". Yup, that's right, there were multiple independent applications on separate production servers all sharing the same test server. Double Cringe. I wonder if this setup counts as a WTF.

OneFactor:

Anonymous:

When I worked as an intern I was still figuring out the magical join clause.  I experimented until I understood them fully.  One of my experiments used a full outer join on two tables, one of which had ~100,000 records.  While the query ran I went to the gas station to fill up my car.  When I got back (15 minutes) the query was just finishing and I had a couple million rows returned.  Yikes I thought. 

Full outer joins are a bad thing children...

I think I need full outer joins about once per project. Unfortunately, one of those projects was on Oracle 8i. I was young and naive and thought that putting the + sign on both sides of the equls sign would do the trick - ah the bliss of ignorance. I felt so dirty doing a left join and union alling with a right join and copy-pasting the query text. Each time I had to modify the query, I had to change the same crap in two places. Cringe.

I remember asking about upgrading to Oracle 9 which supported full joins and the ansi syntax. Then I learned that they would have to upgrade their unix operating systems to support Oracle 9. Then I learned that there were lots of other older apps running on the same server and that upgrading the OS could make the older apps blow up. Then I learned that upgrading the OS on the test server would be bad because then other apps on other production servers would become out of sync with "the test server". Yup, that's right, there were multiple independent applications on separate production servers all sharing the same test server. Double Cringe. I wonder if this setup counts as a WTF.

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS.  If you think you need a full outer join, then you are doing something wrong.  Any JOIN that potentially returns a null in *every* column, including all key columns (albeit on different rows), is a complete mess.   You literally must wrap every column with a COALESCE() function, and any further processing of those results cannot use any indexes since all columns are now wrapped in functions.

Often, a UNION or a clever CROSS JOIN is all that you need to generate the resultset that you *think* might require an ugly FULL OUTER JOIN. 

RIGHT OUTER JOINS should *always* be written a LEFT joins.  no exceptions there, either.

Asking a candidate when to use about FULL OUTER or RIGHT joins is a great interview question; you can immediately tell if they are book smart and just memorized definitions, or if they have actual skill and experience with SQL.

Jeff S:

sinistral:

Yayyy!  I made a cross-join!  Yayyy!

If you know what you are doing, Cross Joins are a *very* powerful tool.

see: http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

If you don't know what you're doing, they're even *more* powerful -- enough to take down an entire reporting server! :)

OneFactor:

What always makes me nervous is finding a query written by someone else that contains unions and distinct. I start reasoning as follows: either the union / distinct is masking duplicates or it is not. If it is not, I should switch to union all / remove distinct to improve performance. If it is, I should switch and fix to improve performance. Therefore, I should switch to improve performance. Then another part of my brain kicks in and says "dont fix it if it aint broke".

Yes, BAD (Broken As Designed) code is tempting to execute (as in kill).

Sincerely,

Gene Wirchenko

Got a problem with something being slow?  Throw more hardware at it!

And don't bother to hire more sysadmins to manage it all... they're expensive just slack anyway.

No, I'm not THAT bitter.  Really.

OneFactor:

I think I need full outer joins about once per project. Unfortunately, one of those projects was on Oracle 8i. I was young and naive and thought that putting the + sign on both sides of the equls sign would do the trick - ah the bliss of ignorance. I felt so dirty doing a left join and union alling with a right join and copy-pasting the query text. Each time I had to modify the query, I had to change the same crap in two places. Cringe.

I remember asking about upgrading to Oracle 9 which supported full joins and the ansi syntax. Then I learned that they would have to upgrade their unix operating systems to support Oracle 9. Then I learned that there were lots of other older apps running on the same server and that upgrading the OS could make the older apps blow up. Then I learned that upgrading the OS on the test server would be bad because then other apps on other production servers would become out of sync with "the test server". Yup, that's right, there were multiple independent applications on separate production servers all sharing the same test server. Double Cringe. I wonder if this setup counts as a WTF.

How many cringes do you need to call it a WTF?  Besides, I think you missed a few cringes up there.  Write it up; send it in.

Sincerely,

Gene Wirchenko

JohnO:

I agree 100%.  Anyone still not using the join syntax should be fired right now.

A little strong.  Some of us are still writing for Oracle 8, after all.

Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS.  If you think you need a full outer join, then you are doing something wrong.

Pshaw.

I'd never touched a database in my life until 12 months ago.  Within the month, I'd learned how to use inner joins to avoid exactly this sort of crap.


It's sad that such stupid people manage to get work in this industry.

JoeyLemur:

Got a problem with something being slow?  Throw more hardware at it!

The thought of throwing hardware at the consultants is an attractive one.

And don't bother to hire more sysadmins to manage it all... they're expensive just slack anyway.

No, I'm not THAT bitter.  Really.

One municipality could not find it in their budget to have a full-time IT person.  They ended having to use a local company for network support.  I do not know the dollar amounts involved, but it would not have taken much to equal a full-time salary, and they would have had a person available for many more hours.  Penny proud, pound (and just plain) foolish.

Sincerely,

Gene Wirchenko

Maurits:

Jeff S:

FULL OUTER JOINS should never be used, or for that matter, RIGHT OUTER JOINS.  If you think you need a full outer join, then you are doing something wrong.

Pshaw.

Disagree?  Do you have an example?

Disclaimer: I started out my career as a professional software
developer (trained in Computer Science) with eight years of
(successful) experience. I have since moved into a consulting firm.



The reality of consulting firms is that knowing how to run a query
analyzer and interpret the results (or horrors, look at the query
itself) isn't the kind of skill that's going to justify the margins
that they need to pay their bills. These days, moving deep technical
work to offshore labor for is a big part of how consulting firms
compete on price. (Considering that consultancies drive a lot of
offshoring, it's natural that they do it themselves.)



The same thing goes for consulting firm employees: technical work
doesn't pay the bills where they work, so it doesn't help people
succeed in the hierarchy (which is very strong in consultancies). The
ability to interview a client team, understand their business
process,  and work with them to write a 100 page specifcation for
a business application is going to be far more useful than the ability
to actually code the app. (which will probably be done in India.).
While this typically implies that the on the ground technical skills of
your consultants aren't what you'd like, aren't you glad your company
isn't paying $200/hour for a SQL skills? (I haven't worked with anybody
where I work now that I'd trust with a copy of Eclpse or even Visual
Basic)



(Of course, if your management is expecting coding miracles (or even
adequacy) out of $200/hour consultants, then you're f***ed anyway...
and for reasons likely to be more significant than most of the code
shown on this website).