Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Bart Smissaert
> 
> That seems to be the answer and after some quick testing it looks it makes
it
> more efficient as well!
> 
> On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> > 2011/10/16 Fabian :
> >> How can you limit a count-query? I tried:
> >> SELECT COUNT(*) FROM table LIMIT 5000
> >
> > SELECT min(COUNT(*),5000) FROM table;
> > --

My first impression was that the count(*) inside the min() would access all
the records anyway (perhaps not all the columns though) and thus in fact
still access more than 5000 records, even if it was hidden by the min
function.

To test this I took a database containing tables with millions of records
and executed the following statements:

1. select count(*) from table;

2. select min(count(*), 5000) from table;

3. select count(*) from (select null from table limit 5000);

Now to really test properly the computer would have to be restarted before
each test in order to avoid buffering of the disks, etc.
However, I could not be bothered in this case and just used three separate
tables with identical structure but a different number of records (between 4
and 5 million). As expected with the difference in execution times between
the three statements it did not matter that much.

1. Around 150 seconds

2. Around 14 seconds

3. Around 50 milliseconds !!!

Thus it seems that although variation two perhaps avoids fully reading the
records, it still have to touch them (or perhaps the primary key) which is
better than a normal full select count(*) on the table. Perhaps somebody
know the inner workings better and can explain the difference of factor 10
between variation 1 and 2.

To actually limit the count statement only variation 3 actually works (which
has been suggested earlier in the thread) and is of course several orders of
magnitude faster.

Somebody had also suggested: 
select count(*) from (select 1 from table limit 5000);

Notice the inner select of 1 instead of null. 
As predicted this is slightly slower at around 60 milliseconds. 

So selecting null is always better in sub queries when only the count or
existence of records are needed and not actual values from any of the
columns.


/Frank Missel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Bart Smissaert]

That seems to be the answer and after some quick testing it looks it
makes it more efficient as well!

RBS


On Tue, Oct 18, 2011 at 1:36 PM, Kit  wrote:
> 2011/10/16 Fabian :
>> How can you limit a count-query? I tried:
>> SELECT COUNT(*) FROM table LIMIT 5000
>
> SELECT min(COUNT(*),5000) FROM table;
> --
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Fabian :
> How can you limit a count-query? I tried:
> SELECT COUNT(*) FROM table LIMIT 5000

SELECT min(COUNT(*),5000) FROM table;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Fabian
> Sent: 17 October 2011 15:34
> >
> No, I only want to have a capped total available.
> 
> If I would go with Simons solution, I have to read the rows for the first
> 100 pages (or whatever the cap is) into a temporary table, just to show
the
> first page. I don't need a cache for all those other pages, so that seems
a lot
> of overhead. I only want to know if there are 100 or less pages (without
> copying data around).
> 
> Maybe COUNT() is also creating a temporary table behind the scenes, then
> the performance of Simons solutions would be comparable with what I have
> now, and I would have the advantage that I can re-use that table to show
> subsequent pages without reading from disk.
> 
> But I always assumed COUNT() was faster than copying between tables,
> maybe I should just benchmark it.

I had the idea that you just retrieved the first 100 records and not the
first 100 pages.
Could the user not just see the first 100 records and perhaps an indicator
if there were more or not. He could then get 100 records at a time browsing
through them or if he so wished get a record count (you would then use the
count function on all records).

If you need to know up front whether there are more than 5000 records or not
I suggest you use the suggestion from Petite Abeille:

select count( * )
from   (
 select 1   or even just "select null" which
will not fetch anything.
 fromtable
 limit 5000
   )

You could do that and also just select e.g. 50 actual rows and display these
to the user.


/Frank


 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Kit]

2011/10/16 Petite Abeille :
> On Oct 16, 2011, at 10:39 PM, Kit wrote:
>>> select count(*) from (select 1 from table limit 5000)
>> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
>
> you realize that count( * )  has a very specific meaning, right?
> "The count(*) function (with no arguments) returns the total number of rows 
> in the group."
> http://www.sqlite.org/lang_aggfunc.html
> If this is what you mean, then stick to it :)

I originally thought that the symbol "*" means "all columns". I tried
to "EXPLAIN", now I see it differently.
Thank you.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[reseok]

What about this:


SELECT
 CASE count(*) WHEN 5000 THEN 'More than 5000' ELSE 'Less than 5000' END
FROM (SELECT ID FROM table ORDER BY whatever LIMIT 5000 OFFSET 25000)



