Re: [sqlite] what's the fastest way to get the record count of a table?
"Brandon, Nicholas \(UK\)" <[EMAIL PROTECTED]> wrote: > > I've noticed this question [making count(*) faster] has been raised > a few times in the past and the workaround proposed is to create a > "count" table to eliminate the need to do a table scan. I was > wondering whether it is worth adding this feature to Sqlite so that > a call to "select count(*)... " (which is not restricted with a > WHERE clause) is retrieved from an internal "count" table? i.e. > this performance workaround is part of the core sqlite code. > I considered but rejected that idea when originally designing the SQLite file format. Keeping a count involves extra I/O which slows down every INSERT or DELETE operation. I did not want users who do not use count(*) to have to pay that penalty. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] what's the fastest way to get the record count of a table?
[EMAIL PROTECTED] wrote: Hi, I want to check the record count of a table every 5 seconds. It seems there's only one way to get a table's record count: select coun(*) from ATable but if the table gets big(1,000,000 rows), it will get slow. Any good idea? Maintain a count in a table somewhere and keep it updated with triggers. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] what's the fastest way to get the record count of a table?
> > Hi, > > I want to check the record count of a table every 5 seconds. > > It seems there's only one way to get a table's record count: > > select coun(*) from ATable > > > > Create a separate table that has a single row and single > column for storing the record count: > > CREATE TABLE reccount(cnt INTEGER); > > Then create triggers that fire on every insert or delete and > update the record count table. > > CREATE TRIGGER rc1 AFTER INSERT ON tableA BEGIN > UPDATE reccount SET cnt=cnt+1; > END; > CREATE TRIGGER rc2 AFTER DELETE ON tableA BEGIN > UPDATE reccount SET cnt=cnt-1; > END; > > Then to get the record count do: > >SELECT cnt FROM reccount; > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > I've noticed this question has been raised a few times in the past and the workaround proposed is to create a "count" table to eliminate the need to do a table scan. I was wondering whether it is worth adding this feature to Sqlite so that a call to "select count(*)... " (which is not restricted with a WHERE clause) is retrieved from an internal "count" table? i.e. this performance workaround is part of the core sqlite code. Just an idea. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] what's the fastest way to get the record count of a table?
<[EMAIL PROTECTED]> wrote: > Hi, > I want to check the record count of a table every 5 seconds. > It seems there's only one way to get a table's record count: > select coun(*) from ATable > > but if the table gets big(1,000,000 rows), it will get slow. > Any good idea? Create a separate table that has a single row and single column for storing the record count: CREATE TABLE reccount(cnt INTEGER); Then create triggers that fire on every insert or delete and update the record count table. CREATE TRIGGER rc1 AFTER INSERT ON tableA BEGIN UPDATE reccount SET cnt=cnt+1; END; CREATE TRIGGER rc2 AFTER DELETE ON tableA BEGIN UPDATE reccount SET cnt=cnt-1; END; Then to get the record count do: SELECT cnt FROM reccount; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] what's the fastest way to get the record count of a table?
On 3/28/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, I want to check the record count of a table every 5 seconds. It seems there's only one way to get a table's record count: select coun(*) from ATable but if the table gets big(1,000,000 rows), it will get slow. Any good idea? Using a trigger to maintain a table count on an auxiliary table after every insert/delete is the prefered way. This was discussed before on the list and i believe there is an example on how to do this. Regards, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] what's the fastest way to get the record count of a table?
On 3/28/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, I want to check the record count of a table every 5 seconds. It seems there's only one way to get a table's record count: select coun(*) from ATable but if the table gets big(1,000,000 rows), it will get slow. Any good idea? create a table to hold the row count. Something like CREATE TABLE rowcounts (tablename TEXT, rowcount INTEGER) Add triggers that adjust the value of rowcount for each table on each INSERT/DELETE. Query table rowcounts instead of Count(*) table. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] what's the fastest way to get the record count of a table?
Hi, I want to check the record count of a table every 5 seconds. It seems there's only one way to get a table's record count: select coun(*) from ATable but if the table gets big(1,000,000 rows), it will get slow. Any good idea?
