Comment On The Magical Mystery Report

Sad, kind of like when King Kong died.

Could this query be an example of "machine generated" code? It looks like an awfully complex monster. Or maybe it grew and evolved?

R.Flowers:

Sad, kind of like when King Kong died.

Could this query be an example of "machine generated" code? It looks like an awfully complex monster. Or maybe it grew and evolved?

Looks more like de-evolved.

I wonder - what all thos inserts were doing.. and if they'll be missed..

Reminds me of another time when I was asked to export data into Clipper from Oracle - run a loop to remove unwanted records, and re-import the results back to oracle... because oracle was too slow.

I ended up rewriting the deletes where clause so it took less  than a second.. all was well.

Ahhhh, this sounds similar to a report I had to help run - except it involved a Mail Merge, some Access tables that macro'ed an Exchange public folder, and a half dozen other pieces of voodoo. Lovely thing it was.

Reminds me of an old product called Case Tools - if you're familiar with the code it spit out, I'm sorry.

Wow...complicated. Amazing that The Report managed even to run with that crud...

Oh, come on now.... those function calls are somewhat analogous to the lazy-coding style:

if (func(x) > func(y))

   then return func(x);

   else return func(y);




...as opposed to using temp variables, and, we all know that using simply:

3

is far less efficient than computing, say:

((int)sin(90)+ (int)sin(90))^3 / 2 - (int)sin(90)

*winks*

 

Seeing the function snippet literally made me say "OUCH!", to think that it's still just a small portion...

I feel for the server that took this kind of torture...

R.Flowers:

Sad, kind of like when King Kong died.

Could this query be an example of "machine generated" code? It looks like an awfully complex monster. Or maybe it grew and evolved?

I've never heard of a machine generating a cursor.  It just goes to show, that if you can't force SQL server to do a table scan, do one manually with a cursor.

I bet this report used to run off of flat files (or excel files) instead of database tables, so the report designer had to 'manually' relate the files.  When they moved into SQL, the idiots simply translated it into T-SQL from the VBA or whatevor.

They should have just used Visual FoxPro.

Alex Papadimoulis:

 

DECLARE t CURSOR FORWARD_ONLY FOR 

At least they used a FORWARD_ONLY cursor.  That surely sped things up...

Anonymous:

Oh, come on now.... those function calls are somewhat analogous to the lazy-coding style:

if (func(x) > func(y))

   then return func(x);

   else return func(y);




...as opposed to using temp variables,

Hey, if you're using a proper functional language, it'll cache the return values of func(x) and func(y) for later use, and you don't need to use such counterintuitive things as temporary variables.

It took me a while to get what was happening, but I think I get it now. It's like a 'view' as written by chimps.

This "fnc_GetCojobTb(X)" function clearly takes an integer and then appearantly returns some rows that link a job code to a job id. The last little bit of evil code in the post reads every job code and for each one, pulls the first 5 job ids associated with that code, and puts them into a temp table of some kind. Then it likely selects from that temp table to return the relevant rows.

The whole function was probably replaced with a single select with one join, or better yet a view. Or even better, the calling statements were rewritten properly and the function was eliminated entirely.

Anonymous:

Anonymous:

Oh, come on now.... those function calls are somewhat analogous to the lazy-coding style:

if (func(x) > func(y))

   then return func(x);

   else return func(y);




...as opposed to using temp variables,

Hey, if you're using a proper functional language, it'll cache the return values of func(x) and func(y) for later use, and you don't need to use such counterintuitive things as temporary variables.

