Re: [sqlite] "Common Table Expression"

[David de Regt]

RSmith - I said "often", not "entirely". :)

Discussion about how to better use SQLite for an already working implementation 
or for a proposed implementation is a great and proper use of the list.  Coming 
onto the list and asking how to store a simple branch-and-leaf tree structure 
in SQL is not.  Complaining about bugs in the "datetime" implementation when 
you clearly haven't read the data types help page to understand that it's being 
stored as text is also not a proper use of the list.  The SQLite docs are 
really quite good, some of the best I've seen on open source projects.

Yours was what I read as the shoot-down, not Simon's, FWIW.  Most people aren't 
designing their own ORMs/sql wrappers, they're using existing ones, often with 
limitations.  One could argue that they "should" write their own, but then that 
same argument could be used against the existence of all of managed code or 
reusable libraries, so that's a bit of a red herring.  Saying to comment the 
code isn't as helpful either.  You can comment things as much as you want, but 
if you have a 500 character complicated subquery, on which you want to then do 
a couple different things, writing "/* same thing as above*/" doesn't really 
help with either code maintainability or query optimization.  Being able to 
refactor duplicate code into a common function is kind of the cornerstone of 
programming languages.

CTEs aren't part of a variable-based system, they're a very simple 
#include-type syntax, that simultaneously gives a great hint to the QO that 
that statement should be executed as a separate subquery.  For whatever reason, 
it may actually be very complicated to implement this sort of thing into 
SQLite, but bringing in all sorts of things that should be unrelated is, well, 
irrelevant to the question at hand.

SQLite seems to be slowly moving in the direction of there being a bunch of 
compile-type options for advanced features (FTS, etc.)  CTE could be one of 
those, if it turns out to be a monster to implement.  I just have no idea about 
the underlying complexity of the VM to know whether that's really necessary or 
not.  I would assume it would be fairly simple, and also provide a great 
workaround mechanism for users experiencing difficulty getting the QO to do 
what they want under other circumstances.  However, that may be wrong on both 
counts. :)

-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Common Table Expression"

[David de Regt]

Sorry, this struck a bit of a sore spot with me, so I apologize for the small 
rant...  Feel free to completely ignore it.

CTEs are important for two reasons:
1. Simplification of query syntax.  One can argue that this isn't terribly 
important in a system designed as an embedded database, rather than a BI-grade 
data mining target. (though I'm sure many people are also using it as such).  
But, whatever.
2. Query optimization.  If I have to use the same subselect more than once in a 
query, it is a good optimization to tell the query parser to take a certain set 
of results, store them in a temporary resultset for this single query, then use 
that as a target of the second query.  So, yes, you could break out a CTE into 
create temporary table/do final query/drop temporary table, but that adds a 
layer of complexity that's not necessary in most database engines, and hence 
aren't found in most ORMs.  Yes, you could add custom code to support this, but 
when it often makes sense to do exactly what CTEs are meant to do, it seems 
like a no-brainer from a theoretical support standpoint.

While a query optimizer can potentially deduce the usefulness of the right 
order to do subqueries in, often times, as a programmer, I know that I need a 
single query that will reduce a large dataset to a very small one, and then I 
need a few nontrivial operations over the very small dataset.  Just saying "use 
multiple subselects" doesn't give any useful feedback about whether that will 
be properly optimized or not, and what caveats there are to the optimization 
process.  It also leaves you with a disgustingly long query in many cases.

Non-bug-related posts to this list often take the form of one of the following 
few categories:
1. Underqualified programmers asking for query help to do their jobs that a 
qualified programmer could easily do.  Doesn't belong on the list -- I'm sure 
there's a #sql-newbies list somewhere for things like this, and there should be 
a form-letter answer forwarding people to that.
2. Feature requests from underqualified programmers that don't realize the 
right way to do something.  Doesn't really belong on the list, though they 
mostly get shot down pretty quick or someone points out the obvious answer.  
Whatever, doesn't take up much mental/email bandwidth.  I can go either way on 
this.
3. True feature requests that are not implemented in SQLite and would be useful 
to a set of users/developers in some way/shape/form and is not directly 
workaroundable.

What I don't like is how often #3 requests gets shot down as being stupid.  
Yes, often a feature request doesn't really fit with the general mantra of 
SQLite, and it can be easily described as such.  However, many things are in a 
pretty grey area.  For example, CTEs would fit fairly nicely with the general 
mantra of SQLite, since it allows for making things smaller/simpler/more 
explicit for the QO, but it's being shot down as a non-useful feature that can 
be worked around.  Well, can it?  Or does sqlite perform the subselect multiple 
times if you mention the same query a couple different times in subtly 
different ways (case sensitive, etc.)?  There are important nuances here before 
completely dismissing something out of hand.

Just because there is another way to do something doesn't mean it's not a valid 
feature request to be prioritized with the rest of the feature requests.  
Saying "this is a potential future feature someday, but due to [some 
architecture issue] it's actually quite complicated to implement, and, as such, 
is unlikely to ever be actually implemented" is a completely valid answer from 
a project management perspective.  Simply dismissing something out of hand 
without a thorough explanation of why, however, isn't quite as valid.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of RSmith
Sent: Thursday, December 26, 2013 5:37 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] "Common Table Expression"

