Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/27/2011 10:18 PM, YONGIL JANG wrote: > should I wait for newer version? You should provide sufficient information (ideally source code and data) so that other people can reproduce what you are experiencing. Roger -BEGIN PGP SIGNATURE-

Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased

Dear Mr. Binns, To test it simple and easy way, I just made test data and results on my build server. create table test (id integer primary key autoincrement, value text); insert into test(value) values ("This is test data."); insert into test(value) select value from test; insert into

[sqlite] Cannot create table named "ORDER"

I was to create a table named ORDER to keep track of customers' orders but there was an exception thrown enforcing me to use another name. I can have the job done perfectly by any other DBMS like MySQL, SQL Server. Please help me figure out whether it is a bug or not?

Re: [sqlite] Cannot create table named "ORDER"

On Sep 28, 2011, at 12:54 PM, Se7en SenSeS wrote: > I was to create a table named ORDER to keep track of customers' orders but > there was an exception thrown enforcing me to use another name. I can have > the job done perfectly by any other DBMS like MySQL, SQL Server. Please help > me

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

Am 27.09.2011 23:07, schrieb Patrick Proniewski: > On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: > >> gawk has fflush() > > > On 27 sept. 2011, at 20:29, Roger Andersson wrote: > >> stdbuf? unbuffer? > > > none of them is available out of the box on Mac OS X, or FreeBSD. > gawk can

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : >> I can't see why you would want to do this more than once every minute >> - or do you? > The granularity I'm looking for is between 1 second and 10 seconds. Cron is > not > an option here. I woke up this morning and there is a way that cron

Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased

Recompile with -Dfdatasync=fdatasync and rerun your test. Let us know the results, please. On Wed, Sep 28, 2011 at 4:31 AM, Felix.Jang wrote: > Dear Mr. Binns, > To test it simple and easy way, I just made test data and results on my > build server. > > > create table

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 28 sept. 2011, at 13:38, Paul Linehan wrote: >> The granularity I'm looking for is between 1 second and 10 seconds. Cron is >> not >> an option here. > > I woke up this morning and there is a way that cron *_could_* do what you > want. You appear to have figured out a way that suits you, but

Re: [sqlite] EXT : speeding up FTS4

Have you done "ANALYZE"? That might help. Also...try to arrange your joins based on record count (both high-to-low and low-to-high) and see what difference it makes. Since you have only one WHERE clause I'm guessing having project_ids as the first join makes sense. Michael D. Black Senior

Re: [sqlite] speeding up FTS4

P.S. Your projects table is missing project_start. So apparently these aren't the real create statements you are using. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased

Dear all, I'm so sorry! All of previous test result was my fault! To get correct information, I'd downloaded all source code again and tested with same configuration. (Of course, fdatasync is enabled by using -Dfdatasync=fdatasync) It shows almost same execution time for each version. I think

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 8:19 AM, Black, Michael (IS) wrote: > P.S. Your projects table is missing project_start. So apparently these > aren't the real create statements you are using. > > > > Sorry, I think that is the only table from which I snipped off information to make the post

Re: [sqlite] Still chasing "database schema has changed"

On 28 Sep 2011 at 00:25, Richard Hipp wrote: > On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater wrote: > >> On 27 Sep 2011 at 18:15, Richard Hipp wrote: >> >>> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater >> wrote:

Re: [sqlite] Still chasing "database schema has changed"

On 28 Sep 2011, at 3:01pm, Tim Streater wrote: > No, I've done no tests. I'm not concerned that each database be down to its > smallest possible size, merely that the app have a mechanism that, from time > to time, compresses certain databases through which most of the apps traffic > flows

Re: [sqlite] EXT : speeding up FTS4

On Sep 28, 2011, at 8:02 AM, Black, Michael (IS) wrote: > Have you done "ANALYZE"? That might help. > > Also...try to arrange your joins based on record count (both high-to-low and > low-to-high) and see what difference it makes. > > Since you have only one WHERE clause I'm guessing having

Re: [sqlite] EXT : speeding up FTS4

On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote: >WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) Why are you doing 'Datetime' here ? Not only does the conversion take time, but it means you can't usefully index either of those two columns. Can you instead store your stamps

Re: [sqlite] EXT : speeding up FTS4

On Sep 28, 2011, at 9:44 AM, Simon Slavin wrote: > > On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote: > >> WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) > > Why are you doing 'Datetime' here ? Not only does the conversion take time, > but it means you can't usefully index

Re: [sqlite] EXT : speeding up FTS4

On Sep 28, 2011, at 4:48 PM, Puneet Kishor wrote: > But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there > really is no such thing as DATETIME value. Internally, it is stored as TEXT > anyway. Or as a number. Your choice: • TEXT as ISO8601 strings ("-MM-DD

Re: [sqlite] EXT : speeding up FTS4

On 28 Sep 2011, at 3:48pm, Puneet Kishor wrote: > Could I? Sure, if I had known better. Should I? I would be happy to create a > new column, convert the values to julian days, and try that, but on a 27 GB > db, that would take a bit of a while. You only have to do it once, you can do it

Re: [sqlite] speeding up FTS4

Sounds like you may just be hitting disk i/o. Your "sys" numbers seem to indicate that. How much memory does your machine have? How much time does each WHERE clause take? select count(*) from project where project_id = 3; select count(*) from fts_uri MATCH 'education,school'; select

Re: [sqlite] EXT : speeding up FTS4

On 28 Sep 2011, at 3:52pm, Petite Abeille wrote: > On Sep 28, 2011, at 4:48 PM, Puneet Kishor wrote: > >> But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there >> really is no such thing as DATETIME value. Internally, it is stored as TEXT >> anyway. > > Or as a number.

Re: [sqlite] Still chasing "database schema has changed"

On Sep 28, 2011, at 1:25 AM, Richard Hipp wrote: > Hence I ask: have you actually measured the difference? Please join the BAAG party :) http://www.battleagainstanyguess.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] speeding up FTS4