Not all language compilers can trace through function calls to all depths - think about side affects (agreed: they shouldn't be coded like that, but alas, frequently are)

Ah, brillant

I challenge anyone to write a non-WTF-ish T-SQL query that uses cursors

Otto:

This "fnc_GetCojobTb(X)" function clearly takes an integer and then appearantly returns some rows that link a job code to a job id. The last little bit of evil code in the post reads every job code and for each one, pulls the first 5 job ids associated with that code, and puts them into a temp table of some kind. Then it likely selects from that temp table to return the relevant rows.

thank you! i dont know what it is, but SQL tends to turn my brain to jelly... i was reading it... thinking ... looks ...  complicated .... don't ... get ... it .... w..tf... is ...the ...

Reminds me of several instances where I (as a function-oriented programmer... someone else's label) created SP's that used multiple nested cursors to do something that was later rewritten using two queries... I've learned a lot since those days... unfortunetly that was last month....

Alex Papadimoulis:

No one had a good explanation for why The Report acted this way, nor did they want to find out. It was The Policy: questioning The Report might upset it; if The Report got upset, it might not run; and if The Report didn't run, its users would be deprived its bountiful data. The Report was good to its users most of the time, and no one wanted to change that. That is, no one, except Steven Dargal.

How many virgins had to be sacrificed daily to please The Report?

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

BiggBru:

How many virgins had to be sacrificed daily to please The Report?

72.

BiggBru:

Alex Papadimoulis:

No one had a good explanation for why The Report acted this way, nor did they want to find out. It was The Policy: questioning The Report might upset it; if The Report got upset, it might not run; and if The Report didn't run, its users would be deprived its bountiful data. The Report was good to its users most of the time, and no one wanted to change that. That is, no one, except Steven Dargal.

How many virgins had to be sacrificed daily to please The Report?

The function to calculate that quantity was embedded in the posted function - unfortunately, by the time it finished the calculation, they weren't virgins anymore...

The server that had that code on it should be washed thouroughly with bleach, and sent to Yucca Mountain to be buried for the next 250,000 years, or until the next epoch, whichever comes last.  The developer that wrote the code should be sent to maintain the server on site and in person.

<shudder>

Volmarias:

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

It is using a function to call a cursor to return a table with 5 rows.  And being called many times.

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

qqqqqq:

Anonymous:

Anonymous:

Oh, come on now.... those function calls are somewhat analogous to the lazy-coding style:

if (func(x) > func(y))

   then return func(x);

   else return func(y);




...as opposed to using temp variables,

Hey, if you're using a proper functional language, it'll cache the return values of func(x) and func(y) for later use, and you don't need to use such counterintuitive things as temporary variables.

Not all language compilers can trace through function calls to all depths - think about side affects (agreed: they shouldn't be coded like that, but alas, frequently are)

"think about side affects"

In a _functional_ language, there _are_ no side effects. You're thinking of imperative languages.

http://en.wikipedia.org/wiki/Functional_programming

Anonymous:

Volmarias:

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

It is using a function to call a cursor to return a table with 5 rows.  And being called many times.

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

I've seen queries in procs that select a data subset into a dynamically generated temp table, then select everything from the temp table, then trash the temp table, then repeat it in the center of a deeply nested series of subqueries - I swear, the lights would dim and you could hear the disk motors straining to keep up

Anonymous:

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

Are you challenging us?  :)  Something involving a digital camera, a wooden table and a scanner comes to mind...

merreborn:

qqqqqq:

Anonymous:

Anonymous:

Oh, come on now.... those function calls are somewhat analogous to the lazy-coding style:

if (func(x) > func(y))

   then return func(x);

   else return func(y);




...as opposed to using temp variables,

Hey, if you're using a proper functional language, it'll cache the return values of func(x) and func(y) for later use, and you don't need to use such counterintuitive things as temporary variables.

Not all language compilers can trace through function calls to all depths - think about side affects (agreed: they shouldn't be coded like that, but alas, frequently are)

"think about side affects"

In a _functional_ language, there _are_ no side effects. You're thinking of imperative languages.

http://en.wikipedia.org/wiki/Functional_programming

I'll be damned - I learned something from TDWTF - thanks :)

Anonymous:

Volmarias:

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

It is using a function to call a cursor to return a table with 5 rows.  And being called many times.

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

Thanks!

Yeah, I'll bet though that it was added after the fact at an attempt to 'optimize' the report

Anonymous:

Volmarias:

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

It is using a function to call a cursor to return a table with 5 rows.  And being called many times.

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

On top of the other two fine suggestions above, you could remove all PKs and de-index all index fields. And for the king of slow, don't forget infinite recursion. (Granted, that's a bit pathological, since it will never actually return a useful value.)

Anonymous:

Volmarias:

I have NO idea what's going on in this code. Can someone please step us through it? Otto's summary was nice, but I want to know just why this is a wtf.

It is using a function to call a cursor to return a table with 5 rows.  And being called many times.

There are many other ways of doing this.  All are faster.  I can't think of a slower way.  Anyone?

WAITFOR DELAY '05:00' ? :)

In SQL Server, this is a "table valued function" i.e. a function that returns an object that behaves like a table, can be SELECTed from, JOINed to etc.

It's not really that WTF. Yes, the cursor is WTF, but for 5 rows, kinda WTF-lite. When it's used in the FROM section of the SQL query as a substiture for a "real" table, it only gets evaluated once.

A real WTF (and I expect the part we weren't shown) is when a function containing a cursor is used in the SELECT part of the SQL query, where the function needs to be re-evaluated for every row in the resultset.

It really is a pity that so many SQL programmers don't know WTF derived tables are & how to use them.

 

So, where's the bit where the user now don't trust The Report cuz it runs too fast, and there's no way that it would generate real data that fast - and what's his name had to put in a sleep() call in there to fake it?

Just curious, do rdbms' have the query-equivalent of file-not-found?

 

Bah.

To me SQL is like HTML. Its not programming at all.

I really think is unteresting how your ORDER BY your socks.

--Tei

Alex Papadimoulis:

Although I'm sure that the SQL Developers out there will appreciate the code a bit more, suffice it to say that Steven was able to replace the view and all of its sub-views and functions with a much simpler query.

 

Please oh PLEASE post the replacement query!

Somewhere, deep in the guts of the UK National Air Traffic Services
processing system (the guys responsible for routing and timetabling),
there's a COBOL program which creates several dozen temporary tables in
the course of its activities.

<br/>

While working on a summer placement at Uni, they wouldn't let me 'fix it'.  I guess it was their 'The Report'.

<br/>

Being COBOL, and an UK government system, it's possible it was written
before SQL was standardised, CRTs were invented, or Edison invented the
lightbulb.

Anonymous:

Somewhere, deep in the guts of the UK National Air Traffic Services processing system (the guys responsible for routing and timetabling), there's a COBOL program which creates several dozen temporary tables in the course of its activities.
<br/>
While working on a summer placement at Uni, they wouldn't let me 'fix it'.  I guess it was their 'The Report'.
<br/>
Being COBOL, and an UK government system, it's possible it was written before SQL was standardised, CRTs were invented, or Edison invented the lightbulb.

Soooo, the tables were really bowls of rocks, and the computer was a monkey playing towers-of-hanoi with the data ?

Otto:

It took me a while to get what was happening, but I think I get it now. It's like a 'view' as written by chimps.

This "fnc_GetCojobTb(X)" function clearly takes an integer and then appearantly returns some rows that link a job code to a job id. The last little bit of evil code in the post reads every job code and for each one, pulls the first 5 job ids associated with that code, and puts them into a temp table of some kind.

It's actually 5 more-or-less random job ids, since there is no sort. It's just possible that selecting 5 random job-ids was part of the spec. However I suspect that once upon a time there was only one job id per code. Then along came a code with 2 job ids, and someone wrote a predecessorof this mess to cope with 2. Then along came a case where there were 3 ... and the most recent person to visit the code thought that there would be no more than 5. Ugh.

The segment of sql from the function correlates job ids for a given job code where the job code is (i assume) not marked as deleted (not entirely sure what del_ind is but thats a reasonable assumption).  There are no other constraints placed on this query so I can also assume that there are never more than 5 jobs per job code.  The same resultset can be generated from:

SELECT job_cd, job_id FROM cojobs INNER JOIN jobs ON cojob.job_cd = jobs.job_cd WHERE del_ind = 0

Without seeing the entire query, there is a better than good chance that all of the inner joins using the function can be replaced with a well constructed set of joins and where clauses.

The WTF here is using row by row processing (a cursor), that can be replaced with a (much simpler and more efficient) query, when relational databases are highly optimised for set based processing (SQL is a set based language), and that this row processing was done 11 times for EVERY row in the view.

(display "Yeah, sure, no side effects.")

Until you are doing anything with your code, like, say, input/output...

qqqqqq:

Just curious, do rdbms' have the query-equivalent of file-not-found?

 

 

There are no stupid questions, only stupid people who ask questions...

  I once had the fun job of rewriting a set of stored procedures, and was able to replace a cursor with a couple of selects using temp tables. Sped one sp up by a factor of 30.

 

I'm guessing that the many calls to that function, passing in different values as arguments, is done to try to "crosstab" the data (i.e., summarize many rows into many columns).

T-SQL is amazing ... In the system I am dealing with now, the guy who wrote the SQL did not know any of the following:

* Derived tables
* Left outer joins
* UNION

Ah, but what do they know?

* Temp Tables
* Cursors
* Dynamic SQL

They made good use of the "temp table cursor-fill then update technique", something well beyond my T-SQL skills.  You see, I just write a SELECT that joins the tables as needed and returns the results.  However, someone with advanced knowledge of SQL knows that the better way is to:

1) create a  huge temp table
2) use a cursor to fill up columns that come from one table
3) write UPDATE statements that update the temp table with related data from other tables (i.e. to avoid those messy joins)
4) repeat 1-3 as  necessary
5) combine everything using another cursor into another temp table
6) return the final temp table


