Re: [sqlite] sqlite3 very slow even after creating without rowid

[Richard Hipp]

On 1/24/15, Keith Medcalf  wrote:
>
> You are using a WITHOUT ROWID table.  Any particular reason why?

I suggested to him that it might be faster.  Apparently I was wrong.  :-\

>Have you
> tried using an ordinary table?
> What type is your "TIME" field? Or did you mean TEXT but misspell it?
> Do you want the primary key columns to contain null, or is just defining
> things that are NOT NULL as being nullable just an oversight (or laziness)?
>
> ---
> Theory is when you know everything but nothing works.  Practice is when
> everything works but no one knows why.  Sometimes theory and practice are
> combined:  nothing works and no one knows why.
>
>>-Original Message-
>>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] On Behalf Of Parakkal, Navin S (Software Engineer)
>>Sent: Friday, 23 January, 2015 11:05
>>To: General Discussion of SQLite Database; d...@hwaci.com
>>Subject: Re: [sqlite] sqlite3 very slow even after creating without rowid
>>
>>Hi,
>>
>>> I also did another experiment. I created this table and did a vaccum
>>and then the select count(*) in sqlite3 was around 2 mins.
>>>
>>> When I create an index manually after the table is loaded (imported
>>from csv), select count(*) in sqlite3 was within 30 to 40 secs.
>>
>>>In the second case, to calculate count(*) SQLite was able to use the
>>index you had created.  Since this index was smaller than the table,
>>SQLite was able to count the entries in it faster.  The result would have
>>been the same if you had done whenever the >index had been created
>>
>>>CREATE TABLE
>>.>import
>>>CREATE INDEX
>>>time the 'select count(*) from hp_table1' command here
>>
>>
>>>should yield pretty-much the same result as
>>
>>>CREATE TABLE
>>>CREATE INDEX
>>>.import
>>>time the 'select count(*) from hp_table1' command here
>>
>>Actually this didn't give me what was expected. It also took more than 20
>>mins twice .
>>I'll rerun it again if you insist.
>>That is the reason I uploaded the file  to ftp and the schema.
>>
>>Also I saw that autoindexes were present for the table (primary keys).
>>
>>
>>>If you are using a table for which rows are INSERTed but never DELETEd,
>>then you will get the same result almost instantly using
>>
>>> select max(rowid) from hp_table1
>>
>>> instead of counting the rows.
>>
>>We purge data once a week automatically and it is configurable. So we
>>can't use the max(rowid) trick always. Yes it works if you don't DELETE..
>>
>>I'm doing all this on CentOS 7 x64.
>>I built sqlite myself with latest sqlite-autoconf-3080801
>>
>>Regards,
>>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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 very slow even after creating without rowid

[Keith Medcalf]

You are using a WITHOUT ROWID table.  Any particular reason why?  Have you 
tried using an ordinary table?
What type is your "TIME" field? Or did you mean TEXT but misspell it?
Do you want the primary key columns to contain null, or is just defining things 
that are NOT NULL as being nullable just an oversight (or laziness)?

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Parakkal, Navin S (Software Engineer)
>Sent: Friday, 23 January, 2015 11:05
>To: General Discussion of SQLite Database; d...@hwaci.com
>Subject: Re: [sqlite] sqlite3 very slow even after creating without rowid
>
>Hi,
>
>> I also did another experiment. I created this table and did a vaccum
>and then the select count(*) in sqlite3 was around 2 mins.
>>
>> When I create an index manually after the table is loaded (imported
>from csv), select count(*) in sqlite3 was within 30 to 40 secs.
>
>>In the second case, to calculate count(*) SQLite was able to use the
>index you had created.  Since this index was smaller than the table,
>SQLite was able to count the entries in it faster.  The result would have
>been the same if you had done whenever the >index had been created
>
>>CREATE TABLE
>.>import
>>CREATE INDEX
>>time the 'select count(*) from hp_table1' command here
>
>
>>should yield pretty-much the same result as
>
>>CREATE TABLE
>>CREATE INDEX
>>.import
>>time the 'select count(*) from hp_table1' command here
>
>Actually this didn't give me what was expected. It also took more than 20
>mins twice .
>I'll rerun it again if you insist.
>That is the reason I uploaded the file  to ftp and the schema.
>
>Also I saw that autoindexes were present for the table (primary keys).
>
>
>>If you are using a table for which rows are INSERTed but never DELETEd,
>then you will get the same result almost instantly using
>
>>  select max(rowid) from hp_table1
>
>> instead of counting the rows.
>
>We purge data once a week automatically and it is configurable. So we
>can't use the max(rowid) trick always. Yes it works if you don't DELETE..
>
>I'm doing all this on CentOS 7 x64.
>I built sqlite myself with latest sqlite-autoconf-3080801
>
>Regards,
>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

