[sqlite] SSD with TRIM

By the way, I have a new MacBook Pro with a Solid State Drive which supports TRIM. Was running some unit tests which include plenty of SQLite operations. The apps in question use various recent versions of SQLite and don't use any PRAGMAs on any of them. Fast. Fasty fast. Speed is high.

Re: [sqlite] Slow commits

Thanks, you've been very helpful. Being a recent lurker here (but a seasoned developer), let me commend you for your outstanding work and support. Normand -Message d'origine- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Richard Hipp Envoyé 

Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 6:49 PM, Normand Mongeau wrote: > > >>> Begin by doing: >> >> PRAGMA synchronous=OFF; >> > > With the above, the total commitTransaction time goes down to 385 > milliseconds... Impressive. > > The "PRAGMA synchronous=OFF" command turns of

Re: [sqlite] Slow commits

On 2012-01-13 18:10, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 13/01/12 14:35, Normand Mongeau wrote: It gets worse. On a clean empty database, the same 534 transactions take 140 seconds. That's a not very impressive rate of 3.8 inserts per second. The FAQ says

Re: [sqlite] Slow commits

On 2012-01-13 17:45, Richard Hipp wrote: On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeauwrote: It gets worse. On a clean empty database, the same 534 transactions take 140 seconds. That's a not very impressive rate of 3.8 inserts per second. The FAQ says that

Re: [sqlite] Slow commits

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 13/01/12 14:35, Normand Mongeau wrote: > > It gets worse. On a clean empty database, the same 534 transactions > take 140 seconds. That's a not very impressive rate of 3.8 inserts > per second. The FAQ says that SQLite should be able to do a

Re: [sqlite] Some pieces of the puzzle are coming together

On Fri, Jan 13, 2012 at 4:49 PM, John Elrick wrote: > > 3.6.17 > > Procedure % TimeTimeCalls > _sqlite3_step 58.4% 1.795,052 > _memcpy 22.8% 0.691,342,957 > _memset 7.8% 0.24

Re: [sqlite] Slow commits

On 13 Jan 2012, at 10:35pm, Normand Mongeau wrote: > It gets worse. On a clean empty database, the same 534 transactions take 140 > seconds. That's a not very impressive rate of 3.8 inserts per second. The > FAQ says that SQLite should be able to do a "few dozen transactions per > second".

Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 5:35 PM, Normand Mongeau wrote: > > It gets worse. On a clean empty database, the same 534 transactions take > 140 seconds. That's a not very impressive rate of 3.8 inserts per second. > The FAQ says that SQLite should be able to do a "few dozen

Re: [sqlite] Slow commits

It gets worse. On a clean empty database, the same 534 transactions take 140 seconds. That's a not very impressive rate of 3.8 inserts per second. The FAQ says that SQLite should be able to do a "few dozen transactions per second". I'd be happy to see that. I don't have much experience

Re: [sqlite] Some pieces of the puzzle are coming together

On 13 Jan 2012, at 9:49pm, John Elrick wrote: > 3.6.17, > _sqlite3_exec calls _malloc 1,101 times > _sqlite3_step calls _malloc 1,812 times > > 3.7.9 > _sqlite3_exec calls _malloc 65,227 times > _sqlite3_step calls _malloc 47,109 times > > At this point I've exhausted my avenues of research.

Re: [sqlite] Some pieces of the puzzle are coming together

> At this point I've exhausted my avenues of research.  Does anyone have any > further ideas as to what may be going on which could cause this increase in > memory management activity?  Or any suggestions of where to look next? IIRC, SQLite 3.7.9 changed its PCACHE interface and thus page cache

Re: [sqlite] Some pieces of the puzzle are coming together

John, I read your previous post and unfortunately, your conversation with Richard didn't reveal much details. I worked with scenario similar tou yours (Delphi + statically linked sqlite) for several years and during this period an advanced admin/console form was developed that allows dynamical

[sqlite] Some pieces of the puzzle are coming together

Richard and all, On January 6, I wrote a posting (Problems encountered while upgrading Sqlite from 3.6.7 to 3.7.9) concerning a slow down noticed in upgrading our Delphi application from using 3.6.17 to 3.7.9. Richard asked for some more specific information including a replicatable case, which