This reminds me of a plan to add RADAR dishes to cars to monitor other traffic 
and avoid collisions - brilliant idea but the detrimental effect on 
aerodynamics and limiting size-factor of already-built garages all over the 
world stifled enthusiasm.

Probably "Temporary Views" would be the exact thing that can achieve the same 
as CTE.  Further simplification might be implemented on your code, if in fact 
you are designing a system and not using some other SQLite-reliant system (in 
which case CTE might really help you).

To emphasize what Simon said: SQLite does not support a full syntactic script 
engine with variables and the like and isn't likely to expand by doing it 
and/or include CTE for the simple reason that the cost tradeoff in DB-Engine 
size vs. added functionality is non-sensical.  It has to function in many cases 
as a DB engine on embedded systems where space is a real concern, and those 
designers would dread 

Re: [sqlite] Is there

[David de Regt]

Maybe just use a connection list of some sort in a table?  When you connect, 
insert (and clear out any others from your client in case it crashed before), 
when you disconnect, remove it.  Pretty sure there's not a way to find open 
connections because the sqlite api closes and opens the DB with every 
transaction.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of veeresh kumar
Sent: Tuesday, December 17, 2013 4:42 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is there

Hi,
I want to detect if a sqlite database is already connected to an application?  
Is there anyway sqlite API available for this? I have a use case where 2 
different applications would try to connect to same database and I want to 
detect that and give info to the user.
Thank you
-Veeresh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL_STATIC unterminated strings, and sqlite3_result_text

[David de Regt]

Sorry to threadjack here, but this made me think of something...

Does this mean that sqlite3_column_text always makes a copy of the string to 
put a null terminator on the end?  My ORM uses std::strings in UTF8 everywhere, 
so does that mean it would be quite a bit faster to pull strings out using 
sqlite3_column_bytes?  When I'm inserting, I'm always using bound columns with 
sqlite3_bind_text, explicitly stating the number of characters (which doesn't 
include a trailing null).  So, I can easily reconstruct std::strings from a 
void * and a number of bytes, without sqlite having to make a copy of it and 
null terminate it for me, if sqlite is doing extra work in my circumstance.

Thanks!
-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Sunday, December 15, 2013 5:10 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL_STATIC unterminated strings, and sqlite3_result_text

On Sun, Dec 15, 2013 at 6:04 PM, James K. Lowden
wrote:

> http://www.sqlite.org/c3ref/result_blob.html
>
> I found a documentation typo and have a question about SQLITE_STATIC.
>
> The documentation for sqlite3_result_text says,
>
> "If the 3rd parameter is non-negative, then it must be the 
> byte offset into the string where the NUL terminator would appear if 
> the string where NUL terminated."
>
> I believe the intent is subjunctive,
>
> "if the string were NUL terminated".
>
> meaning no NUL is required.  It continues:
>
> "If the 4th parameter to the sqlite3_result_text* interfaces 
> or to sqlite3_result_blob is the special constant SQLITE_STATIC, then 
> SQLite assumes that the text or BLOB result is in constant space and 
> does not copy the content"
>

That statement would be more precise if it read:  "... does not copy the
content RIGHT AWAY..."   If you are inserting into the database, obviously
SQLite needs to copy the content in order to put it on disk.  If you later 
query for the content, then it will copy off of disk again.

If your statements is:

SELECT ?1;

And you bind a string that is not zero-terminated then request the result using 
sqlite3_column_text(), then SQLite will make a copy of the string in order to 
add the zero-terminator.  But, if you request the string using
sqlite3_column_blob() it will not make a copy.  In other words, it delays 
copying the string until it really must, and avoids making a copy if possible.

The application has no idea if and when SQLite might make a copy of the 
SQLITE_STATIC-bound string, so the application must guarantee that the string 
does not change until the statement is finalized or until the same parameter is 
rebound to a different value.