Re: [sqlite] Buffered stderr on Windows (Take 2)

[Guilhem Malichier]

> Date: Sat, 24 Jan 2015 07:18:46 -0500
> From: Richard Hipp 
> 
> On 1/24/15, Stephan Beal  wrote:
> > On Fri, Jan 23, 2015 at 5:18 PM, Guilhem Malichier 
> > wrote:
> >
> >> I've been experiencing an issue with SQLite's CLI tool on Windows 7, when
> >> used through a script or spawned process (not when used directly in the
> >> console).
> >>
> >
> > If i'm not mistaken, that was fixed just last week:
> >
> > http://sqlite.org/src/info/80541e8b94b713e8f9e588ae047ffc5ae804ef1c
> >
> > Or maybe this is a related problem, not quite the same.
> >
> 
> I think that was a slightly different problem.  The fix for the
> current problem was just checked in:
> https://www.sqlite.org/src/info/2a9ea9b4a7d6
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
>

Wow, that was fast!
On behalf of all Windows users (for what it's worth...), I'm sending you a big 
THANK YOU :)

Guilhem

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Eduardo Morras]

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 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. When I do a select count(*) on hp_table1 it takes
> more than 5 mins which is quite a huge time. This file is static for
> now ie we copied it from a production server for analysis. Now I
> create a index by create index nvnhpindex on hp_table1
> (column1,column2) . The primary key of the table hp_table1 is
> (column1,column2). It takes around some time (maybe 20 minutes or
> less , I went for lunch and came back , really didn't note the time).
> Now I do select count(*) on hp_table1 , it takes around 15 secs. This
> is what we want our objective. We want it to be fast. The create
> index has increased the size of hp.db to 18 GB. This is OK with us
> and the customers.
> 
> The problem is this is not a static database. We keep inserting data
> (insert rows every 10 secs or like atleast 1 minute ) and
> occassionally delete rows (like once in 5 days). This is a 24x7
> system.
> 
> 
> So to identify the problem , I created a empty_database similar to
> hp.db with no rows. I created a index on column1,column2 on
> empty_table1 inside empty_database.
> 
> Now I inserted the rows from csv (this CSV was created by .mode csv,
> output myhp.csv, select * from hp_table1).
> 
> The size of database is around 18GB (empty_database) with rows. Now I
> do a select count(*) on empty_table1 (actually it contains lots of
> rows like 100M+ records ) and it takes more than 5 mins. 5 mins is
> too much of a time for us to bear. The customer wants the information
> within a minute.
> 
> 
> Can you please help in resolving this problem ? We are planning to
> deploy this across 1+ nodes on Linux x64 on one customer and many
> other customers are going in the similar direction. 
> 
> How do we go about resolving this ie what should we do to create a
> table with sub minute access for 100-500 million . How do we create
> the indexes ? Any other performance incentives.

Use a trigger on insert and a trigger on delete that modifies a value on 
another table with current count(*) number. Table can be temporal if you want 
and stay in memory, but you should do a count(*) on application startup.

> Some say we should buy/use Oracle but I just am holding onto sqlite3
> assuming it would help me solve our problem.

You can use PostgreSQL, using part of Oracle licence cost for better hardware 
and a dinner for the team.

> Regards,
> Navin
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Lev]