Re: [sqlite] Slow commits

On 2012-01-13 15:35, Richard Hipp wrote: On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeauwrote: On 2012-01-13 15:23, Richard Hipp wrote: On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau>wrote: not really, no.

Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 3:34 PM, Normand Mongeau wrote: > > > On 2012-01-13 15:23, Richard Hipp wrote: > >> On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau> com >wrote: >> >> not really, no. This is a server that

Re: [sqlite] Slow commits

On 2012-01-13 15:23, Richard Hipp wrote: On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeauwrote: not really, no. This is a server that receives files, and the transaction below means a file has arrived. Does your server have a really, really slow disk drive?

Re: [sqlite] Slow commits

Record sizes are approx 480 bytes for tableA, 380 bytes for tableB and 800 bytes for tableC. Storage is my hard drive, which is a normal SATA disk. On 2012-01-13 15:23, Stephan Beal wrote: On Fri, Jan 13, 2012 at 9:19 PM, Normand Mongeauwrote: not really, no. This

Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 3:19 PM, Normand Mongeau wrote: > not really, no. This is a server that receives files, and the transaction > below means a file has arrived. > Does your server have a really, really slow disk drive? Transaction commit normally takes

Re: [sqlite] Slow commits

On Fri, Jan 13, 2012 at 9:19 PM, Normand Mongeau wrote: > not really, no. This is a server that receives files, and the transaction > below means a file has arrived. > > You haven't told us how big the records are. If you are storing, e.g., 2GB file uploads in each

Re: [sqlite] Slow commits

not really, no. This is a server that receives files, and the transaction below means a file has arrived. Normand On 2012-01-13 15:16, Simon Slavin wrote: On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote: begin immediate transaction insert 1 record in tableA insert 1 record in tableB

Re: [sqlite] Using non-aggregate columns in group by (with anadditional issue)