>
> My data are static (a read-only mmap'ed file), and the columns are not 
> null-terminated.
>
> The documentation for the usual column-reading function
> sqlite3_column_text() says it always returns a null-terminated string:
>
> "Strings returned by sqlite3_column_text() and 
> sqlite3_column_text16(), even empty strings, are always 
> zero-terminated."
>
> According to the above, the user gets a null-terminated string from 
> static data in a virtual table that is not copied and need not contain 
> a NUL terminator. That seems unlikely.  I would think either a copy is 
> made or the supplied static data must end in NUL.
>
> If sqlite3_result_text() is provided data with a nonzero length marked 
> as SQLITE_STATIC, where does the NUL come from that is returned to the 
> application by sqlite3_column_text()?
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What takes the most time

[David de Regt]

You'll need to use threading if you want to make queries abortable.  Another 
thread will need to call sqlite3_interrupt(handle) to abort it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of L. Wood
Sent: Wednesday, November 13, 2013 2:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What takes the most time

> Yes, _step would generally take the most time, as that's where the 
> actual work happens.
>
> Yes, you would call _step once for every row produced by SELECT (as 
> well as certain PRAGMAs), and only once for other statements that 
> don't produce a resultset.

Great, thank you. Another question:

If a single _step() call takes a long time to execute (a few minutes), is my 
only option to just wait for it? Does SQLite not allow any kind of callback 
mechanism for each _step() to indicate how many percentages are done (or how 
many bytes have been read/written), and allow for cancellation of the process?  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Interesting bug with sqlite3_limit and SQLITE_LIMIT_VARIABLE_NUMBER?

[David de Regt]

I just ran into a possible bug when trying to add some debugging ability to my 
app.  I have a global #define for the max # of bindable columns (to tweak for 
perf/statement cache/etc.), and I wanted to make it so I could set it to 0 to 
basically disable my statement preparation code, so that I could debug queries 
with actual values instead of useless bound parameters ("SELECT * FROM Table 
WHERE Id = ?1" isn't terribly useful in a log file).  One side effect of making 
this change is that, on connection, my code attempts to call:

sqlite3_limit(_handle, SQLITE_LIMIT_VARIABLE_NUMBER, MAX_PREPARED_BIND_COLS);

When I send in 0 for the MAX_PREPARED_BIND_COLS, the sqlite3_limit call returns 
the old value of the DB (32767), implying that the set failed (which should be 
fine - my code still won't bind any columns).  However, a few dozen queries 
later, my init code then attempts to insert into an FTS table with a very 
simple insert ("INSERT INTO Table(docid,Keywords) VALUES (8,'test')"), which 
immediately errors with SQLITE_ERROR, with the useless detail text of "SQL 
logic error or missing database".  If I simply comment out the (seemingly 
no-op) sqlite3_limit call, then everything works as normal, and I'm not binding 
any parameters (verified as such).

Is this a bug somewhere deep inside SQLite, or am I missing something? :)  I've 
worked around it simply by not performing the _limit call if the number is 0, 
which is fine, but this seemed like it might be exposing something bad you guys 
might know more about...

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MATCH with punctuation in parenthesis causing error

[David de Regt]

I realize that the query is being parsed with the enhanced query syntax since I 
added parenthesis (and have that compile flag enabled), but why does the 
exclamation point at the end cause an error?  It seems like it should be just 
ignored, given the default tokenizer.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kevin Benson
Sent: Sunday, November 3, 2013 10:55 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] MATCH with punctuation in parenthesis causing error

On Mon, Nov 4, 2013 at 1:38 AM, David de Regt <dav...@mylollc.com> wrote:

> I've worked around this issue a separate way, but I'd like to 
> understand what went wrong in the first place here.  I have an FTS3 
> table, and if I query with the following:
>
> SELECT * FROM table WHERE keywords MATCH '(blah!)'
>
> I get the following error:
> malformed MATCH expression:_[(blah!)]
>
> If I remove either the parenthesis or the exclamation point, or add 
> quotes around the parenthesis, the error disappears.  I've read over 
> all of the FTS docs and I don't see any reason why it shouldn't work 
> (though it should ignore it with the default tokenizer -- but if I 
> have a custom tokenizer, it should support the !, in theory).  Any ideas?
>
> Thanks!
> -David
>


I believe (for backward compatibility
reasons) the SQLITE_ENABLE_FTS3_PARENTHESIS option comes into your 
considerations as described in Compiling and Enabling FTS3 and FTS4 at:
http://www.sqlite.org/fts3.html#section_2 2

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MATCH with punctuation in parenthesis causing error

[David de Regt]

I've worked around this issue a separate way, but I'd like to understand what 
went wrong in the first place here.  I have an FTS3 table, and if I query with 
the following:

SELECT * FROM table WHERE keywords MATCH '(blah!)'

I get the following error:
malformed MATCH expression:_[(blah!)]

If I remove either the parenthesis or the exclamation point, or add quotes 
around the parenthesis, the error disappears.  I've read over all of the FTS 
docs and I don't see any reason why it shouldn't work (though it should ignore 
it with the default tokenizer -- but if I have a custom tokenizer, it should 
support the !, in theory).  Any ideas?

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Visual Studio 2013 Issue - GetVersionEx deprecated

