- 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
And, last but not least : using PreparedStatement for no real reason, since it barely makes a difference in this case, where it's going to be only used once anyway.
Admin
To limit the amount of data that is prepared, try putting TOP in the query, since you are only looking to see if the table has any records, not what is in them. SELECT * FROM ... means that the DB engine goes through every field of every record in the table, prepares the data and presents it to the client application, which then reads one record (hopefully) and closes the connection. That is a lot of work for nothing. Putting a TOP clause at least lets the engine know that it doesn't have to examine every record in the table, but can stop after reading the first few. "SELECT TOP 1 * FROM some_table" This should return 1 record (depending on how the primary key is set up, it could be any record), but at least you know that it is not empty.
Admin
select * ... is often much faster than select count(*) ... Depending on type of database and version and whatnot. Just saying.
Admin
[quote] SELECT * FROM ... means that the DB engine goes through every field of every record in the table, prepares the data and presents it to the client application, which then reads one record (hopefully) and closes the connection. [quote] That's not how it works in most database engines.
Admin
Too bad we don't learn why empty tables are interesting to the application. However the TWTF are some suggestions:
However: Probably ignoring transaction isolation (what is an empty table in this context ?), not reusing prepared statement objects and best of all ignoring any SQL exception (they are only printed, not raised ) gives a simple solution: public boolean isTableEmpty() { return false; } It shouldn't matter with this exception handling.
Admin
"SELECT true FROM table LIMIT 1" I'd say.
TRWTF is that there doesn't seem to be any universally-accepted syntax for this simple feature: https://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows
Admin
There is (at last) in sql2008: select [column_list] from [table] fetch first [n] rows only;
Admin
I use select distinct 'x'
Admin
"Too bad we don't learn why empty tables are interesting to the application."
I'd like to imagine it's because someone said 'hey, we should have a function for checking if a table is empty, so that you don't have to connect to the database and check every time, that'll save on database resources', and then someone implemented it by doing exactly the same thing, badly, twice.
Admin
On top of being verbose/ugly, the standard syntax "fetch first n rows only" seems to be less commonly supported than "limit n", as far as the number of engines is concerned.
Admin
True, although most major databases support the ansi standard nowadays: db2, oracle, postgress, not sure about sqlserver though.
Admin
In the Oracle world, it is fairly standard to do a "SELECT x FROM y". "SELECT Count(*) from y" does require a table scan or index scan, but just pulling the first row does not.
Admin
I should explain that I am referring to using a cursor within PL/SQL:
OPEN my_cursor; FETCH my_cursor INTO my_throwaway; my_retval := my_cursor%FOUND; CLOSE my_cursor;
RETURN my_retval;
What the client library might be doing behind the scenes, who knows? If it does an aggressive pre-fetch, then yes, LIMIT or TOP would save work.
Admin
Was it just an oversight, or does the method really not accept a name as a parameter? Does it actually have one particular table name hardcoded?
Admin
The real question I have is "why does it matter?"
Admin
Most efficient is "Select case when exists (Select * from table) then 1 else 0 end" but yes this method is a giant WTF in every way.
Admin
There is a typo in the code. A closing curl brace is missing before the first catch statement.
Admin
This function is also terribly formatted, which hides the fact that it's missing a catch for the outermost try, and it's missing a closing curly brace: }
Admin
Since the SQLExceptions are swallowed a false result actually means the table is empty OR we had a SQL failure. Depending on what the caller of this function does with the knowledge that could be a pretty scary outcome. E.g.
LoadExpensiveTempFileIntoTable(); if (isTableEmpty() ) DeleteExpensiveTempFile(); // true== table not empty -> upload succeeded. Temp not needed anymore. Return SUCCESS; // Oops.
As a separate WTF I'm not sure what Remy means about exceptions as flow control. In the no-error path, regardless of table empty or not no exceptions fire. Putting the conn.close() in a finally is exactly what finally is designed for. Although as Jeremy points out just above, there's a missing outer catch and/or finally in the sample. So the issue Remy is talking about may be in the code we don't see.
Admin
Once? I bet it's called in a loop somewhere!
Admin
MS SQL, MySQL and SQLite don't support the ansi syntax AFAICT, that's a big dent in the "most major dbs" qualifyer.
Of course it depends on the kind of projects they usually work on, but I'd say most developers will encounter a "limit n" db more often than a "fetch first n rows only" one.
Admin
Best WTFery in a long time! "Is the table empty?" "Yes, it is. So, FALSE!"
I once worked on a system where an empty table contained -1 in the Company field. When you added a record, it would first "DELETE FROM
WHERE COMPANY=-1" and then "INSERT INTO TABLE ....". No provision for what to do when the last row was deleted. Don't know, don't care, it was total trash. Another system had a table that contained the names of tables and the number of rows. All the programs incremented and decremented the number of rows with each transaction. Y'know..... because databases can't figure out how much data lives in any table at any time.Admin
Ah, MS SQL does support the ansi syntax, I'm just not good at navigating their docs. MySQL and SQLite are still hard to avoid though.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017
Admin
"SELECT COUNT(*) FROM tablename" is often an expensive operation in MySQL InnoDB.
Admin
Folks, the joke is that it was a very simple task. That's always the joke on this site, every single time. But, whatever, congratulations, you can code as well as an intern.
This non-sequitur is TRWTF.
Admin
Test post to discover how line breaks work for unregistered users.
Attempt 1 [enter][enter]
short line 1 [enter] short line 2 [enter] short line 3 [enter][enter]
Attempt 2 [enter[[enter]
short line 1 [enter][enter]
short line 2 [enter][enter]
short line 3 [enter][enter][enter]
Attempt 3 [enter[[enter]
long line 1 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter] long line 2 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter] long line 3 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter][enter]
Attempt 4 [enter[[enter]
long line 1 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter][enter]
long line 2 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter][enter]
long line 3 intended to word-wrap. Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem Lorem ipsem lorem ipsem [enter][enter]
Here's hoping there's a discernable pattern here. I'm tired of my code samples being run together.
Admin
So the punch line is: there's no way to enter a single-spaced code sample into this plain textbox. So just double-enter at each line end and accept the extra vertical white space. Oh well.
Admin
Hrm, what about backticks? Like so, [backtick]
inline
[backtick] right?Or triple Backticks:
[backtick][backtick][backtick]
[backtick][backtick][backtick]
Admin
Lots of ways to format posts. Check it out, WTFGuy:
https://daringfireball.net/projects/markdown/syntax
Admin
It is pretty much well documented in Oracle, that the client does prefetchs on cursors, so "who knows" is pretty ignorant.
And leaving away the first row indication does have impact on the query optimizer (as well documentated): If you ask for all records the DB will assume that you actually want all and choose a full table scan pretty likely as access path. First row statements will result in index driven access which is more likely to be found in cache, except for very small tables.
And you should not ask for all columns if you don't care about the content: Select null from ... will skip the table row access as well.
Admin
For DBs with the "limit/top/fetch n rows" feature, they don't all behave the same way -- yes, they're good at reducing the amount of data returned to you, but some of them will scan the whole set of results before spoon-feeding you the first few rows and tossing the rest away.
So, it's better to use count(*) instead, if you're not using any of the data anyway.
Admin
Though now that I think about it more, EXISTS probably will be a better fit for what was intended anyway, count(*) will have to check for the exact count before returning.
Admin
Thanks for the advice there Bananafish.
this is a test with two spaces at the end
and line two with two spaces at the end
done w no trailing spaces but an [enter] one last line.
Admin
I hope Sean renames the function to tableemptyp.
Admin
Let's see what EXPLAIN says:
Moral of the story: all of them run a scan, but only count(*) has to actually finish it to figure out how many rows there are.
Admin
Silly sort of a question, I suppose, but why precisely is this a "lucrative career?"
Tracking down ten-line self-contained stupidities doesn't really seem to be anything beyond the average low-paid code monkey fresh out of college.
Then again, I'm sure Steve has many other WTFs that actually justify his horrifyingly high consultant rates.
Admin
Yup. Comment wins thread. (Apart from the silly boolean reversal, of course.)
Admin
Of course there is only one table, it is the only table and it has everything in it. You just null out the columns you don't use for your record and you're good!
Or alternatively they are using that one table as some kind of a global on/off flag or something, where inserting a record enables some feature (possibly the columns on the table are the config values used for that feature).
Admin
Surround it with PRE and or CODE tags
Admin
Not sure why the story lists "using exceptions as flow control" as a fault. The "catch, log, and continue" approach to error handling isn't great, but the always-frustrating use of checked exceptions in Java means that a try-catch-finally block like this is completely normal for doing SQL stuff in Java... it's not actually possible to improve it much...
Admin
Oh, so this is markdown? Would be convenient if it actually said that somewhere.
I'ma just test syntax highlighting...
Admin
My thoughts exactly. I suspect that if one registers one gets a fancier javascript text editor and other goodies. Not worth it to me to find out though.
Admin
Ouch. There's never NOT a reason to use PreparedStatement. Using Statement is the mark of an amateur. You can see it in all those tutorials made by amateurs for amateurs, therefore ensuring the cycle of SQL injection and stupid code never ends.
Admin
Nope. Logged-in users get the same editing interface.
It would indeed be very nice if we had any guidance at all on how to format.
Admin
I see many "CASE WHEN 0 > (SELECT COUNT(*) FROM some_table)" in our codebase. I change them to if "EXISTS (SELECT 1 FROM ...)" whenever I can. It's much faster, because the DB doesn't have to do a count but can quit after returning just one row.
So, I would use a function there. "IF EXISTS... RETURN 1 ELSE RETURN 0" or something like that.
Admin
Connection conn = cpInstance.getConnection(); Is there a reason he doesn't use try-with-resources for the connection? It's obviously available on the java version being used as he uses it twice in the same code block.
Admin
I personally require anyone working for me to use PreparedStatement whether there are any parameters in their query or not. It's just a good habit and removes the excuse of not knowing the proper way to set parameters.