I just replaced a 500-line stored procedure with a 26-line select.  Only my 26 lines now have some comments.

Anonymous:

Bah.

To me SQL is like HTML. Its not programming at all.

I really think is unteresting how your ORDER BY your socks.

--Tei

Blasphemy!  Dirty troll.

SQL is the art of thinking about sets unlike (filthy imperative) row based programming.

 

-- Stretch your mind to encompass the superset

Anonymous:

Bah.

To me SQL is like HTML. Its not programming at all.

I really think is unteresting how your ORDER BY your socks.

--Tei

They fall under the category of Declarative programming

But when you include stored procedures the distinction gets very blurry.

>>The same resultset can be generated from:

>>SELECT job_cd, job_id FROM cojobs INNER JOIN jobs ON >>cojob.job_cd = jobs.job_cd WHERE del_ind = 0

Aha, but it's not the same resultset, is it ? It's not guaranteed to have exactly 5 rows and it doesn't have an ascending sequence number column both of which the table-valued function provide. And who knows what dependencies exist on those requirements.

 

Anonymous:

qqqqqq:

Just curious, do rdbms' have the query-equivalent of file-not-found?

There are no stupid questions, only stupid people who ask questions...

Um, some of us just don't really work with databases and/or sql.... (in case you're not a regular around here, 'file-not-found'  is kind of a running joke)