[David de Regt]

A small issue has arisen that the local powers may want to be aware of.  In 
Visual Studio 2013, which uses the Windows 8.1 Platform SDK, they've marked 
GetVersionEx as deprecated, trying to supercede it through to VerifyVersionInfo 
and some other hardcoded macros based on that call that the new SDK implements. 
 Looking at the SQLite source, it looks like it only uses the GetVersionEx call 
to test for whether LockFileEx is supported on the current OS (whether it's 
NT-based or not).

While not a big deal in theory (that M$ wants to deprecate the call), you may 
want to put in a warning disable around the GetVersion calls, and eventually 
look into a better longer term solution.  As of right now, sqlite doesn't 
compile out of the box on VS2013 without disabling the warning in your project 
file.  You may just want to locally disable the warning around the call for 
now, with something like the following:

#pragma warning(push)
#pragma warning(disable:4996)
... blah blah GetVersionEx() ...
#pragma warning(pop)

...  or just find another method to check if LockFileEx is available (check the 
response from a GetProcAddr, etc.)

In our project we're just disabling 4996 at the project level, so it's not a 
blocking issue, but it may stymie other people, especially anyone picking up 
development with Windows 8.1 now.

Cheers,
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[David de Regt]

Seconded.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, September 10, 2013 3:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Hints for the query planner


On 10 Sep 2013, at 10:48pm, Tim Streater  wrote:

> likelihood (EXPR, value)

Best I've seen so far.  I know it makes no sense without the second parameter 
but I think if you're going to make use of a special non-standard optimisation 
system you can be expected to know exactly what it means.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables

[David de Regt]

Mayhaps the CROSS JOIN trick is your friend in this case, if you can be pretty 
sure of the correct direction of the join order. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Friday, September 6, 2013 7:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query preperation time does not scale linearly with 
growth of no. of tables

On Fri, 6 Sep 2013 17:29:25 +
Harmen de Jong - CoachR Group B.V.  wrote:

> > If I recall correctly, query planner's behavior is worst-case 
> > quadratic in the number of tables participating in the query. This 
> > includes tables mentioned directly, and also those pulled in 
> > indirectly via views, triggers or foreign keys.

Factorial, actually.  After three tables, each addtional table increases 
potential join sequences by roughly an order of magnitude.  

Given tables A, B, and C, 1 * 2 * 3 = 6: 

sqlite>  select a.T, b.T, c.T  from F a join F b on a.T  <> b.T join F c 
sqlite> on b.T <> c.T where a.T <> c.T order by a.T, b.T, c.T;
A   B   C 
A   C   B 
B   A   C 
B   C   A 
C   A   B 
C   B   A  

That's six plans for the order in which the system could choose to
access the tables to execute the query. 

Factorial grows quickly, as is demonstrated by adding table D:

sqlite> select a.T, b.T, c.T, d.T  from F a join F b on a.T <> b.T
> cross join F c on b.T <> c.T join F as d on c.T <> d.T where a.T <> 
> c.T and a.T <> d.T and b.T <> d.T order by a.T, b.T, c.T, d.T;
A   B   C   D 
A   B   D   C 
A   C   B   D 
A   C   D   B 
A   D   B   C 
A   D   C   B 
B   A   C   D 
B   A   D   C 
B   C   A   D 
B   C   D   A 
B   D   A   C 
B   D   C   A 
C   A   B   D 
C   A   D   B 
C   B   A   D 
C   B   D   A 
C   D   A   B 
C   D   B   A 
D   A   B   C 
D   A   C   B 
D   B   A   C 
D   B   C   A 
D   C   A   B 
D   C   B   A 

Pity the query optimizer facing  an 8-way join.  Or, say, a 20-table
join:

$ FACT=1; seq 20 | while read F;  do FACT=$(( ${FACT} * $F )); printf '% 3d! = 
%d\n'  $F ${FACT};  done
  1! = 1
  2! = 2
  3! = 6
  4! = 24
  5! = 120
  6! = 720
  7! = 5040
  8! = 40320
  9! = 362880
 10! = 3628800
 11! = 39916800
 12! = 479001600
 13! = 6227020800
 14! = 87178291200
 15! = 1307674368000
 16! = 20922789888000
 17! = 355687428096000
 18! = 6402373705728000
 19! = 121645100408832000
 20! = 243290200817664

There is such a thing as too many choices!  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Number of Colum!!!

[David de Regt]