On Sat, 24 Jan 2015 20:59:22 +
Simon Slavin  wrote:

> and set it to 6 (60 seconds) or so.

Okay, I try that, but I still don't understand how can a single threaded
application get a locked error.

Levente

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice needed for optimization options

[Eduardo Morras]

On Mon, 19 Jan 2015 13:30:24 -0500
Andy Jewell  wrote:

> I have a single threaded application, which at its heart is
> 
> while (true) {
>   open a database // any one of thousands, so I can’t just keep
> it open do a select
>   close the database
> }
> 
> With that part unchanged, does anyone have any optimization
> suggestions? I am compiling the amalgamation myself, so I’m open to
> both compile time and run time options. I don’t need to worry about
> other processes updating the database. I’m not looking for SQL advice
> at this time. 

If your application is single threaded, you can move the open/close out of 
while(true). You say that you can't keep it open, because you work with 
thousands databases, but you can keep them open with different db pointer 
structure (sqlite3 *), there's no limit to that. There's a limit (30/62) if you 
try to attach a database to another db already open.

> Openning the database with SQLITE_OPEN_READONLY was an enourmous
> help. Explicitly turning off threading didn’t seem to help much.
> Changing my compiler optimization level from -O2 to -O3 (gcc 4.4)
> made it larger but no faster.
> 
> 
> many thanks,
> adj
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked in PHP

[Simon Slavin]

On 24 Jan 2015, at 8:13pm, Lev  wrote:

> I sometimes get a database locked error when I access the database by
> calling the execute() call. This is on PHP.

Have you set a timeout ?  Immediately after opening the connection use



and set it to 6 (60 seconds) or so.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database locked in PHP

[Lev]

Hi list,


I'm currently working on a www frontend that uses SQLite.

I sometimes get a database locked error when I access the database by
calling the execute() call. This is on PHP.

Reading the document

http://www.sqlite.org/rescode.html#locked

I learned that locked error occurs only on the same database connection or
when shared cache is used.

I open the database with

$db = new SQLite3($db_filename);

then

$stmt = @$db->prepare($query);

$result = $stmt->execute();

Does these function calls enable shared cache? Or I can't see why I get
locking error.

Could you please help.

Thanks,
Levente

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Buffered stderr on Windows (Take 2)

[Richard Hipp]

On 1/24/15, Stephan Beal  wrote:
> On Fri, Jan 23, 2015 at 5:18 PM, Guilhem Malichier 
> wrote:
>
>> I've been experiencing an issue with SQLite's CLI tool on Windows 7, when
>> used through a script or spawned process (not when used directly in the
>> console).
>>
>
> If i'm not mistaken, that was fixed just last week:
>
> http://sqlite.org/src/info/80541e8b94b713e8f9e588ae047ffc5ae804ef1c
>
> Or maybe this is a related problem, not quite the same.
>

I think that was a slightly different problem.  The fix for the
current problem was just checked in:
https://www.sqlite.org/src/info/2a9ea9b4a7d6

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Stefan Keller]

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 hp_table1 schema (CREATE TABLE statement...) and
some data (INSERTs)?

Yours, S.


2015-01-24 10:33 GMT+01:00 Clemens Ladisch :
> 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 or indexes, and
>> is much faster than max(rowid) for huge tables.
>
> Max(rowid) has a special optimization and looks only at the last entry
> in the index.  It is what SQLite uses internally for tables without
> AUTOINCREMENT, and is actually faster than looking up the sequence value
> in a separate table.
>
>
> Regards,
> Clemens
> ___
> 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

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

[Clemens Ladisch]

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 or indexes, and
> is much faster than max(rowid) for huge tables.

Max(rowid) has a special optimization and looks only at the last entry
in the index.  It is what SQLite uses internally for tables without
AUTOINCREMENT, and is actually faster than looking up the sequence value
in a separate table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users