Your change to numeric date/time may not take a long as you think. drop any indexes on project_start and downloaded_on; update projects set project_start=julianday(project_start); update uris set downloaded_on=julianday(downloaded_on); Recreate indexes. Modify your code to insert

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote: > Your change to numeric date/time may not take a long as you think. > > > Took an hour and a half. Step 1: Alter all tables with datetime columns, converting those columns to integer; Step 2: Update all tables setting new datetime

Re: [sqlite] speeding up FTS4

On 28 Sep 2011, at 6:44pm, Puneet Kishor wrote: > Step 4: Run the following query > > SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on > FROM fts_uri f > JOIN uris u ON f.uri_id = u.uri_id > JOIN feed_history fh ON u.feed_history_id =

Re: [sqlite] speeding up FTS4

strftime returns a text representation. So you didn't really change anything. You need to use juliandays() as I said. And you want a REAL number...not integer...though SQLite doesn't really care what you call it. It's more for your own reference. You just added a bunch more strings

Re: [sqlite] Sqlite shell's .import does not work with attached db's, add wiki note?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/26/2011 06:03 PM, yary wrote: > I noticed that the sqlite shell won't ".import" into an attached database: You'll be pleased to know that the team have now fixed the bug. It will be in the next SQLite release.

Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)

Richard, That is my conclusion as well. From what I have read online, JFFS doesn't support mmap. Would it be possible to place those files in tmpfs instead? Is there logic to do that? Korey -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] speeding up FTS4

On 09/28/11 20:14, Black, Michael (IS) wrote: strftime returns a text representation. So you didn't really change anything. You need to use juliandays() as I said. And you want a REAL number...not integer...though SQLite doesn't really care what you call it. It's more for your own

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote: > strftime returns a text representation. So you didn't really change anything. > That's not true at all. I added u_downloaded_on (u_ for unixtime) CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT, uri_html TEXT,

Re: [sqlite] speeding up FTS4

'scuse meI was wrong (again)...I guess strftime does return an integerseems to me that belies the name as it's a mismatch to the unix function. select strftime('%s','now'); 1317236583 But I think you may want: strftime('%s','now','unixepoch','localtime'); That works with the

Re: [sqlite] speeding up FTS4

On 09/28/11 21:10, Black, Michael (IS) wrote: 'scuse meI was wrong (again)...I guess strftime does return an integerseems to me that belies the name as it's a mismatch to the unix function. ? SQLite version 3.7.8 2011-09-19 14:49:19 Enter ".help" for instructions Enter SQL

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 9:00 PM, Puneet Kishor wrote: > If I understand correctly, the *size* of the database should not matter. Or, > at least not matter as much. So she said. But contrary to popular believe, size does matter. ___ sqlite-users mailing

Re: [sqlite] speeding up FTS4

What happens if you create an index on uris(feed_history_id) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Puneet Kishor

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 2:41 PM, Black, Michael (IS) wrote: > What happens if you create an index on uris(feed_history_id) > > > Yeah, I noticed that lacking as well. sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 2:26 PM, Roger Andersson wrote: > On 09/28/11 21:10, Black, Michael (IS) wrote: >> >> 'scuse meI was wrong (again)...I guess strftime does return an >> integerseems to me that belies the name as it's a mismatch to the unix >> function. >> >> > ? > SQLite version

Re: [sqlite] speeding up FTS4

On 09/28/11 21:55, Puneet Kishor wrote: Perhaps, but I have inserted that in my table where the column is INTEGER. sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1; integer -- OK! ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Sqlite shell's .import does not work with attached db's, add wiki note?

On Wed, Sep 28, 2011 at 2:21 PM, Roger Binns wrote: > On 09/26/2011 06:03 PM, yary wrote: >> I noticed that the sqlite shell won't ".import" into an attached database: > > You'll be pleased to know that the team have now fixed the bug. It will be > in the next SQLite

[sqlite] attach readonly db to main db that is readwrite

My primary database is opened for read/write. I use the ATTACH command to attach a second database that lives in a read-only filesystem. It appears to mostly work, but is there anything I should be aware of or concerned about? One thing I noticed is if I run "ANALYZE" once the read-only is

Re: [sqlite] speeding up FTS4

I have no idea if this would work...but...here's some more thoughts... #1 How long does this take: select count(*) from fts_uri match 'education school'; #2 Create a view on uris with just what you need and use that in your join (I'm guessing that uri_content takes up most of your

Re: [sqlite] speeding up FTS4

On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote: > I have no idea if this would work...but...here's some more thoughts... > > > > #1 How long does this take: > > select count(*) from fts_uri match 'education school'; > > > > #2 Create a view on uris with just what you need and

Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)

On Wed, Sep 28, 2011 at 2:40 PM, Korey Calmettes wrote: > Richard, > > That is my conclusion as well. From what I have read online, JFFS > doesn't support mmap. Would it be possible to place those files in > tmpfs instead? Is there logic to do that? > An early