[yunqa.de] Re: Why is count(*) so slow?

  • From: Delphi Inspiration <delphi@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Fri, 01 Aug 2008 10:21:38 +0200

Kai Peters wrote:

>am evaluating DISQLite and have just executed a select count(*) form t via the 
>latest SQLiteSpy version against the million record table from the demos (I 
>actually loaded 2,000,000 recs) and it takes 120 seconds! on a reasonably fast 
>machine instead of the fraction of a second I expected.
>
>What is up with that?

SQLite does not store the table record count as part of the database. This idea 
was considered but rejected that when originally designing the SQLite file 
format. Keeping a count involves extra I/O which slows down every INSERT or 
DELETE operation. Users who do not use count(*) should not have to pay that 
penalty. Consequently, SQLite must iterate all records to determine the 
count(*), which takes its toll for some million records.

As a workaround, it is possible to 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;

The "count(*)" result will be available immediately.

Ralf 

_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



Other related posts: