Re: [sqlite] "INSERT" and "END TRANSACTION" time

If that were true, then I wouldn't be getting a very large speed-up when enveloping write ops in an explicit transaction, would I? On Mon, Mar 3, 2014 at 8:44 PM, Simon Slavin wrote: > > On 4 Mar 2014, at 1:15am, romtek wrote: > > > I have a question based on my observation. According to your

Re: [sqlite] Using WHERE clauses with REAL data

There are a small number of floating point numbers which are almost always exactly testable for equality, namely those you have confidence of exact storage. Positive zero is good, although negative zero sometimes causes problems; you can't always assume they're the same number. Smallish integers mu

Re: [sqlite] Using WHERE clauses with REAL data

Thanks for the quick response. That was what I was expecting. Regards, Donald. On 4 March 2014 15:20, Richard Hipp wrote: > On Mon, Mar 3, 2014 at 11:14 PM, Donald Shepherd > wrote: > > > It appears that using equals on floating point (REAL) data in WHERE > clauses > > doesn't necessarily wor

Re: [sqlite] Using WHERE clauses with REAL data

On Mon, Mar 3, 2014 at 11:14 PM, Donald Shepherd wrote: > It appears that using equals on floating point (REAL) data in WHERE clauses > doesn't necessarily work, presumably because of rounding errors - see below > for an example. Is this the case? Do I need to use BETWEEN instead of = > as I exp

[sqlite] Using WHERE clauses with REAL data

It appears that using equals on floating point (REAL) data in WHERE clauses doesn't necessarily work, presumably because of rounding errors - see below for an example. Is this the case? Do I need to use BETWEEN instead of = as I expect to be the case? Thanks, Donald. sqlite> select * from table

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On 4 Mar 2014, at 1:15am, romtek wrote: > I have a question based on my observation. According to your numbers for a > 5400 RPM disk, one write op should take about 11 ms. However, it often > takes only about 1 ms on HostGator drives on its shared hosting servers. > Are there drives that are SO

Re: [sqlite] "INSERT" and "END TRANSACTION" time

Thank for the clarification, Simon! I have a question based on my observation. According to your numbers for a 5400 RPM disk, one write op should take about 11 ms. However, it often takes only about 1 ms on HostGator drives on its shared hosting servers. Are there drives that are SO much faster th

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On 3 Mar 2014, at 9:11pm, romtek wrote: > Simon, does a real disk have to be a rotating hard disk? Is there problem > with SSDs as far as SQLite is concerned? SSDs aren't a problem, and SQLite works fine with them, but they change the timings associated with SQLite a great deal. Simplified e

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

>Another way to bust your data is to rely on RAID 5 or 6 or similar, at >least if the software does not take special care. > >When those mechanisms, updating a block always results in at least 2 disk >writes: The data block and the checksum block. There's a small time >window where only one of tho

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

At 21:35 03/03/2014, you wrote: ´¯¯¯ RAID3-4-5 was great when disks were expensive, in 80's an 90's. Now not. A minimal RAID5 needs 3 disks. A minimal RAID10 4. An enterprise disk SAS 15Krpm 146 GB 6G is $350, and a not enterprise grade cheaper and bigger. Now RAID1E and RAID10E give more flexi

Re: [sqlite] "INSERT" and "END TRANSACTION" time

> > a normal DB where writing does not happen often I would suggest a SSD... > but anything with a very active read/write cycle is best avoided If you were to quantify this, would you say that "active" starts at once per second, many times per second, once a min or...? On Mon, Mar 3, 2014 at 3:

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On 2014/03/03 23:11, romtek wrote: Simon, does a real disk have to be a rotating hard disk? Is there problem with SSDs as far as SQLite is concerned? No, what Simon is trying to point out is simply that the write performance experienced by L. Wood might be because journal writes might be syn

Re: [sqlite] "INSERT" and "END TRANSACTION" time

Simon, does a real disk have to be a rotating hard disk? Is there problem with SSDs as far as SQLite is concerned? On Mon, Mar 3, 2014 at 2:21 PM, Simon Slavin wrote: > > On 3 Mar 2014, at 6:03pm, L. Wood wrote: > > > _bind() on a prepared statement and execution of "INSERT" are taking 70% >

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

