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
g 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
Subjec
sqlite] Windows (slow) vs. iOS/OSX (fast) Performance
On 30 Nov 2012, at 5:41pm, David de Regt 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 produ
/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
1
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 an
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
PM, David de Regt 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
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?
H
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,
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
ex, 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,
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 month
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: [sq
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 debugge
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 wrote:
> Is there any chance that the OS is still flushing the WAL ch
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 o
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, Septembe
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
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
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 r
, David de Regt 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!)'
>
&g
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 co
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
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
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 e
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
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
27 matches
Mail list logo