Re: [sqlite] Count error?

Jim Morris, on Friday, November 1, 2019 12:07 PM, wrote... > > Using a sub-select should work > > select > > (select count(n) from t0) as "t0_count", > > (select count(n) from t1) as "t1_count", > > (select count(n) from t2) as "t2_count" > > ; Thanks. Works. josé > On 11/1/2019 9:07 AM, Jose

Re: [sqlite] Count error?

Using a sub-select should work select (select count(n) from t0) as "t0_count", (select count(n) from t1) as "t1_count", (select count(n) from t2) as "t2_count" ; On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote: > Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote... >> >>

Re: [sqlite] Count error?

Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote... > > > Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote... > > > > On 11/1/19, Jose Isaias Cabrera, on > > > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 > > > AS > > > b LEFT JOIN t2 AS c; >

Re: [sqlite] Count error?

Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote... > > On 11/1/19, Jose Isaias Cabrera, on > > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS > > b LEFT JOIN t2 AS c; > > 3375|3375|3375 > > > > Huh? I expected the result: > > > > 15|15|15 > > You did a

Re: [sqlite] Count error?

On 11/1/19, Jose Isaias Cabrera wrote: > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS > b LEFT JOIN t2 AS c; > 3375|3375|3375 > > Huh? I expected the result: > > 15|15|15 You did a three-way join on tables with 15 rows each. 15x15x15 is 3375. A LEFT JOIN

[sqlite] Count error?

Greetings. Please take a look at the following: create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11'); insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11'); insert

Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

Interesting! I appreciate the detailed response. I don't think the shadow table digging fits our risk profile exactly :), but it's interesting to know where to look if we want to check ourselves. I realized after rereading all of this that ultimately we want to keep track of the max rowid

Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

On 19/10/62 06:31, Ben Asher wrote: Hello! I'm trying to write some code to keep an external content table in sync with the index. To do this, I need to be able to get some state about the index: either how many rows have been inserted so far or the max rowid that has been inserted into the

Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

I should have included this in the first place. Here are the queries used to setup this table and an example: CREATE TABLE text (text TEXT NONNULL); CREATE VIRTUAL TABLE text_fts_index USING fts5(text, content=text, content_rowid=rowid, prefix='2 3 4 5 6 7 8 9 10', tokenize='unicode61'); INSERT

[sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

Hello! I'm trying to write some code to keep an external content table in sync with the index. To do this, I need to be able to get some state about the index: either how many rows have been inserted so far or the max rowid that has been inserted into the index. However, if I try to run queries

Re: [sqlite] count optimisation

;ryansmit...@gmail.com> Sent: Thursday, March 15, 2018 11:25:18 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] count optimisation On 2018/03/15 12:20 PM, x wrote: > select count(*) from TblA cross join TblB > > is over 200 times slower than > > select (select count(*)

Re: [sqlite] count optimisation

On 2018/03/15 12:20 PM, x wrote: select count(*) from TblA cross join TblB is over 200 times slower than select (select count(*) from TblA) * (select count(*) from TblB); This is a human-level optimisation, it's not efficient for the database engine to do the optimisation. By human-level

[sqlite] count optimisation

select count(*) from TblA cross join TblB is over 200 times slower than select (select count(*) from TblA) * (select count(*) from TblB); Not that it matters to me. I came across it by accident and mention it only in case it’s a missed optimisation.

Re: [sqlite] count registers ina a table

On 30 Dec 2016, at 11:15am, MONSTRUO Hugo González wrote: > [URL for a copy of his database] > > SELECT COUNT (*) FROM bm_ppal > 59.046 seconds of delay > > SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId > 1.128 minutes of delay > > SELECT COUNT (nbmId) FROM bm_ppal

Re: [sqlite] count registers ina a table

Hi, thanks: Donald Griggs, John Gillespie, Simon Slavin I have a phone book (bm_ppal), 726.000 rows, 10 columns This phone book have this columns Name Declared Type Type Size nbmId INTEGER INTEGER nbmCodigo VARCHAR (6) VARCHAR 6 abmNombre VARCHAR (320) VARCHAR 320 abmNombrePlano VARCHAR (320)

Re: [sqlite] count registers in a table

On 26 Dec 2016, at 3:14pm, MONSTRUO Hugo González wrote: > I have a table with 726.000 registers. > > SELECT COUNT(*) FROM MyTable << is very slowly > > SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST While this is not a bug in SQLite,

Re: [sqlite] count registers in a table

Hello, Hugo, Regarding: "I have a table with 726.000 registers." 1) I assume that you mean what others call "rows" correct? (and not columns, I hope) 2) Regarding: "SELECT COUNT(*) FROM MyTable << is very slowly" As I understand it, that should be as fast as SELECT COUNT (RowId) as of

