Thanks Benjamin.
I added COUNTER table and triggers as you have said. Because I use COPY
command to import data file, triggers did not work. After copying had
completed, I found COUNT field in COUNTERS table unchanged (that is 0).
Data transfer occurs at program startup and it is a lengthy process by
itself. So it would not be a big problem, if I make the user wait for
extra 5 seconds while SQLite executes COUNT(*) to update COUNTERS
table. After all, I can use COUNTERS table safely for new inserts and
deletes with the help of triggers.
Thanks for your help.
--- [EMAIL PROTECTED] wrote:
> ----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 31/03/2004 10:13 AM
> -----
>
>
> Ben Carlyle
> 30/03/2004 11:17 AM
>
>
> To: "Balthasar Indermuehle" <[EMAIL PROTECTED]>@CORP
> cc:
> Subject: RE: [sqlite] Is using max(rowid) instead of
> count(*) safe?
>
>
>
>
>
>
> "Balthasar Indermuehle" <[EMAIL PROTECTED]>
> 30/03/2004 12:02 AM
>
>
> To: <[EMAIL PROTECTED]>
> cc:
> Subject: RE: [sqlite] Is using max(rowid) instead of
> count(*) safe?
>
>
> > I created a test database on my laptop (winxp) with my tester app.
> > Inserted 1 million records with random data. Here's how fast this
> works,
> > MAX takes .168ms (milliseconds, not seconds!), count() with a where
> > clause takes 266ms.
>
> Yes, sqlite is pretty quick at doing a table-scan. On the other hand,
> the
> whole zen of database querying is about avoiding the table scan. A
> one-off
> query may not make this obvious, but a number of queries on a much
> larger
> database can certainly make things take longer. This is especially
> true if
> you have a large amount of data that has to be read from disk.
>
> > And this is is with itunes and more running at the same time on the
> > machine... Maybe you didn't index the table? Indexing makes all the
> > difference in the world.
>
> Indexing won't help a COUNT(*). COUNT(*) would probably be slower if
> it
> used an index. Instead, it uses the table's main B-tree and simply
> counts
> all entries.
>
> > Alle 08:54, luned́ 29 marzo 2004, Ali Sadik Kumlali ha scritto:
> > > I wonder if I could use max(rowid) safely instead of count(*).
> Does
> > > rowid increments by 1 for each new row?
> > As previously stated, it's not safe.
> > A workaround could be to create an additional table with one field
> and
> > one row, in which you put the number of rows of the other table. At
>
> > every insert or delete on the big table, you should update the
> value of
> > this counter (the best way I can think of is using a trigger, with
> a
> > single atomic update: UPDATE table_counter SET counter = counter +
> 1).
>
> The detailed solution looks something like this:
> CREATE TABLE counters (name, count);
> INSERT INTO counters VALUES("foo", 0);
> CREATE TRIGGER foo_insert_counter AFTER INSERT on foo
> BEGIN
> UPDATE counters SET count = count + 1 WHERE name = "foo";
> END;
> CREATE TRIGGER foo_delete_counter AFTER DELETE on foo
> BEGIN
> UPDATE counters SET count = count - 1 WHERE name = "foo";
> END;
>
> Benjamin.
__________________________________
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
http://taxes.yahoo.com/filing.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
