• Debra (unregistered)

    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.

  • (nodebb)

    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.

  • (nodebb)

    select * ... is often much faster than select count(*) ... Depending on type of database and version and whatnot. Just saying.

  • (nodebb) in reply to Nutster

    [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.

  • Carsten (unregistered)

    Too bad we don't learn why empty tables are interesting to the application. However the TWTF are some suggestions:

    No COUNT(*) in the query. Not good, if the table is actually well filled. Use https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause or DB specific equivalent. It is faster to throw any row at you than the exact number.

    Using exceptions as flow control. Actually I do not see business logic in the exception handling.

    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.

  • moltonel 3x Combo (google)

    "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

  • (nodebb) in reply to moltonel 3x Combo

    There is (at last) in sql2008: select [column_list] from [table] fetch first [n] rows only;

  • Tim (unregistered)

    I use select distinct 'x'

  • Dave (unregistered) in reply to Carsten

    "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.

  • moltonel 3x Combo (google) in reply to nerd4sale

    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.

  • (nodebb) in reply to moltonel 3x Combo

    True, although most major databases support the ansi standard nowadays: db2, oracle, postgress, not sure about sqlserver though.

  • random stranger (unregistered)

    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.

  • random stranger (unregistered)

    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.

  • someone (unregistered)

    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?

  • my name is missing (unregistered)

    The real question I have is "why does it matter?"

  • Mr. TA (unregistered)

    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.

  • kitihounel (unregistered)

    There is a typo in the code. A closing curl brace is missing before the first catch statement.

  • Jeremy (unregistered)

    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: }

  • WTFGuy (unregistered)

    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.

  • Supersonic Tumbleweed (unregistered) in reply to Debra

    Once? I bet it's called in a loop somewhere!

  • moltonel 3x Combo (google) in reply to nerd4sale

    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.

  • (nodebb)

    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.

  • moltonel 3x Combo (google) in reply to moltonel 3x Combo

    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

  • (nodebb)

    "SELECT COUNT(*) FROM tablename" is often an expensive operation in MySQL InnoDB.

  • siciac (unregistered)

    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.

    Maybe there’s a perfectly valid reason to build a method like this that I can’t imagine. Well, let’s check the implementation.

    This non-sequitur is TRWTF.

  • WTFGuy (unregistered)

    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.

  • WTFGuy (unregistered)

    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.

  • K. (unregistered) in reply to WTFGuy

    Hrm, what about backticks? Like so, [backtick]inline[backtick] right?

    Or triple Backticks:

    [backtick][backtick][backtick]

    first line
seconds line
third line
... and so on...

    [backtick][backtick][backtick]

  • (nodebb) in reply to WTFGuy

    Lots of ways to format posts. Check it out, WTFGuy:

    https://daringfireball.net/projects/markdown/syntax

  • Carsten (unregistered) in reply to random stranger

    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.

  • Ducky (unregistered) in reply to Nutster

    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.

  • Ducky (unregistered) in reply to Ducky

    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.

  • WTFGuy (unregistered)

    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.

  • (nodebb)

    I hope Sean renames the function to tableemptyp.

  • lol (unregistered)

    Let's see what EXPLAIN says:

    bob=# create table boo as select generate_series(1, 10000000) as id, random()::text as stuff;
SELECT 10000000

bob=# analyze boo;
ANALYZE
bob=# explain select exists (select id from boo) as yup;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Result  (cost=0.02..0.03 rows=1 width=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on boo  (cost=0.00..163747.26 rows=9999926 width=0)
(3 rows)

bob=# explain select id from boo limit 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Limit  (cost=0.00..0.02 rows=1 width=4)
   ->  Seq Scan on boo  (cost=0.00..163747.26 rows=9999926 width=4)
(2 rows)

bob=# explain select count(*) from boo;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate  (cost=188747.08..188747.09 rows=1 width=8)
   ->  Seq Scan on boo  (cost=0.00..163747.26 rows=9999926 width=0)
(2 rows)

    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.

  • Sole Purpose of Visit (unregistered) in reply to my name is missing

    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.

  • Sole Purpose of Visit (unregistered) in reply to my name is missing

    Yup. Comment wins thread. (Apart from the silly boolean reversal, of course.)

  • airdrik (unregistered) in reply to someone

    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).

  • Appalled (unregistered) in reply to WTFGuy

    Surround it with PRE and or CODE tags

  • Simon (unregistered)

    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...

  • someone (unregistered) in reply to Bananafish

    Oh, so this is markdown? Would be convenient if it actually said that somewhere.

    Wow, look at this!

    I'ma just test syntax highlighting...

    $TRWTF = 'PHP' // apparently
  • WTFGuy (unregistered) in reply to someone

    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.

  • Mongo (unregistered) in reply to Debra

    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.

  • (nodebb) in reply to WTFGuy

    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.

  • 🤷 (unregistered)

    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.

  • B (unregistered)

    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.

  • B (unregistered) in reply to Mongo

    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.

Leave a comment on “Is the Table Empty?”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article