On Mon, 03 Mar 2014 17:36:10 +0100 Jean-Christophe Deschamps wrote: > > >It's how RAID5 works. Check this page docs http://baarf.com/ about > >it. > > This is utter BS. No. > Serious RAID controllers perform parallel I/O on as many drives that > are making up a given array. Of course I'm ta

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On 3 Mar 2014, at 6:03pm, L. Wood wrote: > _bind() on a prepared statement and execution of "INSERT" are taking 70% of > the time, but the "END TRANSACTION" only 30% of the time. > > The time between _bind() and execution of "INSERT" is roughly 50/50, so it's > a total of: > > _bind(): 35% >

Re: [sqlite] About "speed"

The result in a .csv format for Mikael, Sorry I'm very bad in html, I hope someone can re-post it in a nice-looking html table Nota : - Postgresql is not tuned at all, and its figures move a lot between two measures, - I couldn't do a Pandas measure because "not enough memory". "sequence of ope

[sqlite] "INSERT" and "END TRANSACTION" time

On a recent thread, some of you told me that if I wrap lots of "INSERT" statements inside "BEGIN TRANSACTION" and "END TRANSACTION", then executing "END TRANSACTION" will take the most (99%) of the time. This is not happening. _bind() on a prepared statement and execution of "INSERT" are taking

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/03/14 03:00, Simon Slavin wrote: > What the heck ? Is this a particular implementation of RAID ... The technical term is "write hole" and can occur at many RAID levels: http://www.raid-recovery-guide.com/raid5-write-hole.aspx You can mitiga

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

It's how RAID5 works. Check this page docs http://baarf.com/ about it. This is utter BS. Serious RAID controllers perform parallel I/O on as many drives that are making up a given array. Of course I'm talking of SAS drives here with battery backed-up controller. Kid sister RAID5-6 impleme

Re: [sqlite] Issues after upgrade to 3.8.2

Hey Guys, I still have this issue with SQLite 3.8.3.1, I could fix my case my changing line "LastPlayed] = (select case when [UserPlaylist].[LastPlayed] > s.[LastPlayed] then [UserPlaylist].[LastPlayed] else s.[LastPlayed] end from [Song] as s where s.[SongId] = new.[SongId])," to "[LastPlayed]

Re: [sqlite] SQLite destroys civilization.

LOL! Hopefully they wrote credit at the top of the source file. I saw season 1 of the show. Aaron is a "good guy". http://en.wikipedia.org/wiki/Revolution_%28TV_series%29 A On Sun, Mar 2, 2014 at 9:40 PM, mm.w <0xcafef...@gmail.com> wrote: > LOL > > don't know if it will go thru see png > >

Re: [sqlite] SQLite destroys civilization.

LOL don't know if it will go thru see png layer or neuron out of bounds ! Best Regards. On Sun, Mar 2, 2014 at 2:04 PM, Richard Hipp wrote: > On Sun, Mar 2, 2014 at 12:34 PM, Richard Hipp wrote: > > > Reports on twitter say that the "nanobots" in the TV drama "Revolution" > > have source co

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

On Mon, 3 Mar 2014 11:00:47 + Simon Slavin wrote: > What the heck ? Is this a particular implementation of RAID or a > conceptual problem with how RAID is designed to work ? It sounds > like a bug in one particular model rather than a general problem with > how RAID works. It's how RAID5 w

Re: [sqlite] Virtual table API performance

We have gotten around this problem by defining "virtual" fields that contain a reference to the "current record" and the entrypoint of a wrapper around the xColumn function. That way only two fields get passed upwards through the virtual table stack and the top level virtual table's xColumn impl

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On 3 Mar 2014, at 8:18am, Markus Schaber wrote: > > Another way to bust your data is to rely on RAID 5 or 6 or similar, at > > least if the software does not take special care. > > > > When those mechanisms, updat

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

On 3 Mar 2014, at 8:18am, Markus Schaber wrote: > Another way to bust your data is to rely on RAID 5 or 6 or similar, at least > if the software does not take special care. > > When those mechanisms, updating a block always results in at least 2 disk > writes: The data block and the checksum b

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

Hi, sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On 3 Mar 2014, at 3:41am, romtek wrote: > [...] > > Here's a SQLite engineer writing about the same thing: section 3.1 of > > > > Your disk hardware, its firmware driver,