[sqlite] Please test the latest SQLite snapshot

Please test the latest snapshot of SQLite in your applications and report any performance regressions to this mailing list, or directly to me at d...@sqlite.org. You can get a tarball or ZIP archive of the latest raw sources from Fossil at http://www.sqlite.org/src/info/trunk or you can pull

Re: [sqlite] problem with migration from 1.0.66 to 1.0.82 (DateTimeKind)

Olexandr Kravets wrote: > > Some columns in > database are used to store date and time in UTC and some in local. > You are storing both date/time values in both local time and UTC time in the same database? This is problematic, see below. > > I came to following conclusion. If I migrate >

Re: [sqlite] Help to create view of my data

On 9 Nov 2012, at 4:16pm, Clemens Ladisch wrote: > Just use SQL to generate the SQL query: > > SELECT 'SELECT units.id AS "unit id"' AS SQL > UNION ALL > SELECT ', cv' || id || '.value AS ' || quote(name) FROM configtypes > UNION ALL > SELECT 'FROM units JOIN unit_types ON

[sqlite] problem with migration from 1.0.66 to 1.0.82 (DateTimeKind)

Hello all, Currently, I am using version 1.0.66 SQLite.NET and all datetime columns by default have following format: 2012-11-05 16:05:10.3702633 Some columns in database are used to store date and time in UTC and some in local. Therefore, there is no way to see if column saved UTC

Re: [sqlite] Help to create view of my data

Clemens Ladisch wrote: > it is not possible to generate columns automatically from data values. "All problems in computer science can be solved by another level of indirection." (David Wheeler) Just use SQL to generate the SQL query: SELECT 'SELECT units.id AS "unit id"' AS SQL UNION ALL

Re: [sqlite] Help to create view of my data

Daniel Polski wrote: > I'm redesigning parts of my database to normalize it better [...] > The SELECT query should return all units, and for each configuration > option it should return either the stored value (if available) or null > if no such option exist for the unit. SQLite does not have

[sqlite] Help to create view of my data

Hello, I'm redesigning parts of my database to normalize it better and to make a more general solution available for other parts of the system. My goal now is to create a view which will let me do a select in the style of: SELECT (unit id) (general option value) (color value) (number value)

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

Thanks again Michael. > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: 09 November 2012 15:07 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Anomalously slow performance

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

Thanks again Clemens. > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Clemens Ladisch > Sent: 09 November 2012 15:08 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Anomalously slow performance on updates to >

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

O'Toole, Eamonn wrote: > DELETE FROM object > WHERE name = ? AND (created_at < ?) This needs an index with "name" as the *first* column. > query += ' AND deleted IN (0, 1)' If the name column is (nearly) unique, putting indexes on the

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

What I would do is find the max length of your data fields. Then dump the database, change the create table to use default values at those string lengths. Import it. See what that does for you. Or just reload your data the way you've been doing with the new default string lengths. Also a

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

O'Toole, Eamonn wrote: >> From: Clemens Ladisch >> Is it the DELETE or the INSERT that is slow, or both? > > Good question - I don't know > >> Are you addressing the records by ROWID or by name? > > By name So when the DELETE searches for the record to be deleted, it has to scan the *entire*

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: 09 November 2012 14:26 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Anomalously slow performance on updates to >

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

Sqlite_analyzer output: /** Disk-Space Utilization Report For container10M.db *** As of 2012-Nov-09 13:04:53 Page size in bytes 1024 Pages in the whole file (measured) 3412536 Pages in the whole file (calculated).. 3412535 Pages that store

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

>[O'Toole, Eamonn] This definitely sounds like it could be an issue. There is >just one container_stat entry >confirmed by sqlite3_analyzer output which I'll >post later). So you're saying that the single container_stat table :entry is >potentially being relocated very frequently the closer

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

Thanks Clemens, comments below: > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Clemens Ladisch > Sent: 09 November 2012 12:26 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Anomalously slow performance on

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

Thanks Michael. Comments below: > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: 09 November 2012 13:49 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Anomalously slow

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

Hmmm...is this a disk head seeking problem? You've got several TEXT entries which are either NULL or default to ''; I did a small experiment: on 3.7.13 CREATE TABLE container_stat ( account TEXT, container TEXT, created_at TEXT,

Re: [sqlite] Write to a View

Marco Bambini wrote: > From the official documentation: > You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. > However, in many cases you can use an INSTEAD OF trigger on the view to > accomplish the same thing. > > Can someone clarifies this point? > Seems like it is

Re: [sqlite] about date question

YAN HONG YE wrote: > sqlite> select > fmn,DATE('NOW'),supplierDate,date('now'),strftime(supplierDate),s > trftime(supplierDate)-date('now') from t93c_adl where > strftime(supplierDate) e('now') limit 10; > 854954|2012-11-09|12/01/2012|2012-11-09|12/01/2012|-2000 > > why

[sqlite] Write to a View

>From the official documentation: You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing. Can someone clarifies this point? Seems like it is possible to write into a view but I

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

O'Toole, Eamonn wrote: > We are seeing a performance anomaly on updates to existing object > records in the SQLite DB. If the container DB is sufficiently large > (about 10 million objects, 3.3GB) then the time to update records at > the beginning of the database by order of entry is anomalously

[sqlite] Anomalously slow performance on updates to early entries in a DB

Hello all, First of all, I'm a complete novice with respect to SQLite so apologies if there is an obvious answer to my question. I've also posted this question in IRC, and it was suggested that I post the question to this mailing list. We're running a test-bed of an object store (Openstack

Re: [sqlite] about date question

Quoth YAN HONG YE , on 2012-11-09 08:35:25 +: > > > //--why have noting in the result?

Re: [sqlite] about date question

Quoth YAN HONG YE , on 2012-11-09 08:28:53 +: > The result let crazy. Because they're STRINGS! There is NO DATE TYPE in SQLite! Not to mention if those values are intended to be ISO 8601, they should have zero padding; it should be "2012-09-07" and such---in which case

[sqlite] about date question

sqlite> select strftime(importdate) from t93c_adl3 limit 5; 2012-9-13 2012-9-5 2012-10-9 2012-10-9 2012-10-9 sqlite> select strftime('%y-%m-%d',importdate) from t93c_adl3 limit 5;

[sqlite] about date question

The result let crazy. sqlite> select distinct importdate from t93c_adl3 order by importdate desc limit 12; 2012-9-7 2012-9-6 2012-9-5 2012-9-4 2012-9-3 2012-9-29 //---here , why? 2012-9-28 2012-9-27 2012-9-26 2012-9-25 2012-9-24 2012-9-20 sqlite>

Re: [sqlite] about date question

Quoth YAN HONG YE , on 2012-11-09 05:45:06 +: > why use supplierDate,date('now'),strftime(supplierDate) the result is not > same result format? > the one is 2012-11-09 > another is 12/01/2012 > ? > and how to get the subtraction of two column? SQLite calendar functions