- Feature Articles
- CodeSOD
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
whatever happened to the COUNT function in SQL itself?
Admin
<FONT face=Verdana size=2>One word: Brilliant!</FONT>
<FONT face=Verdana size=2>Who's this genius, and where can I hire him/her?</FONT>
Admin
Maybe they were good at cf but they sucked hard at SQL
Admin
This few-queries-instead-one approach is very, very common...
It's also an easiest way to earn some bucks and reputation too (because the performance gain is O(1) and not O(n), so it's usually noticeable), when you correct such faults.
Admin
I haven't had to write any ColdFusion in a few years, but as I recall this is exactly the type of thing that inexperienced CF coders would do. I once attempted to help a coworker debug a 2000 line CF program he wrote. Oh, the humanity.
ColdFusion's big benefit is also it's main problem. It's easy to get started with, thus a lot of crappy code that more or less works gets written.
Sheridan
Admin
A.K.A. - Visual Basic. I know I know, the VB zealot trouncing his own language. I've seen a lot of VB code like this before, where people don't bother to investigate the data source they're accessing to see if there's a better way.
And on that note, I'd like to use this icon because I've never seen anyone else use it: [pi]
Admin
Uh...WTF?
Admin
My CF is sketchy as well, but there's something a bit disconcerting about the "Request.InitechDatabase" reference.
I have no idea where this "team of specialized ColdFusion experts" hails from, but it's a perfect example of how the coder community can be it's own worst enemy. The person who wrote this code is occupying a position that could be held by someone who actually deserves it ;)
Admin
Ugggh so many better ways of doing this...
If CF if all I needed was the record count (not using the query results later) it's typical to just let SQL do it (SELECT Count(*) FROM bookings ....)
If I needed both the count and data there was always the given variable #q2.recordcount# that does the trick as well.
Sad....
Admin
Ladislaus: You misspelled "Brillant."
Admin
[pi]
Admin
Ohh, Paula got a new job apparently...
Admin
Admin
Looks like the developer recently moved from PHP and MySQL. Its the ONLY place where code this stupid is considered industry norm.
Always get references for consultants, and preferably view a system they wrote in action.
Admin
[p]
Admin
hmm? i don't think i've ever seen any php/mysql code that used a loop to count a record set. mysql does have count(), you know... not to mention php has mysql_num_rows, which you get for "free" with the database result.
Admin
SELECT Client_Num, Count(1) FROM Bookings group by Client_Num
Would actually be faster since the * will have to replace each column....
Admin
[3.14159]
Admin
Um, unless I'm missing something wouldn't it be even easier to do something like:
Admin
Doh! I should say that would be easier.
I meant:
select count(*) from bookings
where client_num = '#client_num#'
and bookings_active_indicator = "Y";
It's like he did half a selection???
Admin
OK, here's my version of the equivalent single SQL statement:
SELECT [ActiveBookings] = COUNT(*) FROM Bookings WHERE bookings_active_indicator = 'Y'
But it's kind of hard to tell whether stuff was snipped in the loops, or whether the sole purpose of this code was to populate the bookingsCount variable?
Admin
No. COUNT(*) is nothing like SELECT * other than the fact that they both use the asterisk character to denote something.
Admin
Close.
SELECT Client_Num, Count(*)
FROM Bookings group by Client_Num
WHERE bookings_active_indicator = 'Y'
By the way, I love thecolumn name "bookings_active_indicator" ....
The grouping on client_num depends if there is some code in there that was snipped that displays the count per client. Otherwise, of course, just a single count w/o any grouping would be needed to returned the total.
Admin
Whether or not this is faster is sensitive to database product and version. Many SQL optimizers look for Count(*) specifically and optimize its calculation.
Furthermore, the performance of this query could be substantially affected by the indexing scheme. It is a pretty reasonable guess that Client_Num is indexed. An ordered index could be traversed very easily in order to calculate the counts, but a tree index might have all the counts pre-calculated.
My messagae here is that if this query is important to the performance profile of your application, there is no substitute for benchmarking.
Admin
Alright, people... The Brillant and IsTrue jokes are played out. Know when a good thing is no longer good and let it go. Just let it go...
Admin
Oh! you mean this: [image]?
Admin
But...if you have to do it, at least spell brillant right.
Admin
Actually, it is still only 1 query, but he's shifting the data logic from the database layer to the web layer. That's the wtf as the database layer uses complex algorithms to increase performance, and it doesn't require all of the data to be sent to the web layer then processed.
Admin
I wouldn't be surprised if they used a char(1) for it in addition...
Admin
Seriously, they make it into almost every thread don't they.
Admin
I haven't the slightest idea of what to make of that icon. Very peculiar.
Admin
It's a slice of a pizza pie, whats so weird about that?
Admin
Trust me, I love a pie every bit as much as the next guy. I'm just not sure of what possible context it could be used.
Admin
Initech. Office Space ( http://www.imdb.com/title/tt0151804/ ). Concerned yet?
Admin
count() vs count(column). count() means "count all rows that match the WHERE". count(column) means "count all rows that match the WHERE AND column IS NOT NULL."
SELECT count() FROM table WHERE x IS NOT NULL
is the same as:
SELECT count(x)
Some databases can optimize the count() into an O(1) operation, some always perform a table scan.
Admin
There's also COUNT(DISTINCT x) which counts the number of different non-null values of x across the rowset.
Admin
It can't be that Initech. That one burned down.
Admin
When I read that I figured company names were changed to protect the (arguably not) innocent..
Admin
What's there to be concerned about? Alex is simply using that reference to anonymize the actual company name.
Seriously, do you think there are very many coders that would not know what Office Space is?
Admin
I agree. I think we can start getting some mileage out of "brilant" though.
Admin
This is the first ColdFusion code I've ever seen.. Is this Java-like pseudocode accurately representing what is going on?
<font size="1">bookingsCount = 0;
q1 = "SELECT * FROM Clients";
q1_results = run_query(q1);
for (i = 0; i < length(q1_results); i++)
{
q2 = "SELECT * FROM Bookings WHERE Client_Num = '" + q1_results[i].Client_Num + "'";
q2_results = run_query(q2);
if (length(q2_results) > 0)
{
for (j = 0; j < length(q2); j++)
{
if (q2_results.bookings_active_indicator == "Y")
{
bookingsCount = bookingsCount + 1;
}
}
}
}</font>
Sincerely
Gene Nixon the Great
Admin
thats pretty sad. and they cant be experts on coldfusion either. ever heard of myquery.recordcount ? of course the SELECT COUNT(*) is the best way doing it but if they had little CF knowleged, they would know at least recordcount.
Admin
Bastard! How did you do it? I saw that Ytram tried and got the same results as me.
Stupid forum software, can't even insert a picture of a slice of pizza. Wow, I never thought i'd get a chance to say that. Rubber baby buggy bumper.
Admin
I'll give it a shot too.
Admin
[pi]
C'mon Manni, it's easy! [pi]
If that doesn't work, oh man I'll look like such an ass. I want to vacuum.
Admin
I've got it!! [image][image][image][image][image][image][image][image][image][image][image][image][image][image][image][image][image][image][image]
Admin
I'm just wondering if Alex is too scared to say "Brillant was my typo, Paula actuall got it right"
Admin
I hate you all. Yes you looked like an idiot at first, only partly due to the fact you thought you were displaying pizza icons, but you've slightly redeemed yourself. Should I put the IMG tags in manually then? Screw that, then I'll look like the idiot that can't make pizza on the screen, and whose messages look like <img src="lbahbhla"> because the forum software will undoubtedly convert my tags to plain text.
Maybe you can do those icons, but look what I can do! *jumps in the air* I can do it way better than Stuart.
And about the Initech stuff...if this is the first article you've read on the The Daily WTF, Alex (the forum administrator and site owner) anonymizes the code by replacing company names with "Initech". Why? Because it's a reference from "Office Space", a movie about how inept corporate America is when it comes to software development, employee morale, and proper managerial structure.
You've been warned. The next "warning" will be an aluminum bat to your knees. Try ice skating in the Olympics after that.
Admin
Hmm, I like [image]
Admin
Actually all I did was quote the guy that got it working right, and then I just cut and pasted his pizza [:P]
Of course, from now on, I'll have to return to this thread and cut and paste and see if it works across threads.
When the moon hits your eye
Like a big [image][image][image][image][image][image][image][image]
That's amore...
(8 slices = one pizza pie)