Re: [sqlite] Performance increase between 3.7 and 3.8

Eduardo Morras-2 wrote > A ~8000MB db with app example data. More than 1000 query-corp created as > part of test driven development of the app. We have precalculated the > correct results in tables and its number of rows. No write, only read > queries and don't use other improvements like partial i

Re: [sqlite] Performance increase between 3.7 and 3.8

Eduardo Morras-2 wrote > I use a big test db to assure new versions of sqlite works properly. With > 3.7.15.2 it takes 43 minutes, with 3.8.8.2 on same hardware 27 minutes, > it's 16/0.43 = 37% less or 27/0.43 = 63% improve. Thanks, Eduardo. Could I ask you for a rough characterization of the test

Re: [sqlite] Performance increase between 3.7 and 3.8

Richard Hipp-3 wrote > From the description, your tests sound like you are pushing a bunch of > separate SQL statements into SQLite. In other words, the compute time > is likely dominated by the time need to parse the SQL and prepare > plans. Yes > Have you rerun the tests using prepared sta

[sqlite] Performance increase between 3.7 and 3.8

I know the reports about huge performance increase achieved within the last year. (Compliments for that.) However, those numbers ignore processor architecture and I/O. My question is a different one. What speed difference do you perceive in real-world applications? I know that there can't be any

Re: [sqlite] Huge WAL log

Thanks for the documentation update. >From my point of view I would invite more details related to the term "large transaction". Specifically the role of indexes is important. (They are often overlooked, added by an admin after the development is over etc.) > Defenses against this failure mode i

Re: [sqlite] Huge WAL log

Here is my final report. The problem was definitely in indexes. Simply the larger is the index table as compared to the page cache size, the faster growths the WAL log. My solution (all these measures were important): - Drop the index before the bulk insert, create it at the end. - Intermediate c

Re: [sqlite] Huge WAL log

I'll add the results from the latest test that was running for the last 2 hours: This time I did not use intermediate commits. Instead, I dropped the table indexes before the bulk insert started and re-created them at the end. The results after a few tables (most of them small, a few contained 50

Re: [sqlite] Huge WAL log

Dan Kennedy-4 wrote > Is it correct that you have a single transaction inserting lots of data > into a table with multiple indexes on it? Something like 1GB? Depends on. It is the best option from the application point of view. Other solutions introduce additional risks. Apparently, the answer is

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote >> Thanks to your post I discovered multiple-row inserts so that I now >> understand what you asked. > > Just a note that multiple-row inserts were added to SQLite relatively > recently (2012-03-20 (3.7.11)) and, because SQLite does only > database-level locking, its overhead

Re: [sqlite] Huge WAL log

RSmith wrote >> >> The code schema is as follows: >> >> foreach table >> { >> BEGIN >> INSERT INTO table VALUES() >> INSERT INTO table VALUES() >> ... >> COMMIT >> } >> >> Large column values are supplied as parameters, the rest (vast majority) >> is >> passed throu

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > Unlike a rollback journal a WAL file can have multiple copies of the same > page. > > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" > > If your inserted records is 5million separate insertions then each > ins

Re: [sqlite] Huge WAL log

Paul Sanderson wrote > So from yor main loop, expanding the following code may help us > understand. > > "insert all downloaded rows" The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large column

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote > However, other information in your message suggests that you have a > resource leak of some type somewhere. Especially, it should not take 12 > minutes to insert 3.5M rows into a simple table with an index or two > unless really long strings or blobs are involved. > > Unfor

Re: [sqlite] Huge WAL log

I'll add the results from additional tests. First of all, I forced a commit after each 100,000 records inserted into a single table. (A complication for us.) Some numbers for a table with a single index and 3,423,000 inserted records: Intermediate commits took subsequently 764 msec, 2164 msec, 4

Re: [sqlite] Huge WAL log

Simon Slavin-3 wrote >> - WAL log size 7.490 GB > > Please repeat your tests but as the first command after opening your > database file issue > > PRAGMA journal_size_limit = 100 > > With this change the WAL file may still grow to 7 GB while that particular > transaction is being executed bu

Re: [sqlite] Huge WAL log