Fabian schrieb:
> 2011/10/16 Frank Missel 
> 
>> What do you want to attain with the count?
>>
>>
> I want to allow users to paginate through a result set. The pages are
> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
> I have execute a separate COUNT() query (without LIMIT) once.
> 
> Because I'm basicly executing the same query twice just to get a total
> count, I'm trying to optimize this. Restricting the maximum number of pages
> to 10 should improve performance, if there was some way to put make COUNT()
> respect the LIMIT specified.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Fabian]

2011/10/16 Frank Missel 

>
> But it sounds a bit like Fabian both wants to have the total number of
> records available and at the same time limit the count.
>
>
No, I only want to have a capped total available.

If I would go with Simons solution, I have to read the rows for the first
100 pages (or whatever the cap is) into a temporary table, just to show the
first page. I don't need a cache for all those other pages, so that seems a
lot of overhead. I only want to know if there are 100 or less pages (without
copying data around).

Maybe COUNT() is also creating a temporary table behind the scenes, then the
performance of Simons solutions would be comparable with what I have now,
and I would have the advantage that I can re-use that table to show
subsequent pages without reading from disk.

But I always assumed COUNT() was faster than copying between tables, maybe I
should just benchmark it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Petite Abeille]

On Oct 16, 2011, at 10:39 PM, Kit wrote:

>> select count(*) from (select 1 from table limit 5000)
> 
> SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);

you realize that count( * )  has a very specific meaning, right?

"The count(*) function (with no arguments) returns the total number of rows in 
the group."

http://www.sqlite.org/lang_aggfunc.html

If this is what you mean, then stick to it :)

http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710703#2710703

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Kit]

> select count(*) from (select 1 from table limit 5000)

SELECT count(1) FROM (SELECT 1 FROM table LIMIT 5000);
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Petite Abeille]

On Oct 16, 2011, at 1:09 PM, Fabian wrote:

> How can you limit a count-query? I tried:
> 
> SELECT COUNT(*) FROM table LIMIT 5000
> 
> But it ignores the LIMIT clause. 

No it doesn't, it works as advertised. You are falling into the same trap as 
you did just a couple of threads ago. You need to get a grip on that LIMIT 
construct :)

The fine manual is there to help, please see "ORDER BY and LIMIT/OFFSET 
Clauses":

http://www.sqlite.org/lang_select.html

In the meantime:

select count( * )
from   (
 select 1
 fromtable
 limit 5000
   )


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Frank Missel]

> boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: 16 October 2011 21:53
> 
> Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's
going
> to need them eventually for when he displays them), then count how many
> rows he got.

Yeah, I would go that way also.

But it sounds a bit like Fabian both wants to have the total number of
records available and at the same time limit the count.
It is just not possible to do both at the same time as far as I can see.

Thus, you are left with following options as far as I can see:

1. As Slavin mentions: Read a certain number of records, e.g. 101 and change
your code so that the user can ask for e.g. 100 additional records at a
time. Possibly you could have an extra function that the user could activate
to have a total count if he so wishes. This would then read all records and
you would not have to worry about performance as all records (unless there
are very many) will be in the memory cache and subsequent request for these
records should be fast.

2. If there are many concurrent users and the query requests from these are
quite predictable, e.g. straight reads of all records in certain tables, you
could perhaps  have a local background job regularly count the number of
records for the relevant tables/queries and store these count results  in a
special status table. Your client program could then read the number of
records from the special status table as well as the first 101 records from
the actual data carrying table. The user could then get the first 100
records, and continue with batches of 100 as needed.

If these options does not seem to fit, I think that you have to explain a
bit more about how your solution is,  what you expect and how you access the
database.


/Frank


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Roger Andersson]

 On 10/16/11 14:21, Fabian wrote:

I want to allow users to paginate through a result set. The pages are
retreived through LIMIT/OFFSET, but to calculate the total number of pages,
I have execute a separate COUNT() query (without LIMIT) once.

Because I'm basicly executing the same query twice just to get a total
count, I'm trying to optimize this. Restricting the maximum number of pages
to 10 should improve performance, if there was some way to put make COUNT()
respect the LIMIT specified.
___

I can't tell if this is more efficient but it's one query
select *,(select count(*) from table) as total_record_count from table 
limit 100;

/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Simon Slavin]

On 16 Oct 2011, at 2:50pm, Bart Smissaert wrote:

> He is trying to make it more efficient, so stop counting if count > X.
> So setting the count after having counted the whole lot won't help.

Then he can't use count() because SQLite's implementation of it is not 
efficient for that.

Perhaps he could read the rows LIMIT 100 into a buffer (after all, he's going 
to need them eventually for when he displays them), then count how many rows he 
got.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Bart Smissaert]

He is trying to make it more efficient, so stop counting if count > X.
So setting the count after having counted the whole lot won't help.