The limit of 64 columns for a covered index to work should really go on that 
page too. :(

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Tuesday, August 13, 2013 7:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Number of Colum!!!

On Tue, Aug 13, 2013 at 07:41:25PM +0530, techi eth scratched on the wall:
> Is their any limit on number of Colum in SQLite3 table?

  Yes.   http://www.sqlite.org/limits.html#max_column



   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,  but showing 
it to the wrong people has the tendency to make them  feel uncomfortable." -- 
Angela Johnson ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any way to debug database is locked?

[David de Regt]

They're all being accessed from multiple threads within a single process app, 
with no external sources/connections.  This happens on both OSX and Win32.  
Always on a local disk.

I'm using the release amalg straight off the website (3.7.17), with 
SQLITE_ENABLE_FTS3/FTS3_PARENTHESIS as preprocessor options.

The database is fine when you restart the app -- it just seems to hit the 
locked error once in a blue moon, which I have set up to assert out right now 
to help me track it down.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, July 26, 2013 5:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Any way to debug database is locked?


On 27 Jul 2013, at 1:20am, David de Regt <dav...@mylollc.com> wrote:

> Is there any chance that the OS is still flushing the WAL changes to disk 
> post-closing the database in another connection, when another connection 
> tries to get an exclusive lock?

How are your various processes contacting the database file ?  Are they all 
running on the computer the database is kept on ?  Or are they using network 
access ?  If so, which NFS ?

Which pragmas have you used ?

Does your database pass the integrity check ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any way to debug database is locked?

[David de Regt]

We have a fairly complicated system of OS mutexes and using exclusive DB 
transactions to attempt to avoid database locking issues with SQLite.  It works 
great most of the time, but every few days one of our testers randomly runs 
into a database is locked error.  Every time it's been in a debugger, and we've 
checked all threads, none of which have any other open database commands.  So, 
while it's potentially an error in our locking logic, all of the locks are 
stack auto-construct/destructed, that I've been over a bunch of times, so it's 
fairly unlikely it's part of that.  We're using a simple busy handler that just 
returns 1, and we may want to add a sleep(0) into it, but either way, some sort 
of lock issue is occurring.

So, in an attempt to narrow down what's going on, is there any internal sqlite 
logic I can check or query against to see what other thread or even connection 
object currently has the lock on the database, so that I can start building 
some instrumentation to track it down when the issue happens?

Is there any chance that the OS is still flushing the WAL changes to disk 
post-closing the database in another connection, when another connection tries 
to get an exclusive lock?

Any other ideas for me to check?

Thanks for any ideas!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Large Database Windows vs Linux

[David de Regt]

It's the kind of useful help like this that makes me love the FOSS movement.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Walter Hurry
Sent: Friday, June 28, 2013 5:09 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Large Database Windows vs Linux

On Fri, 28 Jun 2013 15:22:57 -0600, Keith Medcalf wrote:

> That would explain why the best thing to be done with System Destroyer 
> (System Restore) is the same as the best way to handle the Hardware 
> Destroyer (Power Management) in Windows.  Disable it completely.
> 
The best thing to do with Windows is format the drive and install Unix or 
FreeBSD or Linux.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Large Database Windows vs Linux

[David de Regt]

FWIW, with our test and prod implementations, we find between a 3 and 10x 
(300-1000%) increase in almost all of our query times on Windows NTFS over OSX 
and iOS systems, depending on the query type.  We've done a bunch of testing 
and can verify it every time.

I started a thread on this ~7 months ago, and everyone else was able to verify 
it too with a simple implementation.  No explanations were provided outside of 
"Windows sucks", "Lol n00b", and "try linux". :)

And no, system restore isn't backing up our database files...

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Keith Medcalf
Sent: Friday, June 28, 2013 2:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Large Database Windows vs Linux


That would explain why the best thing to be done with System Destroyer (System 
Restore) is the same as the best way to handle the Hardware Destroyer (Power 
Management) in Windows.  Disable it completely.


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- 
> boun...@sqlite.org] On Behalf Of Roger Binns
> Sent: Friday, 28 June, 2013 15:07
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Large Database Windows vs Linux
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 28/06/13 13:17, RSmith wrote:
> > Best guess is some other system is trying to also look into that
> file,
> > making the Windows file manager stutter quite possibly the Win7 
> > Preview pane, a 3rd party file indexer service, an anti-virus
> system or
> > some other
> 
> Those are often called tag alongs since they tag along file i/o 
> activity.
> 
> Also beware that if your database (or any other file) as a particular 
> extension then Windows' System Restore will keep making backup copies 
> whenever it changes.  Here is the list of monitored extensions:
> 
> 
> http://msdn.microsoft.com/en-
> us/library/windows/desktop/aa378870(v=vs.85).aspx
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.12 (GNU/Linux)
> 
> iEYEARECAAYFAlHN+ugACgkQmOOfHg372QTTqgCeN3hNpHGON/CaoEx95y9605Qz
> GMIAnAtmuJzgo3wwLrZdGOIbA2yWwuRP
> =5SeP
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Covering Index?