Re: [sqlite] count registers in a table

> Which is the fastest way to count the records of a table. ? And records > that meet a condition? I have a table with 726.000 registers. SELECT COUNT(*) FROM MyTable << is very slowly SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST SELECT COUNT(RowId) FROM MyTable

Re: [sqlite] count registers in a table

On 24 Dec 2016, at 10:52am, MONSTRUO Hugo González wrote: > Which is the fastest way to count the records of a table. ? And records > that meet a condition? SELECT COUNT(*) FROM MyTable SELECT COUNT(*) FROM MyTable WHERE a = b There is special optimization for

[sqlite] count registers in a table

Hi, Which is the fastest way to count the records of a table. ? And records that meet a condition? regards ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Count connections

Hi, How do we know how many connections a database has ? Is it possible to query it using SQL ? Is there a simple command from the sqlite3 shell client ? -- Marco Arthur @ (M)arco Creatives

[sqlite] Count connections

On 5/10/16, Marco Silva wrote: > Hi, > > How do we know how many connections a database has ? Is it possible > to query it using SQL ? Is there a simple command from the sqlite3 > shell client ? > A "connection" to a database is an open file descriptor. Some operating systems have

Re: [sqlite] count (*) performance

s will be identical; but the contents reflect the last record read if the statement requires a sort operation). -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Mittwoch, 27. November 2013 09:45 An: General Discussion of SQLite Database Betreff

Re: [sqlite] count (*) performance

On Wed, Nov 27, 2013 at 9:35 AM, Clemens Ladisch wrote: > veeresh kumar wrote: > > I see that in release history for 3.8.1 below item. > > "Estimate the sizes of table and index rows and use the smallest > applicable B-Tree for full scans and "count(*)" operations." > > > >

Re: [sqlite] count (*) performance

veeresh kumar wrote: > I see that in release history for 3.8.1 below item. > "Estimate the sizes of table and index rows and use the smallest applicable > B-Tree for full scans and "count(*)" operations." > > Does it mean that performance of count(*) has been improved in 3.8.1 Yes. > and if yes

[sqlite] count (*) performance

Hi , I see that in release history for 3.8.1 below item. "Estimate the sizes of table and index rows and use the smallest applicable B-Tree for full scans and "count(*)" operations." Does it mean that performance of count(*) has been improved in 3.8.1 and if yes by how much? We were using

Re: [sqlite] count from 2 tables

The answer to you exact question is "Yes". Didn't help much did it? But I will at least start off by giving some hints. You need to do an inner join on the common column (service_no). You need to use the HAVING clause to select only those entries with more than 2 entries in the result table

[sqlite] count from 2 tables

There are 2 related table above. There are service_no 1,2,3,5. Service 1,2,3 are type e-line, service 5 is type e-lan. Service 1,2,3 own 2 service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3). What is the count of services have more than 2 service_port(sp) and type is e-lan?

Re: [sqlite] count from 2 tables

