Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

Hi Navin, Excuse me if some of the points below repeat things you already know. 1. Dr. Hipp's advice not to create redundant indexes was *not* intended to give you very quick row counts -- Simon Slavin et al had already given advice to speed up row counts -- and just now Stefen Keller even

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

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

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

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

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

Hi, On Monday 26 January 2015 12:35 AM, Navin S Parakkal wrote: 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

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

On Tue, 20 Jan 2015 12:12:00 + "Parakkal, Navin S (Software Engineer)" wrote: > Hello, > >I've few questions about sqlite3 , the database it creates. > Actually I'm finding lot of differences in performance. > > My story: > I have this sqlite3 database

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

Hi, Relying on sequence will not work (and is a wrong hack) since the use case includes deleting rows explicitly. 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

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

Jim Wilcoxson wrote: > If you have a table where rows are inserted but never deleted, and you > have a rowid column, you can use this: > > select seq from sqlite_sequence where name = 'tablename' This works only for an AUTOINCREMENT column. > This will return instantly, without scanning any rows

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

If you have a table where rows are inserted but never deleted, and you have a rowid column, you can use this: select seq from sqlite_sequence where name = 'tablename' This will return instantly, without scanning any rows or indexes, and is much faster than max(rowid) for huge tables. If no rows

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

On 20 Jan 2015, at 12:12pm, Parakkal, Navin S (Software Engineer) wrote: >When I do a select count(*) on hp_table1 it takes more than 5 mins which > is quite a huge time. If this is a table for which rows are inserted but never deleted, then you will find that

[sqlite] sqlite3 performance on select count very slow for 16 GB file

Hello, I've few questions about sqlite3 , the database it creates. Actually I'm finding lot of differences in performance. My story: I have this sqlite3 database called hp.db which is like 100+ million records for table1. The size of hp.db on Linux x64 (CentOS 7) is like 16 GB.

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

On 1/20/15, Parakkal, Navin S (Software Engineer) wrote: > Hello, > >I've few questions about sqlite3 , the database it creates. Actually I'm > finding lot of differences in performance. > > My story: > I have this sqlite3 database called hp.db which is like 100+