[David de Regt]

Okay, but, it's essentially doing the equivalent of a "table scan" over the 
portion of the index where col1='a', so if col1='a' doesn't actually end up 
narrowing down the resultset hugely, you're still better off with a properly 
ordered index, correct?  (with YMMV disclaimers)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, June 4, 2013 7:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Covering Index?

On Tue, Jun 4, 2013 at 10:16 PM, David de Regt <dav...@mylollc.com> wrote:

> Quick question, SQLites,
>
> CREATE TABLE test (col1 text, col2 text, col3 text); CREATE INDEX 
> tindex ON test (col1, col2, col3);
>
> explain query plan
> SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';
>
> The above returns:
> SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)
>
>
> Which of the following is happening:
> 1. It's actually only using it as an "index" for the col1 check, and 
> then just using the fact that, somewhere, it contains the rest of the 
> data for the query inside the index, which is, in theory, faster than 
> table scanning the actual table for the results, but you're getting no 
> search performance gain out of anything other than the col1 part of the index.
> 2. It's actually somehow using it as an optimized index over both col1 
> and
> col3 conditions, but only saying col1 in the explain.
> 3. Other..?
>

It seeks to the first entry of the index where col1='a', then starts reading 
entries sequentially as long as col1 continues to equal 'a'.  Thus, only a 
small part of the index is examined, and the table itself is never even opened.


>
> Thanks!
> -David
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Covering Index?

[David de Regt]

Quick question, SQLites,

CREATE TABLE test (col1 text, col2 text, col3 text);
CREATE INDEX tindex ON test (col1, col2, col3);

explain query plan
SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c';

The above returns:
SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows)


Which of the following is happening:
1. It's actually only using it as an "index" for the col1 check, and then just 
using the fact that, somewhere, it contains the rest of the data for the query 
inside the index, which is, in theory, faster than table scanning the actual 
table for the results, but you're getting no search performance gain out of 
anything other than the col1 part of the index.
2. It's actually somehow using it as an optimized index over both col1 and col3 
conditions, but only saying col1 in the explain.
3. Other..?

Thanks!
-David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Yeah, the index maintenance is enormous, but it means that the select times are 
fast.  Medical info systems are usually pretty heavily oriented toward fast 
read operations.

Our table here has a set of repeated substructures (8-column structures that we 
have 6 of, with certain fields used on each, since the type isn't fully 
repeating.)  However, we use the whole subset of fields the vast majority of 
times we pull it, and so it's much faster to pull it out of the flat structure 
into our internal structure instead of doing the top select, then selecting all 
the subtypes.  The reverse, on insert, is true as well.  One insert is better 
than a master insert and then blowing away a bunch of subtable rows and then 
re-inserting.

We're changing our data model generation tool to handle all of the indexes on 
our schema, and to make sure to output all of those first in the table 
generation order.  That'll keep us under 63 for now...

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I was just thinking of that... if you have needs to dozens of covering indexes, 
then the index maintenance anytime you modify the table must be enormous. It 
makes me think you might be better off using triggers to maintain separate 
tables with covered data instead of indexes.  The only downside to that, I 
suppose, is that you have to pick the right table when doing the select.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 3:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt <dav...@mylollc.com> wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt <dav...@mylollc.com> wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt <dav...@mylollc.com> wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Max of 63 columns for a covering index to work?

[David de Regt]

I'm experimenting with covering indices on one of our larger tables.  I started 
seeing really inconsistent behavior, and made the following sample setup code 
that demonstrates it:

DROP TABLE IF EXISTS test;

CREATE TABLE test(
col01 integer,col02 integer,col03 integer,col04 integer,col05 integer,col06 
integer,col07 integer,col08 integer,col09 integer,col10 integer,
col11 integer,col12 integer,col13 integer,col14 integer,col15 integer,col16 
integer,col17 integer,col18 integer,col19 integer,col20 integer,
col21 integer,col22 integer,col23 integer,col24 integer,col25 integer,col26 
integer,col27 integer,col28 integer,col29 integer,col30 integer,
col31 integer,col32 integer,col33 integer,col34 integer,col35 integer,col36 
integer,col37 integer,col38 integer,col39 integer,col40 integer,
col41 integer,col42 integer,col43 integer,col44 integer,col45 integer,col46 
integer,col47 integer,col48 integer,col49 integer,col50 integer,
col51 integer,col52 integer,col53 integer,col54 integer,col55 integer,col56 
integer,col57 integer,col58 integer,col59 integer,col60 integer,
col61 integer,col62 integer,col63 integer,col64 integer,col65 integer,col66 
integer,col67 integer,col68 integer,col69 integer,col70 integer
);

