Re: [sqlite] Check for empty table

[Dennis Cote]

RB Smissaert wrote:

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.

But I am not sure if a table always has the hidden field ROWID.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

RB,

This was discussed recently in this thread 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg19148.html


I believe Igor's suggestion is optimal for SQLite. It may be faster to 
add a limit clause to the subquery for other database engines.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Scott Hess]

On 12/13/06, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:

"Scott Hess" <[EMAIL PROTECTED]> writes:
> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?

COUNT(*) is generally slow.  It actually iterates over every row, so with
large tables, it's very slow.  By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.


Hmm, that's a good point, I was thinking in terms of the empty case.
Indeed, in the past I've used one of:

 SELECT max(rowid) FROM table;
 SELECT rowid FROM table ORDER BY rowid DESC LIMIT 1;

as a proxy for COUNT(*).  Obviously this only works for a table which
hasn't seen deletions (or who's deletions have only been at the end
and you created the table appropriately).  I don't remember why both
versions stick in my mind, maybe I tried both and just don't remember
which was faster.  Also, this returns null if the table is empty, so
you might want:

 SELECT ifnull(max(rowid),0) FROM table;

but, honestly, if you're this worried about speed you should manage
the null test in your C code.

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Martin Jenkins]

Scott Hess wrote:

What's wrong with "SELECT COUNT(*) FROM table;"?
It scans the whole table. This is OK if the table is small (nearly) 
empty, but if it has a couple of million rowZzzz


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Nicolas Williams]

On Wed, Dec 13, 2006 at 06:07:56PM +, RB Smissaert wrote:
> Nothing wrong, but is it the fastest?

count(*) doesn't read every record in the table.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Derrell . Lipman]

"Scott Hess" <[EMAIL PROTECTED]> writes:

> On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
>> Trying to find the fastest way to determine if a table has no rows.
>>
>> I think this will do:
>> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
>> If a table has rows then the result should be 1.
>
> What's wrong with "SELECT COUNT(*) FROM table;"?

COUNT(*) is generally slow.  It actually iterates over every row, so with
large tables, it's very slow.  By using a query with a "LIMIT 1", you ensure
that as soon as a single matching row is found, no additional rows are
searched for.

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Check for empty table

[RB Smissaert]

Nothing wrong, but is it the fastest?

RBS

-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: 13 December 2006 17:25
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Check for empty table

On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:
> Trying to find the fastest way to determine if a table has no rows.
>
> I think this will do:
> SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
> If a table has rows then the result should be 1.

What's wrong with "SELECT COUNT(*) FROM table;"?

-scott


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Scott Hess]

On 12/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote:

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.


What's wrong with "SELECT COUNT(*) FROM table;"?

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Check for empty table

[Rich Shepard]

On Wed, 13 Dec 2006, RB Smissaert wrote:


Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.


RBS

  About a month ago, I sought a better solution than what I had devised.
Here's one of the answers for you:

You could write

select exists (select * from  where ='');

This produces a singleton result (single row, single column). The only cell
of this result contains integer 1 if there are indeed rows with
='' in table component, and 0 if there are none.

I still don't understand why you want to "look before you leap", so to
speak. Why not just run the query

select * from  where ='';

and handle the case where it produces an empty result set?


Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Check for empty table

[RB Smissaert]

Trying to find the fastest way to determine if a table has no rows.

I think this will do:
SELECT (SELECT ROWID FROM table limit 1) IS NOT NULL;
If a table has rows then the result should be 1.

But I am not sure if a table always has the hidden field ROWID.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-