On 9/3/2013 6:52 PM, ChingChang Hsiao wrote: What is the count of services have more than 2 service_port(sp) and type is e-lan? The answer is 1. Could it be described as one sql statement? select count(*) from service_table s where service_type = 'e-lan' and (select count(*) from

[sqlite] count from 2 tables

CREATE TABLE service_table ( service_nameTEXT, /* name of the service (unique) */ service_no INTEGER, /* internal service number, assigned by the system. */ service_typeTEXT, /* Type of Service. Should be one of

[sqlite] count from 2 tables

CREATE TABLE service_table ( service_nameTEXT, /* name of the service (unique) */ service_no INTEGER, /* internal service number, assigned by the system. */ service_typeTEXT, /* Type of Service. Should be one of

Re: [sqlite] count infact passed rows of OFFSET select

If you get no rows returned, you could query SELECT COUNT(*) FROM table1; (If you get rows returned, you know that the number skipped is the size of your offset.) On Thu, Dec 6, 2012 at 11:44 AM, Steffen Mangold < steffen.mang...@balticsd.de> wrote: > Hi, > > I want to know if there is a

[sqlite] count infact passed rows of OFFSET select

Hi, I want to know if there is a possibility to count the infact passed row if I make an OFFSET SELECT. For example: table1 --- Row1 Row2 Row3 Row4 Row5 Select * from table1 Order by rowID DESC Lilit 2 Offset 10; Here the Result is null, but I want to detect that I passed 5 existing

Re: [sqlite] Count(*) help

On Mon, Sep 17, 2012 at 06:03:12PM -0600, Keith Medcalf scratched on the wall: > > There it is again. What does length(NULL) mean ? Should it be NULL because > > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > > negative integer representing how long the NULL

Re: [sqlite] Count(*) help

> There it is again. What does length(NULL) mean ? Should it be NULL because > anything derived from "Don't know" is "Don't know" ? Or should it be a non- > negative integer representing how long the NULL value is ? I may have missed > it, but I don't think the answer is in SQL92. The only

Re: [sqlite] Count(*) help

On Sep 17, 2012, at 5:09 PM, Simon Slavin wrote: > I suspect that this is what leads to different SQL engines implementing > count() in different ways. I suspect that you are indeed very confused :D The issue at hand is not whether or not count( * ) is implemented

Re: [sqlite] Count(*) help

On Sep 17, 2012, at 5:23 PM, "Keith Medcalf" wrote: > I don't know if there is a standard for treating empty character strings as > if they were not-present values without first applying one of the builtin > functions designed to deal with NULL. However, my opinion is

Re: [sqlite] Count(*) help

Simon Slavin wrote: > What does length(NULL) mean ? When there is no string, there is no string length. > I don't think the answer is in SQL92. | 6.6 | [...] | General Rules | [...] | 4) If a is specified, then |Case: |a) Let S be the . If the value of S is | not the null value,

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 4:30pm, "Black, Michael (IS)" wrote: > sqlite> select count(*) from t where length(a) = 0; > 1 There it is again. What does length(NULL) mean ? Should it be NULL because anything derived from "Don't know" is "Don't know" ? Or should it be a

Re: [sqlite] Count(*) help

___ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John Clegg [john.cl...@nailsea.net] Sent: Monday, September 17, 2012 10:05 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Count(*) help Yes, that was the problem thanks. Even

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 4:05pm, John Clegg wrote: > Yes, that was the problem thanks. Even though sqlitebrowser declared them > as empty with '' ! No, actually that's your problem. '' is exactly the way to define something as an empty string. To supply a null you would

Re: [sqlite] Count(*) help

On 17 Sep 2012, at 3:50pm, "Black, Michael (IS)" wrote: > I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL > standard say anything about what a "NULL" value is? And who's correct here > if there is a standard? I believe that the values are

Re: [sqlite] Count(*) help

Yes, that was the problem thanks. Even though sqlitebrowser declared them as empty with '' ! On 17 September 2012 15:50, Black, Michael (IS) wrote: > Or just fix the existing table: > > update members set year2007=NULL where year2007=''; > update members set year2008=NULL

Re: [sqlite] Count(*) help

Or just fix the existing table: update members set year2007=NULL where year2007=''; update members set year2008=NULL where year2008=''; update members set year2009=NULL where year2009=''; update members set year2010=NULL where year2010=''; update members set year2011=NULL where year2011='';

Re: [sqlite] Count(*) help

John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select >

Re: [sqlite] Count(*) help

org] on behalf of John Clegg [john.cl...@nailsea.net] Sent: Monday, September 17, 2012 1:59 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Count(*) help OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have remo

Re: [sqlite] Count(*) help

Hi John, If you zip the file (it sounds it is only small) and mail it to me or to this list and tell what answer you want I (or somebody else) will post the SQL. RBS On Mon, Sep 17, 2012 at 7:59 AM, John Clegg wrote: > OK thanks folks. Here is the full query (which is

Re: [sqlite] Count(*) help

OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have removed the double-quoted but it makes no difference. SELECT COUNT( "Year2007" ), COUNT( "Year2008" ), COUNT( "Year2009" ), COUNT( "Year2010" ), COUNT( "Year2011" ), COUNT(

Re: [sqlite] Count(*) help

> PS Note that it is count(X) and not count("X") Count(X) and Count("X") are identical if X is a column in the table (or join) from which you are counting, and X does not contain embeded spaces, special characters, or other inanities. count(*) counts rows, and count('X') or count(1) [or any

Re: [sqlite] Count(*) help

On 9/16/2012 9:17 AM, John Clegg wrote: I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days when this table was in Access, select count("Year2013")

Re: [sqlite] Count(*) help

f Of John Clegg > Sent: Sunday, 16 September, 2012 10:18 > To: sqlite-users@sqlite.org > Subject: [sqlite] Count(*) help > > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and

Re: [sqlite] Count(*) help

On 16-09-2012 18:21, Marcus Ilgner wrote: On So, 2012-09-16 at 17:17 +0100, John Clegg wrote: I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days

Re: [sqlite] Count(*) help

John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select > count("Year2013") from Members

Re: [sqlite] Count(*) help

Hi John, Funny seeing you here on the SQLite forum. Are these by any chance the ISUG members? Doing a count without a WHERE clause is always likely to give different results with the various SQL implications as far as I know. Why not add a WHERE? RBS On Sun, Sep 16, 2012 at 5:17 PM, John Clegg

Re: [sqlite] Count(*) help

On So, 2012-09-16 at 17:17 +0100, John Clegg wrote: > I have a table Members with 896 rows and a text field "Year2012". It > contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed > in sqlitebrowser as "empty") > > Back in the olden days when this table was in Access, select

[sqlite] Count(*) help

I have a table Members with 896 rows and a text field "Year2012". It contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed in sqlitebrowser as "empty") Back in the olden days when this table was in Access, select count("Year2013") from Members used to return 169. In

Re: [sqlite] count() problem.

On 6/14/12 1:00 PM, "Igor Tandetnik" wrote: >On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote: >> What I want to do is find a_id's for which c contains no rows with the >>matching b_id in which the flag column is 1. > >Why don't you just say that? > >select a_id from

Re: [sqlite] count() problem.

On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote: What I want to do is find a_id's for which c contains no rows with the matching b_id in which the flag column is 1. Why don't you just say that? select a_id from a where b_id not in (select b_id from c where flag = 1); -- Igor

Re: [sqlite] count() problem.

On 6/14/12 11:06 AM, "Pavel Ivanov" wrote: >> SELECT a_id, count(*) >> FROM a LEFT OUTER JOIN c USING (b_id) >> WHERE c.flag = 1 >> GROUP BY a_id >> >> I get a positive count if there are there are matching rows with flag = >>1; but if an a_id has no matching rows in c with

Re: [sqlite] count() problem.

> SELECT a_id, count(*) > FROM a LEFT OUTER JOIN c USING (b_id) > WHERE c.flag = 1 > GROUP BY a_id > > I get a positive count if there are there are matching rows with flag = 1; > but if an a_id has no matching rows in c with flag=1, I get nothing at all.   > What I want is a row with the a_id

[sqlite] count() problem.

I have a database with two tables, one of which depends on the other: CREATE TABLE a( a_id INTEGER PRIMARY KEY b_id INTEGER); CREATE TABLE c( b_id INTEGER, num INTEGER, flag INTEGER, PRIMARY KEY (b_id,num)); In words, each

Re: [sqlite] COUNT() extremely slow first time!

Did you do multiple runs and average? And how are you measuring time? Did you do buffered reads? And you're MB/sec isn't accurate for the count() test. I think it was already said that count() doesn't read the entire record. The SQLite numbers are due to SQLite caching. If you make page

Re: [sqlite] COUNT() extremely slow first time!

On Sun, Feb 27, 2011 at 3:11 PM, Max Vlasov wrote: > Hmm, yesterday something struck me I can do similar tests on an Asus T91MT > having SSD as the storage. I got similar timings to Greg's. So it seems like > page size is a very sensitive parameter for solid state drives.

Re: [sqlite] COUNT() extremely slow first time!

On Sat, Feb 26, 2011 at 1:03 AM, Greg Barker wrote: > harddrive. Can you confirm this? > > > Giving this, if the fastest is 3.6 seconds, you have a very fast > I can confirm this, my tests were run on a machine that uses a solid state > drive. > > Hmm, yesterday something

Re: [sqlite] COUNT() extremely slow first time!

On 25 Feb 2011, at 10:03pm, Greg Barker wrote: > Thanks for the responses guys. > >> I would never have any table with 150 columns. It should be possible to >> keep the schema for your table in your head. > > Unfortunately those are the cards I have been dealt. The columns are just > buckets

Re: [sqlite] COUNT() extremely slow first time!

Thanks for the responses guys. > I would never have any table with 150 columns. It should be possible to keep the schema for your table in your head. Unfortunately those are the cards I have been dealt. The columns are just buckets of data (bucket1, bucket2, bucket3, etc). Each bucket could be

Re: [sqlite] COUNT() extremely slow first time!

On Fri, Feb 25, 2011 at 1:51 AM, Greg Barker wrote: > I ran my tests again, this time setting the cache_size based on the > page_size. For some reason I wasn't able to replicate the timing I got > before for the 1024 page size (40.2 seconds) which is really confusing me, >

Re: [sqlite] COUNT() extremely slow first time!

On 24 Feb 2011, at 10:51pm, Greg Barker wrote: > What do > you do if there could be anywhere between 30-150 columns? I would never have any table with 150 columns. It should be possible to keep the schema for your table in your head. > Optimizing performance > for an application where both

Re: [sqlite] COUNT() extremely slow first time!

I ran my tests again, this time setting the cache_size based on the page_size. For some reason I wasn't able to replicate the timing I got before for the 1024 page size (40.2 seconds) which is really confusing me, since I was careful to make sure nothing was running in the background while I was

Re: [sqlite] COUNT() extremely slow first time!

Average payload per entry for my test table was 409.00. Sounds about right since the db has 4 columns and each is filled with a random string of length 100. I've uploaded the full output from the sqlite3_analyzer for a few different page sizes: http://fletchowns.net/files/1024-analyzer.txt

Re: [sqlite] COUNT() extremely slow first time!

t; To: sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > Is the record size you refer to here the same as the "Average payload per > entry" that sqlite3_analyzer determines for me? > > On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov <max.vla

Re: [sqlite] COUNT() extremely slow first time!

Yes, Greg, please post this value for this table you use in count query Max On Wed, Feb 23, 2011 at 9:58 PM, Greg Barker wrote: > Is the record size you refer to here the same as the "Average payload per > entry" that sqlite3_analyzer determines for me? > > On Wed, Feb

Re: [sqlite] COUNT() extremely slow first time!

Is the record size you refer to here the same as the "Average payload per entry" that sqlite3_analyzer determines for me? On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote: > Greg, you should also take the record size into account. My hypothesis is > that if your record is

Re: [sqlite] COUNT() extremely slow first time!

t; >> Date: Wed, 23 Feb 2011 10:47:03 -0500 >> From: pri...@gmail.com >> To: t...@djii.com; sqlite-users@sqlite.org >> Subject: Re: [sqlite] COUNT() extremely slow first time! >> >> The SQLite cache size is in pages (2000 by default), so by increasing >

Re: [sqlite] COUNT() extremely slow first time!

characters): MyID|MyText With a page size of 4096, will SQLite put ~200 rows in one page? > Date: Wed, 23 Feb 2011 10:47:03 -0500 > From: pri...@gmail.com > To: t...@djii.com; sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > The

Re: [sqlite] COUNT() extremely slow first time!

The SQLite cache size is in pages (2000 by default), so by increasing the page size 8x, you're also increasing the cache size and memory requirements by 8x. Not saying it's a bad thing, just something to be aware of. If you want to compare 1K and 8K page size and only compare the effect page

Re: [sqlite] COUNT() extremely slow first time!

On Wed, Feb 23, 2011 at 03:10:02PM +0100, Sven L scratched on the wall: > > Does this trick work on the primary key? If not, why? Yes, all the time. Defining a column as a PK automatically creates a UNIQUE index over that column. The only exception is when the column is an INTEGER

Re: [sqlite] COUNT() extremely slow first time!

Hello Greg, I found this to be the case too. The difference between 1K and 8K is staggering. I default all my windows DB's to 8K now. Tuesday, February 22, 2011, 1:59:29 PM, you wrote: GB> I'm currently dealing with a similar issue. I've found that the page_size GB> PRAGMA setting can have a

Re: [sqlite] COUNT() extremely slow first time!

Does this trick work on the primary key? If not, why? > From: max.vla...@gmail.com > Date: Wed, 23 Feb 2011 16:09:04 +0300 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker <fle...@

Re: [sqlite] COUNT() extremely slow first time!

On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote: > I'm currently dealing with a similar issue. I've found that the page_size > PRAGMA setting can have a dramatic effect on how long it takes to "warm up" > the table. On Windows 7, with page_size=1024, a SELECT

Re: [sqlite] COUNT() extremely slow first time!

Interesting! I've been using "PRAGMA page_size = 4096;" in my software. Perhaps I should increase it and see if I can get a performance gain. Does it affect INSERTs too? > Date: Tue, 22 Feb 2011 10:59:29 -0800 > From: fle...@fletchowns.net > To: sqlite-users@sqlite.org >

Re: [sqlite] COUNT() extremely slow first time!

I'm currently dealing with a similar issue. I've found that the page_size PRAGMA setting can have a dramatic effect on how long it takes to "warm up" the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column) takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson wrote: > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: >> >> Thank you for your detailed explanation! >> First, can you please tell me how to purge the cache in Windows 7? This >> could be very useful for

Re: [sqlite] COUNT() extremely slow first time!

lav...@bigfraud.org > Date: Mon, 21 Feb 2011 16:56:01 + > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > > On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote: > > > On Mon, Feb 21, 2011 at 11:05 AM, Sven L <larvpo...@hotmail.se>

Re: [sqlite] COUNT() extremely slow first time!

On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote: > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: >> >> Thank you for your detailed explanation! >> First, can you please tell me how to purge the cache in Windows 7? This >> could be very useful for my tests! > > Sorry,

Re: [sqlite] COUNT() extremely slow first time!

tem (Mac OSX), and may have nothing at all to do with the performance on yours. Jim > > Thanks again! > > >> Date: Mon, 21 Feb 2011 10:17:03 -0500 >> From: pri...@gmail.com >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] COUNT() extremely slow first time!

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 03:37:50PM +0100, Sven L scratched on the wall: > I've learnt that COUNT(*) is slower than COUNT(ID), That's usually not true. > since * means the engine has to traverse all columns Actually, count(*) is the one case when the engine does *not* need to traverse

Re: [sqlite] COUNT() extremely slow first time!

> > I've learnt that COUNT(*) is slower than COUNT(ID), since * means the >> > engine has to traverse all columns and it might even return another value >> > if there are NULL-values... >> > >> > Also, this is quite interesting: >> > >>

Re: [sqlite] COUNT() extremely slow first time!

not possible? :P Thanks again! > Date: Mon, 21 Feb 2011 10:17:03 -0500 > From: pri...@gmail.com > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > This is a common issue on the mailing list. The first time you do > count(*), SQLite (

Re: [sqlite] COUNT() extremely slow first time!

LAN SELECT COUNT(ItemID) FROM Items; > > 0|0|0|SCAN TABLE Items (~100 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items; > > sqlite> > > > > I would expect an index scan on the first statement. The second statement > > tells me nada?! > >

Re: [sqlite] COUNT() extremely slow first time!

) > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items; > sqlite> > > I would expect an index scan on the first statement. The second statement > tells me nada?! > > Thanks for your help! > > >> From: slav...@bigfraud.org >> Date: Mon, 21 Feb 2011 14:

Re: [sqlite] COUNT() extremely slow first time!

Thanks for your help! > From: slav...@bigfraud.org > Date: Mon, 21 Feb 2011 14:24:50 + > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] COUNT() extremely slow first time! > > > On 21 Feb 2011, at 2:23pm, Sven L wrote: > > > SELECT COUNT(ItemID) FROM Items

Re: [sqlite] COUNT() extremely slow first time!

On 21 Feb 2011, at 2:23pm, Sven L wrote: > SELECT COUNT(ItemID) FROM Items; > > This takes around 40 seconds the first time! WHY?! Try again, doing everything identically except that instead of the above line use SELECT COUNT(*) FROM Items; Simon.

[sqlite] COUNT() extremely slow first time!

SQLite 3.7.5. I have a table (13 columns with 1 primary key, 1 index on a date column, 1 UNIQUE-constraint on two columns). I insert ~130 rows, 1000 at a time (transaction-wise). I close the database, reopen it and immediately perform a COUNT-operation: SELECT COUNT(ItemID) FROM Items;

Re: [sqlite] COUNT very slow

I can get a big speed up of COUNT if I first do a VIEW of what I have to count and than make select COUNT on the view. Without VIEW: 9 Minutes With VIEW: 8 Seconds! Il 24/09/2010 10.58, Martin Engelschalk ha scritto: > > Am 24.09.2010 10:38, schrieb Michele Pradella: >> ok, thank you for

Re: [sqlite] COUNT very slow

Am 24.09.2010 10:38, schrieb Michele Pradella: >ok, thank you for the advices, I'll try to use a TRIGGER. > The DB already has an index. > Anyway if I have to count something like this: > select COUNT(*) from logs WHERE DateTime<=yesterday > I can't do it with a TRIGGER No, but in this case

Re: [sqlite] COUNT very slow

ok, thank you for the advices, I'll try to use a TRIGGER. The DB already has an index. Anyway if I have to count something like this: select COUNT(*) from logs WHERE DateTime<=yesterday I can't do it with a TRIGGER Il 24/09/2010 10.29, Martin Engelschalk ha scritto: >Hello Michele, > >

Re: [sqlite] COUNT very slow

Quoth Michele Pradella , on 2010-09-24 10:13:59 +0200: > I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(*) from log" statement is > extremely slow, it takes me about 9-10 minutes! > I try with: > select

Re: [sqlite] COUNT very slow

Hello Michele, sqlite does not remember the number of records in a table. Therefore, counting them requires to scan the full table, which explains the slow perfornamce. This topic has been discussed previously in this list. See

Re: [sqlite] COUNT very slow

On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote: > I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(*) from log" statement is > extremely slow, it takes me about 9-10 minutes! In SQLite, count() is obliged to

  1   2   >