Richard Hipp-3 wrote > What is your page size? 1024 Richard Hipp-3 wrote > Your original post said you inserted two rows for each transaction. > How big are those two rows? Sorry for misleading information. Here is a more formal algorithm: foreach table { BEGIN insert all downloaded ro

Re: [sqlite] Huge WAL log

Richard Hipp-3 wrote >> No other active readers or writers. > > Are you sure? Writers for sure. As far readers are concerned, the things are too complex to make an absolute statement. (I shall check once more.) However, I can add a few observations I made: WAL file size was about 70 MB (as repo

[sqlite] Huge WAL log

I understand that the WAL log uses less efficient storage rules than the real database, but this case was a real surprise for me. Here is the brief description. We start from an empty database, create a few tables (each having a few indexes), then begin a transaction, do a couple of inserts into t

Re: [sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

Thanks, Simon, perhaps I did not formulate may question clearly enough. The class of course works as you wrote, the only problem relates to the error handling. If you are interested, the C# wrapper (original code) is here

[sqlite] sqlite3_step and SQLITE_LOCKED/BUSY

I don't have any real problem this time, just wondered if our C# wrapper is programmed correctly/efficiently. Suppose we prepared a statement using sqlite3_prepare_v2(). Could please anybody explain how to treat above mentioned error codes returned by sqlite3_step()? Our C# wrapper (inspired by s

Re: [sqlite] Integrity check

Thanks, Simon. Simon Slavin-3 wrote > If possible, you should try to do your synchronisation when your app is > frontmost only. However, I understand that this may not be appropriate > for your app. Exactly, under normal circumstances the synchronization of our app is the topmost priority, henc

Re: [sqlite] Integrity check

Thanks, Simon. Simon Slavin-3 wrote > Your solution seems to do it by exchanging data accessed using the SQLite > API so you shouldn't have that sort of problem. Yes, only standard SQLite API is used. > I assume that you aren't using any PRAGMAs which speed up SQLite at the > expense of safety

Re: [sqlite] Integrity check

Richard Hipp-3 wrote > Have you reviewed the list of corruption causes at > http://www.sqlite.org/howtocorrupt.html and eliminated them all as > possibilities? Multiple times, but I did it again. In general I can exclude only a few points... 1.0 File overwrite by a rogue thread or process Exclude

Re: [sqlite] Integrity check

Thanks, may I ask about PRAGMA synchronous=Normal? The worst-case scenario I can imagine is that the app is killed by the OS when a checkpoint operation is in process... -- View this message in context: http://sqlite.1065341.n5.nabble.com/Integrity-check-tp77519p77558.html Sent from the SQLit

Re: [sqlite] Integrity check

Simon Slavin-3 wrote > On 27 Aug 2014, at 4:21pm, Jan Slodicka < > jano@ > > wrote: > >> - There is one potentionally risky operation that our app performs: The >> data >> exchange with a remote WebService. This can take long (10+ min). Users >> o

Re: [sqlite] Integrity check

Thank you for the answer. > Your custom collation function would be my prime suspect here. Yes, it was. Some time ago we really corrected a bug in the collation, which resulted in decreased number of user reports. Even later we switched to the ICU library, which - I suppose - should be relatively

[sqlite] Integrity check

A while ago I reported about DB corruption issues that we occasionally receive from our users. They always have the same pattern: - A few rowid's missing from a few indexes, and - A few "wrong # of entri

Re: [sqlite] LIKE operator and collations

Simon Slavin-3 wrote > On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote: > >> ​This is very interesting Jan. The only way this could fail is if the >> collation implementation does something funny if it encounters this >> character​, e.g. choose to ignore it when comparing. > > That cut

Re: [sqlite] LIKE operator and collations

Dominique Devienne wrote > On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka < > jano@ > > wrote: >> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + >> '\uDBFF\uDFFD'). > > make that > >

Re: [sqlite] LIKE operator and collations

Constantine Yannakopoulos wrote > On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka < > jano@ > > wrote: > I understand that it is difficult to find the least greater character of a > given character if you are unaware of the inner workings of a collation, > but maybe fi

Re: [sqlite] LIKE operator and collations

> ​It can be implemented if the definition of a collation is extended to be able to provide this information as Simon suggested. Sure, this could be done. But I am not sure whether it would be that usefull. For example I would not use it for my current application (C#). -- View this message in

Re: [sqlite] LIKE operator and collations

> However, I don't think it's necessary to solve this problem. Just don't try to optimize it. Whoever is doing the programming knows that pattern matching is slow. If they want a fast solution they'll use BETWEEN instead. And that will make them have to provide their own comparison strings. I

Re: [sqlite] LIKE operator and collations

> ​I understand that it is difficult to find the least greater character of a given character If you understand this, then you must admit that it cannot be done by the DB engine. Secondly: I pointed out to the problems when deriving the string "abd" from "abc". However, you suggest now a differ

Re: [sqlite] LIKE operator and collations

Hi Clemens, I know that link very well. I answered Constantine's statement who claimed that Sqlite implementation could be less restrictive. I just tried to explain why it is not possible. Jan -- View this message in context: http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp

Re: [sqlite] LIKE operator and collations

Sqlite LIKE optimization is described as follows: A like pattern of the form "x LIKE 'abc%'" is changed into constraints "x>='abc' AND x<'abd' AND x LIKE 'abc%'" If you look into sqlite code, then the string "abd" is generated from "abc" using a trivial algebra. However, this algebra won't work o

Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

s = idxs.TryGetValue("abc", out i);// false => BUG Note also that idxs.Add(null, 3) throws an exception. It means null string can't be added to the dictionary. (Which seems to be a reason behind using ColumnNameComparer.) In my opinion ColumnnNameComparer should be

Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

>> 2) SqliteDataReader.GetOrdinal() > This does not appear to be entirely compatible, most notable due to not > taking the keyInfo into account. Sorry, I forgot that we eliminated all keyInfo-related code. Anyway, it still would make sense to extend caching to all column names that were visite

Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

, j); _fieldIndexesLastIndex = j; _fieldIndexes.Add(jname, j); if (String.Compare(name, jname, StringComparison.OrdinalIgnoreCase) == 0) { r = j; break; } } } return r; } Best regards, Jan Slodi