On Fri, Jan 13, 2012 at 9:10 AM, Igor Tandetnik wrote: > Dilip Ranganathan wrote: > > Suppose among emp1 and emp2, I only want to see the entry with the latest > > timestamp. > > select timestamp, value, person from mytable t1 > where rowid = ( >

Re: [sqlite] Slow commits

On 13 Jan 2012, at 7:57pm, Normand Mongeau wrote: > begin immediate transaction > insert 1 record in tableA > insert 1 record in tableB > insert 1 record in tableC > commit transaction > > Inserting 534 records takes about 75 seconds. Most of the time (about 71 > seconds) is spent on the

[sqlite] Slow commits

Hi, I have an app using sqlite, and the main insertion point is very slow on commitTransaction. This is what I do: begin immediate transaction insert 1 record in tableA insert 1 record in tableB insert 1 record in tableC commit transaction Inserting 534 records takes about 75 seconds. Most

Re: [sqlite] Solaris Sparc, SIGBUS core dump

Patch worked like a charm! I'm sorry i couldn't find the patch myself, seems i didnt hit the right words with google. Thanks a lot, you saved my life :P. On Fri, Jan 13, 2012 at 12:23 PM, Richard Hipp wrote: > 2012/1/13 Alejandro Martínez > >> Hello, >> >>

Re: [sqlite] Solaris Sparc, SIGBUS core dump

2012/1/13 Alejandro Martínez > Hello, > > I'm running this piece of software on 2 machines that are "equal". > > SunOS brsassmm090 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V445 > > In on of them, everything runs ok, but in the other (production :S) i > get a core

[sqlite] Solaris Sparc, SIGBUS core dump

Hello, I'm running this piece of software on 2 machines that are "equal". SunOS brsassmm090 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V445 In on of them, everything runs ok, but in the other (production :S) i get a core dump: In mdb: > ::status debugging core file of ftma_pss_cache

Re: [sqlite] Using non-aggregate columns in group by (with anadditional issue)

Dilip Ranganathan wrote: > Suppose among emp1 and emp2, I only want to see the entry with the latest > timestamp. select timestamp, value, person from mytable t1 where rowid = ( select rowid from mytable t2 where t1.person=t2.person order by value desc, timestamp

[sqlite] Possible enhancement request.

Hi All, This is my first post here so please be gentle! I'd like to make a suggestion for a future enhancement.I'm using the latest System.Data.Sqlite and C#, I've got a small database with a freetext (FTS3) table. When I query this table I use the Offsets function to get the position of the

Re: [sqlite] Need a sqlite c api that wrires data into a table.

If I understand your question, the answer is NO. There is NO function like sqlite3_insert_data_into_table(TableName, Data, FieldName). The SQL engine responsible for reading and writing data from and to tables only responds to SQL queries passed to it via functions like sqlite3_exec(). For

Re: [sqlite] Resetting virtual table

On Fri, Jan 13, 2012 at 3:02 PM, Dan Kennedy wrote: > On 01/13/2012 03:31 PM, Max Vlasov wrote: > >> As I see sqlite can accept the schema of a virtual table >> (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if >> I >> want to refresh not only data,

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

On Fri, Jan 13, 2012 at 6:22 AM, Simon Slavin wrote: > > On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote: > > > I have a table that looks like something like this: > > > >timestampvalue person > >

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

On 13-01-2012 12:07, Dilip Ranganathan wrote: > I have a table that looks like something like this: > > timestampvalue person > === > 2010-01-12 00:00:00 33 emp1 > 2010-01-12 11:00:00 22

Re: [sqlite] Using non-aggregate columns in group by (with an additional issue)

On 13 Jan 2012, at 11:07am, Dilip Ranganathan wrote: > I have a table that looks like something like this: > >timestampvalue person >=== >2010-01-12 00:00:00 33 emp1 >2010-01-12 11:00:00

[sqlite] Using non-aggregate columns in group by (with an additional issue)

I have a table that looks like something like this: timestampvalue person === 2010-01-12 00:00:00 33 emp1 2010-01-12 11:00:00 22 emp1 2010-01-12 09:00:00 16

Re: [sqlite] Resetting virtual table

On 01/13/2012 03:31 PM, Max Vlasov wrote: Hi, As I see sqlite can accept the schema of a virtual table (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I want to refresh not only data, but schema also, is it possible? For example, I already did a query for my virtual

Re: [sqlite] suppress "database is locked"?

Hello, What did you want that command to actually do ? Are you searching for short strings which occur in long strings ? Do you perhaps mean something like SELECT stringfield FROM mydb WHERE '%'||stringfield||'%' LIKE 'abcabcabcabc' basically yes. i did not kew the '%'||xxx||'%'

Re: [sqlite] Performance gain from ATTACHing small writeable DB to large readonly?

On 13 Jan 2012, at 8:45am, Ulf BJORKENGREN wrote: > I have a handful of quite populated tables that are only read, and two tables > with small number of rows that are written into. Right now it is all opened > as one readwriteable DB. > If I make the large tables into one readonly DB, and the

Re: [sqlite] suppress "database is locked"?

On 13 Jan 2012, at 8:06am, Hajo Locke wrote: > in my db-field there are always just short strings like: > > stringfield > - > abc > acd > bac > bca > > > the string which i get from script is in most cases much longer. > so i was not successful when selecting this way: > select

[sqlite] Performance gain from ATTACHing small writeable DB to large readonly?

I have a handful of quite populated tables that are only read, and two tables with small number of rows that are written into. Right now it is all opened as one readwriteable DB. If I make the large tables into one readonly DB, and the other two into one readwriteable DB, and the use ATTACH to

[sqlite] Resetting virtual table

Hi, As I see sqlite can accept the schema of a virtual table (Sqlite3_declare_vtab) only in the context of xCreate or xConnect. But if I want to refresh not only data, but schema also, is it possible? For example, I already did a query for my virtual table based on clipboard contents, the

Re: [sqlite] suppress "database is locked"?

Hello, On the other hand, it looks like all that extension does is implement some regex stuff, and you might be able to do what you want to do with SQLite's "LIKE" operator. hmm, like operator would be the 1st choice, but this is not working in my case. in my db-field there are always just