Hi Navin
I've compared with PostgreSQL. It's twice as as "fast" as SQLite with
100 mio. records on my old laptop - but still too slow using count().
So, as Eduardo suggested, you have to solve this problem with a
separate table and triggers, like shown below.
Yours, S.
-- Create test table
CREATE TABLE test (id INTEGER PRIMARY KEY, r REAL, t TEXT );
-- Fill test table with test data...!
INSERT INTO test (r,t)
WITH RECURSIVE
cte(r,t) AS (
SELECT random(), hex(random()*1000)
UNION ALL
SELECT random(), hex(random()*1000)
FROM cte
LIMIT 10000000 -- 10 mio.
)
SELECT * FROM cte;
-- Create auxiliary table
CREATE TABLE count_statistics (
table_name TEXT not NULL,
row_count INTEGER
);
CREATE TRIGGER count_statistics_delete_test
BEFORE DELETE ON 'test'
FOR EACH ROW BEGIN
UPDATE count_statistics SET row_count=row_count-1 WHERE table_name='test';
END;
CREATE TRIGGER count_statistics_insert_test
BEFORE INSERT ON 'test'
FOR EACH ROW BEGIN
UPDATE count_statistics SET row_count=row_count+1 WHERE table_name='test';
END;
-- initialize/update/reset count_statistics
UPDATE count_statistics SET row_count=(
SELECT count(*) FROM test
) WHERE table_name='test';
.timer on
SELECT max(id) FROM test;
SELECT count(*) FROM test; -- slow!
-- This is the count replacement:
SELECT row_count FROM count_statistics WHERE table_name='test'; -- fast!
-- Done.
2015-01-25 20:05 GMT+01:00 Navin S Parakkal <nav...@hp.com>:
>
> On Saturday 24 January 2015 03:15 PM, Stefan Keller wrote:
>>
>> I think it's time for a serious simple benchmark with sqlite and say
>> PostgreSQL.
>> PostgeSQL also had performance problems time ago but this has been
>> resolved.
>> Can you describe the hp_table1 schema (CREATE TABLE statement...) and
>> some data (INSERTs)?
>>
>>
> Yes though a avid fan of sqlite, have to reconsider it for scalability
> issues. Everything about the table is present in the archives and thread .
>
>
>
> On Sunday 25 January 2015 06:17 AM, Eduardo Morras wrote:
>>
>> You can use PostgreSQL, using part of Oracle licence cost for better
>> hardware and a dinner for the team.
>>
> Yes that is an option we have to consider seriously. I thought we were doing
> something wrong like creating the index or maybe tuning page size.
>
>
> I'm still stuck.
>
>
>
>
> I maybe completely wrong but i'm guessing your B-tree index is static.
> Something in that doesn't support dynamic updation or some bug causing it
> have linear update .
>
>
> http://pastebin.com/davqKcF8
>
>
>
> Reposting below if you have missed it.
>
>
> My process.csv is around 27G. I've gzipped it and put
> atftp://navinps:sqlit...@h2.usa.hp.com as process.csv.gz
>
> There is only 1 file there.
> md5sum process.csv.gz
> e77a322744a26d4c8a1ad4d61a84ee72 process.csv.gz
>
> [root@centosnavin sqlite-autoconf-3080801]# cat sqlite3commands.txt
> CREATE TABLE [hp_table1] ( InstanceId INTEGER, LogTime INTEGER, ArrivalTime
> INTEGER, CollectionTime INTEGER, [dml_PROC_TIME] TIME, [dml_PROC_INTERVAL]
> INTEGER, [dml_PROC_INTEREST] TEXT, [dml_PROC_TOP_CPU_INDEX] INTEGER,
> [dml_PROC_TOP_DISK_INDEX] INTEGER, [dml_PROC_STATE_FLAG] INTEGER,
> [dml_PROC_RUN_TIME] REAL, [dml_PROC_STOP_REASON_FLAG] INTEGER,
> [dml_PROC_INTERVAL_ALIVE] INTEGER, [dml_PROC_STOP_REASON] TEXT,
> [dml_PROC_STATE] TEXT, [dml_PROC_PRI] INTEGER, [dml_PROC_NICE_PRI] INTEGER,
> [dml_PROC_CPU_LAST_USED] INTEGER, [dml_PROC_CPU_SWITCHES] INTEGER,
> [dml_PROC_IO_BYTE] REAL, [dml_PROC_VOLUNTARY_CSWITCH] INTEGER,
> [dml_PROC_FORCED_CSWITCH] INTEGER, [dml_PROC_IO_BYTE_RATE] REAL,
> [dml_PROC_CPU_TOTAL_UTIL] REAL, [dml_PROC_CPU_TOTAL_TIME] REAL,
> [dml_PROC_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYS_MODE_TIME] REAL,
> [dml_PROC_CPU_USER_MODE_UTIL] REAL, [dml_PROC_CPU_USER_MODE_TIME] REAL,
> [dml_PROC_THREAD_COUNT] INTEGER, [dml_PROC_CPU_ALIVE_TOTAL_UTIL] REAL ,
> [dml_PROC_CPU_ALIVE_USER_MODE_UTIL] REAL,
> [dml_PROC_CPU_ALIVE_SYS_MODE_UTIL] REAL, [dml_PROC_CPU_SYSCALL_UTIL] REAL,
> [dml_PROC_CPU_SYSCALL_TIME] REAL, [dml_PROC_CHILD_CPU_USER_MODE_UTIL] REAL,
> [dml_PROC_CHILD_CPU_SYS_MODE_UTIL] REAL, [dml_PROC_CHILD_CPU_TOTAL_UTIL]
> REAL, [dml_PROC_DISK_PHYS_READ] INTEGER, [dml_PROC_DISK_PHYS_READ_RATE]
> REAL, [dml_PROC_DISK_PHYS_WRITE] INTEGER, [dml_PROC_DISK_PHYS_WRITE_RATE]
> REAL, [dml_PROC_DISK_PHYS_IO_RATE] REAL, [dml_PROC_MEM_RES] REAL,
> [dml_PROC_MEM_SHARED_RES] REAL, [dml_PROC_MEM_VIRT] REAL,
> [dml_PROC_MEM_DATA_VIRT] REAL, [dml_PROC_MEM_STACK_VIRT] REAL,
> [dml_PROC_PAGEFAULT] INTEGER, [dml_PROC_PAGEFAULT_RATE] REAL,
> [dml_PROC_MINOR_FAULT] INTEGER, [dml_PROC_MAJOR_FAULT] INTEGER,
> [dml_PROC_MEM_LOCKED] REAL, [dml_PROC_DISK_SUBSYSTEM_WAIT_PCT] REAL,
> [dml_PROC_DISK_SUBSYSTEM_WAIT_TIME] REAL, [dml_PROC_PRI_WAIT_PCT] REAL,
> [dml_PROC_PRI_WAIT_TIME] REAL, PRIMARY KEY (InstanceId, CollectionTime))
> WITHOUT ROWID ;
>
>
> Also pasted with details at :
>
> http://pastebin.com/davqKcF8
>
>
> Thanks,
> Navin
>
>
>
> _______________________________________________
> 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