[sqlite] Revisiting Collation Advice

For those that were interested in the discussion about iOS NOCASE collation here are some news. First of all we made the original algorithm more safe. The most important step was to limit the character set where ascii compariso

Re: [sqlite] Question about begin/commit

Under normal circumstances only these stmts perform some DB activity: - BeginTransaction - ExecuteNonQuery - Commit SqliteCommand constructor as well as the following lines (setting parameters and command text) are memory constructs that prepare data for ExecuteNonQuery. If everything works corre

Re: [sqlite] Collation advice

Big thanks, Igor. BTW, ICU surprised me. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668p70720.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] Collation advice

> btw. Muenster / Münster would fall back to full comparison due to the ü Yes, but what could fail then is for example Muenster / Muster. In case OS sorts ue/ü between uz and v - ascii and OS comparisons would yield different results. -- View this message in context: http://sqlite.1065341.n

Re: [sqlite] Collation advice

> That's not all that unusual: even in English, you might want to sort > Muenster and Münster next to each other. Thanks, Igor. Do you know more? Do you consider ascii comparison too dangerous? Jan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Collation-advice-tp70668

Re: [sqlite] Collation advice

Hi Dan > Sounds like an accident waiting to happen though. A developer could update a file using the sqlite shell. I forgot to mention that our DB uses custom enryption. Hence nobody can manipulate anything outside the application. The only exception is our custom-built sqlite shell, which is us

Re: [sqlite] Collation advice