RBS


On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin  wrote:
>
> On 16 Oct 2011, at 1:21pm, Fabian wrote:
>
>> 2011/10/16 Frank Missel 
>>
>>> What do you want to attain with the count?
>>
>> I want to allow users to paginate through a result set. The pages are
>> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
>> I have execute a separate COUNT() query (without LIMIT) once.
>
> You're using a programming language.  So do this:
>
> numberOfRows = SELECT count(*) FROM myTable
> if (numberOfRows > 100) then numberOfRows = 100
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Simon Slavin]

On 16 Oct 2011, at 1:21pm, Fabian wrote:

> 2011/10/16 Frank Missel 
> 
>> What do you want to attain with the count?
> 
> I want to allow users to paginate through a result set. The pages are
> retreived through LIMIT/OFFSET, but to calculate the total number of pages,
> I have execute a separate COUNT() query (without LIMIT) once.

You're using a programming language.  So do this:

numberOfRows = SELECT count(*) FROM myTable
if (numberOfRows > 100) then numberOfRows = 100

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Roger Andersson]

 On 10/16/11 14:21, Fabian wrote:

I want to allow users to paginate through a result set. The pages are
retreived through LIMIT/OFFSET, but to calculate the total number of pages,
I have execute a separate COUNT() query (without LIMIT) once.

Because I'm basicly executing the same query twice just to get a total
count, I'm trying to optimize this. Restricting the maximum number of pages
to 10 should improve performance, if there was some way to put make COUNT()
respect the LIMIT specified.
___

Maybe GROUP BY and HAVING can help you?
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Fabian]

2011/10/16 Frank Missel 

>
> What do you want to attain with the count?
>
>
I want to allow users to paginate through a result set. The pages are
retreived through LIMIT/OFFSET, but to calculate the total number of pages,
I have execute a separate COUNT() query (without LIMIT) once.

Because I'm basicly executing the same query twice just to get a total
count, I'm trying to optimize this. Restricting the maximum number of pages
to 10 should improve performance, if there was some way to put make COUNT()
respect the LIMIT specified.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Jos Groot Lipman]

 
SELECT COUNT(*) FROM table will always return exactly 1 record (with the
count).

LIMIT 5000 limits the number of records returned to 5000.

As 1 record < 5000 records the 5000 is effecively ignored
--
Jos
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabian
Sent: zondag 16 oktober 2011 13:09
To: General Discussion of SQLite Database
Subject: [sqlite] Limit COUNT

How can you limit a count-query? I tried:

SELECT COUNT(*) FROM table LIMIT 5000

But it ignores the LIMIT clause. I think the workaround would be counting
the results of a sub-query, but I'm trying to understand whats wrong with
the syntax above. The goal is to make the engine stop iterating after it
reached the upper limit, in order to save performance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit COUNT

[Frank Missel]

Hi Fabian,

The problem is that the limit apparently is applied on the result set, and
in this case the result set is only one row which is less than the 5000.
The culprit is the count(*) which basically says that to get the first row
in the result set all rows from the table has to be processed.

You could instead try:

select count(*)
from (select * from table limit 500)
;

That would give you the number 500. But it is kind of meaningless.
You might as well write:

select *
from table 
limit 500
;

And get the actual 500 first rows. In this case the result set is build up
as the select statement processing loops through the records in the table,
and thus it only needs to take 500 rows. Again, if you add a count(*) it
needs to traverse all records to get the first and only row  of the result
set that states the number of records in the table. It is the same if you
specify ORDER BY together with limit. You will only get the x number of
records in the LIMIT statement but before that all records will have to be
traversed and sorted before you the 500 rows, unless perhaps there already
is an index with one component fitting the ORDER BY perfectly.

What do you want to attain with the count?

/Frank

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Fabian
> Sent: 16 October 2011 19:09
> To: General Discussion of SQLite Database
> Subject: [sqlite] Limit COUNT
> 
> How can you limit a count-query? I tried:
> 
> SELECT COUNT(*) FROM table LIMIT 5000
> 
> But it ignores the LIMIT clause. I think the workaround would be counting
the
> results of a sub-query, but I'm trying to understand whats wrong with the
> syntax above. The goal is to make the engine stop iterating after it
reached
> the upper limit, in order to save performance.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Limit COUNT

[Fabian]

How can you limit a count-query? I tried:

SELECT COUNT(*) FROM table LIMIT 5000

But it ignores the LIMIT clause. I think the workaround would be counting
the results of a sub-query, but I'm trying to understand whats wrong with
the syntax above. The goal is to make the engine stop iterating after it
reached the upper limit, in order to save performance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users