Anonymous:

>>The same resultset can be generated from:

>>SELECT job_cd, job_id FROM cojobs INNER JOIN jobs ON >>cojob.job_cd = jobs.job_cd WHERE del_ind = 0

Aha, but it's not the same resultset, is it ? It's not guaranteed to have exactly 5 rows and it doesn't have an ascending sequence number column both of which the table-valued function provide. And who knows what dependencies exist on those requirements.

Well, there's another thing. If I read this correctly, it fetches up to five ids and then inserts exactly five ids. And if there are less than 5 ids per cd, it appears to reinsert some ids from a previous cd.

I'm glad I don't know SQL well enough to write something as bad as this.

Maybe it will.  But the idiom

if(func(x) > func(y))
    then return func(x);
    else return func(y);

is kind of dorky because if return func(x) ever executes, then there's never a chance to return func(y), rendering the else redundant.

What's truly scary is that this particular construct shows up in the original K&R, and you'd think those guys would know better, even in 197x.

Of course, if you're using one of those massively silly languages that does logical blocking by indentation instead of bracing symbols or commands (as God intended), then you have to write something like this, and you have my condolences.

Anonymous:

>>The same resultset can be generated from:

>>SELECT job_cd, job_id FROM cojobs INNER JOIN jobs ON >>cojob.job_cd = jobs.job_cd WHERE del_ind = 0

Aha, but it's not the same resultset, is it ? It's not guaranteed to have exactly 5 rows and it doesn't have an ascending sequence number column both of which the table-valued function provide. And who knows what dependencies exist on those requirements.

Actually, if I read the code right, the table-valued function has up to five rows; and you can get the same behavior with a TOP clause in most databases I've worked with (or a FETCH FIRST <n> ROWS ONLY clause in the last version of DB2 I used).

The sequence number is a little trickier, if you have an old SQL implementation.  A current one (that includes the OLAP functions) should allow you to put ROW_NUMBER() OVER() or some variation thereof in your select list.