Hi Simon > Given the declared setup we have two concerns: > 1) dependent on system locale which can be changed As I already mentioned in one of previous posts: If it proves to be a problem, we can remember used locale and force index rebuild if necessary. (Android version behaves this way alread

Re: [sqlite] Collation advice

> Unit Tests: > I would isolate the comparison in a core function, and primarily test that > core function Main application is written i C#. Sqlite and extensions are in dll, hence the main code cannot directly access the collation function. While we have low-level tests that are occasionally r

Re: [sqlite] Collation advice

P.S. Based on your doubts (this iOS code wasn't written by me, I normally work on other platforms), I decided to do some googling. And well, this article demostrates incompatibility between ascii and CFStringCompare. Per

Re: [sqlite] Collation advice

ent strings. Correct me if I am wrong, please. In fact, our Android version uses Unicode ICU library, but we want to avoid this in general. (Performance, size, maintenance.) As is Unicode testing concerned, I'll start a new post. Regards, Jan Slodicka -- View this message in context: http:

Re: [sqlite] Collation advice

> If you build a database using NOCASE version 1, then try to use the database with a different NOCASE version X where X>1, the database will appear to be corrupt, since the order of the indices will be incorrect. Thanks, the danger is clear. Regards, Jan Slodicka -- View this m

Re: [sqlite] Collation advice

> There's a problem here when comparing empty strings, that is, when nKey1 == nKey2 == 0. Good point. Must have been blind when I wrote this. > Why are you comparing elements at index 0 explicitly? memcmp() should do > the right thing. Yes, but it is faster this way. Re

Re: [sqlite] Collation advice

> Furthermore, the collation depends on the system locale. I am aware of that risk, but take it as acceptable. Should it present a problem in the future (highly doubtful), we can store used locale and rebuild indexes in case the system locale changes. Regards, Jan Slodicka -- View t

Re: [sqlite] Collation advice

this, but in our case it should not be that hot. It is an embedded application and the users are not supposed to manipulate the DB directly. (In some cases the OS efficiently hides the DB file.) Best regards, Jan Slodicka -- View this message in context: http://sqlite.1065341.n5.nabble.com

[sqlite] Collation advice

ected strings. This way one could verify relations such as reflexivity/symmetry/transitivity for specific sets of strings. The question is, however, where to find good test sets. Best regards Jan Slodicka static char g_bUseAscii=-1;SQLITE_EXTERN int sqlite3_collate(void *NotUsed, int nKey1, const

Re: [sqlite] System.Data.SQLite version 1.0.88.0 released

() reports errors that were previously reported, i.e. they must have been handled by the user already. I think the exception should not be thrown. Jan Slodicka -- View this message in context: http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-version-1-0-88-0-released-tp70425p70575.html Sent

Re: [sqlite] System.Data.SQLite version 1.0.88.0 released

Rather a question than a problem: Why the value getters (sqlite3_column_int() etc., but also some other functions such as sqlite3_column_name()) do not check for errors? SQLite documentation mentions at least possible memory problems. Wouldn't it be reasonable to check for those an throw an exce

Re: [sqlite] What can be deduced from integrity check

I am in process of checking everything possible and impossible and this point is on the list:) But in general the (system.data.sqlite) C# wrapper is constructed so that it throws an exception every time something goes wrong. However, I see some shortcomings in this layer such as for example ignori

Re: [sqlite] What can be deduced from integrity check

The same NOCASE implementation is used for all database operations. I am aware of the consequences of an incorrect collation implementation. However, I do not believe that they could explain index reference to records that were deleted. Do you have any explanation for this problem? -- View this

Re: [sqlite] What can be deduced from integrity check

> "Sync action"? What's that? Local database is synchronized with remote DB. Synchronization goes table by table (all table operations are grouped into a transaction) in several iterations. The algorithm is very complex, but in the final step all DB operations are linearized and executed on a sin

Re: [sqlite] What can be deduced from integrity check

Thanks. > the corruption is restricted to indices, so that it can be completely > repaired by running "REINDEX" Yes, REINDEX helps at least to the extent that integrity_check succeeds. Question: How can we recognize that integrity errors refer solely to an index corruption? (Considering the pos

Re: [sqlite] What can be deduced from integrity check

Hi Dominique > One usually gets a link to http://www.sqlite.org/howtocorrupt.html I know, I studied it multiple times. > It doesn't seem to be the case here, but Foreign-Keys are optional Right, it is just a confusing naming scheme - all indexes have the names starting with FK_. Actually, forei

[sqlite] What can be deduced from integrity check

From time to time we receive user reports about database crashes. Problems seem to be iOS-related. (Given the number of reports it would be a coincidence if other platforms worked flawlessly.) Our testers finally managed to hit the problem, so I have a corrupted DB file. On the other hand, that's

Re: [sqlite] Query optimization: Checking for existence before performing action

Hi Simon, the solution might look elegant, but it is probably a lot slower. I did not check this particular case, but in the past I found triggers to perform rather badly. Regards, Jan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-optimization-Checking-for-exi

[sqlite] FTS performance (mobile devices)

The title mentions mobile devices for the sole reason - the criteria for the performance are much higher than in the desktop world. For example our application (where we consider adding FTS) may run into memory problems on some weaker devices when we increase memory consumption by approx. 10 MB. In

Re: [sqlite] Pager bugs(?)

rds, Jan Slodicka Resco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pager bugs(?)

rs to the standard memory allocated by the page cache. (2000 pages by default.) Please, consider using less confusing wording at least in sqlite shell. Best regards, Jan Slodicka ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlit

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

Hi Dan > What are your settings for pragmas "cache_size", "journal_mode" and > "synchronous"? cache_size/synchronous - default values Don't remember, which journal_mode was used for testing. Should be WAL, but I might have been lazy to write needed code. The source code was meanwhile modified,

Re: [sqlite] Interrupting SQLite execution (mainly FTS)

Hi Simon. > I have much love for FTS but it chews up storage space, processing power, and therefore battery life, something fierce. You may end up with a working app but your users will find it reduces their battery life to an hour. Sounds unbelievable. Can you bring some example, please? In th

[sqlite] Interrupting SQLite execution (mainly FTS)

an attached DB.) Any advice? Thanks in advance, Jan Slodicka ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] More bugs

ables sqlite3_data/temp_directory do not work as advertized. In fact, using them results in access violation. The workaround is simple: Publish them with _declspec(dllexport) attribute. (Def file won't help.) Have a good day. With best regards, Jan Slodick

[sqlite] Bug in winAccess

Have a good day. With best regards, Jan Slodicka Resco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users