CREATE INDEX test1 ON test(col01,col02,col03);
CREATE INDEX test2 ON test(col01,col02,col63);
CREATE INDEX test3 ON test(col62,col63,col64);
CREATE INDEX test4 ON test(col64,col65,col66);
CREATE INDEX test5 ON test(col66,col67,col10);

--With that initial setup, here's a pile of sample EXPLAIN QUERY PLANs and 
their result, as to whether the covering index works:

EXPLAIN QUERY PLAN
SELECT SUM(col03) FROM test WHERE col01=0 AND col02=1;
--test1 COVERING

EXPLAIN QUERY PLAN
SELECT SUM(col63) FROM test WHERE col01=0 AND col02=1;
--test2 COVERING

EXPLAIN QUERY PLAN
SELECT col02,col63 FROM test WHERE col01=0;
--test2 COVERING

EXPLAIN QUERY PLAN
SELECT SUM(col64) FROM test WHERE col62=0 AND col63=1;
--test3 NONCOVERING

EXPLAIN QUERY PLAN
SELECT col63 FROM test WHERE col62=0;
--test3 COVERING

EXPLAIN QUERY PLAN
SELECT col63,col64 FROM test WHERE col62=0;
--test3 NONCOVERING

EXPLAIN QUERY PLAN
SELECT SUM(col66) FROM test WHERE col64=0 AND col65=1;
--test4 NONCOVERING

EXPLAIN QUERY PLAN
SELECT SUM(col10) FROM test WHERE col66=0 AND col67=1;
--test5 NONCOVERING

EXPLAIN QUERY PLAN
SELECT col67,col10 FROM test WHERE col66=0;
--test5 NONCOVERING

Help? :)

We'd really rather keep our table denormed, as we really do pull and use all 
>64 columns of data with every query, but if this is just a limitation, then 
we'll have to figure out a solution.  It's not listed anywhere on the limits 
page, though, so I'm wondering if this needs to be added, or if I'm just doing 
something stupid or something. :)

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

For the partition in question, the starting offset is 156,860,678,144 which 
divides evenly into 128k (131072).  So, doesn't look like the issue.  Also 
divides nicely into several further powers of 2 if the block size were smaller.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, November 30, 2012 2:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 13:31, David de Regt wrote:
> Only possible agent is MSE,

MSE is the best behaved.  Norton and similar are especially bad.

> ... and process monitor doesn't show it eating IO

Sadly that rules out easy fixes :-)

> I tried changing block size to the native block size and it only sped
> up by ~5%.

Although that helps, I was talking about alignment.  This can happen at a
minor level - eg a small block size could be 4kb but the partition starts
at 63kb.  That means each filesystem block maps onto parts of two
different SSD/HDD blocks.  For SSDs there are also major block sizes
(erase block) which typically tended to be 128kb.  Again a misalignment
could cause a lot of extra work to be done.

Depending on how Windows got partitioned - the older the partitioning the
more likely this is to happen.  It won't shouldn't using a current Windows
7/8 on a fresh machine today.

Run msinfo32 and then Components > Storage > Disks to find the relevant
partition and its starting offset/alignment.

(This is unlikely to be your problem, but if present does result in the
kind of performance degradation you are seeing.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5LIYACgkQmOOfHg372QQTsgCg1kUbwbwBnJOcenMHnFULGZe5
PqcAn30XBAT6extxig8Md7MI6XEtoHbi
=xYNE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Only possible agent is MSE, and process monitor doesn't show it eating IO, 
likely since it's not a watched extension.  I saw the article about the 
extensions a while ago, so we decided to use .s3db for our database extension.

I tried changing block size to the native block size and it only sped up by ~5%.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, November 30, 2012 1:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 30/11/12 09:41, David de Regt wrote:
> Is there something ridiculous about the windows file system performance
> that hoses sqlite's open/read/write/close transaction cycle?

There are multiple possible confounding factors.  One is that you could
have tag alongs running - virus scanners, backup agents, content indexers
etc.  I strongly recommend running Sysinternal's Process Monitor which
will show file activity and point fingers.

Another is that Windows monitors files with certain extensions as part of
system restore.  If you happened to pick one of the extensions for your
database you'll find it a lot slower:

  http://msdn.microsoft.com/en-us/library/aa378870.aspx

There are some other tradeoffs in the NTFS implementation, such as how all
directory information is stored in a single large "file" (MFT).

It is also possible that the blocks of the filesystem don't align with the
blocks of the SSD which will cause the SSD performance to be a lot slower.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlC5IaoACgkQmOOfHg372QQjRgCdG4HCUkm9K/fRqIESJDfusiKG
WGQAni80PNqPHynWWYZxil1QRZmUEdZE
=nGIY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Windows 7/64 SP1.  Latest OSX (10.8).

The performance differential is definitely IO-related.  When I switch to 
wrapping everything in a transaction, the differential drops to ~1.5x (windows 
= 110ms, ios = 70ms, on a giant set of inserts).  So, it's something to do with 
the IO subsystem.  Michael Black has duplicated the speed issue with a simple 
test app he wrote doing my same basic inserts.

I have, however, discovered WAL mode today, and that gave about an 8x speed 
improvement (2500ms before, ~330ms after).  I'm still reading up on WAL to see 
if it's fully safe through atomic transactions for power loss, but it appears 
to be.  The funny thing, though, is that iOS sped up by almost the same order 
of magnitude under WAL mode (280ms -> ~40ms, but was running into timer 
resolution issues that low).  So, while this brings SQLite back into the realm 
of acceptable performance under Windows for our usage (assuming I can use WAL), 
which is great news for me, it's still very strange that it's 8x slower than 
Apple-based...

TRUNCATE mode alone gave an almost 50% improvement on windows too, hilariously 
enough (2500ms->1300ms), and almost zero benefit on iOS (280ms->260ms).

-David

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Friday, November 30, 2012 10:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

On 30 Nov 2012, at 5:41pm, David de Regt <dav...@mylollc.com> wrote:

> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I read with interest the figures you produced so far, though I have no 
explanation.  Can I ask which versions of Windows and OS X you're running ?

The most recent version of OS X (10.8 == Mountain Lion) is extremely efficient 
when addressing SSDs.  Not only does it implement TRIM but two levels of 
storage drivers have been rewritten to remove optimization assumptions which 
used to be valid with spinning drives but actually slow things down with SSDs.  
Although this mostly involves just removing extra code which no longer helps, 
this has made 10.8 extremely fast with SSDs which the OS correctly identifies 
as SSDs.

However, some work has gone into doing the same thing with Windows 7 and 
Windows 8.  But I know far less about low-level behaviour of Windows and don't 
know if the same things have been done.

iOS speed on a recent iDevice (iPad 2+, iPhone 4+) should be within a close 
order of magnitude to Mac speeds, which is what you're finding.  I see nothing 
unexpected in your iOS figures.

The figures you supplied are ... well, your word 'ridiculous' is as good as 
any.  Windows shouldn't be a tenth the speed of OS X.  No matter how much I 
despise Microsoft it's really not that bad.  Someone would have spotted 
something.  I'm wondering whether Windows is correctly enforcing 
in-order-writing whereas the other OSen aren't.  I predict that Linux times 
would be closer to OS X times than Windows times.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Nope, I ran the tests both in Parallels and rebooting directly into boot camp 
(basically native windows), and had essentially identical performance (+/- 2%, 
within noise level differences).  It also echoes the performance difference I'd 
been seeing on the database side just watching the real app run on iOS and on 
my other non-Apple native windows box.  Interesting little find, nonetheless, 
thanks for that. :)

To Alex: Unfortunately, Windows is a core platform for us.  We can't really 
just tell them to buzz off, so it's either figure out how to improve SQLite 
performance or switch DB engines, at least on that platform...

-David

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, November 30, 2012 9:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Could this be your problem?
http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of David de Regt [dav...@mylollc.com]
Sent: Friday, November 30, 2012 11:41 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[David de Regt]

Hey all.  I've been struggling with a basic perf issue running the same code on 
Windows vs. iOS and OSX.

Basic query set:
CREATE TABLE test (col1 int, col2 text);
[loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')

I'm coding this using the default C amalgamation release and using prepare/etc. 
on all platforms in the exact same way (same very simple DB-access class I 
made).  I realize that using a transaction around this would vastly improve 
perf, but given the atomic nature of the app that this test is simulating, it 
won't work to wrap it into transactions, so my goal is to improve the atomic 
performance.  These are all being run on the same Macbook Pro, with an SSD, 
running Windows via boot camp, OSX natively, and iOS via the iOS simulator:

With defaults (pragma sync = on, default journal_mode):
Windows: 2500ms
iOS: 300ms
OSX: 280ms

With pragma sync = off, journal_mode = memory:
Windows: 62ms
iOS: 25ms
OSX: 25ms

Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
scenario, so with sync on, it seems like something must be fishy for it to be 
~8-9x slower than the other platforms.  Is there something ridiculous about the 
windows file system performance that hoses sqlite's open/read/write/close 
transaction cycle?  Is there anything I can do, or just accept it and move on?  
With how that scales up, we may need to move to something like using embedded 
MySQL or LocalDB on Windows to get the same performance as we see with SQLite 
on other platforms, which seems quite ridiculous.

Thanks!
-David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users