On Fri, Mar 17, 2017 at 12:00 AM, Richard Hipp wrote:
> On 3/16/17, Bob Friesenhahn wrote:
> > In sqlite_master I see quite a lot of "sql_autoindex" indexes. Do
> > these auto indexes consume the same RAM as explicit indexes?
>
> Yes. Those indexes are implementing UNIQUE constraints.
>
Richar
On 17 Mar 2017, at 7:49am, Dominique Devienne wrote:
> Richard, why is SQLite ignoring an attempt to giving these an explicit
> name? —DD
>
> […]
>
> sqlite> create table t (id constraint u1 unique);
You are supplying a name for the constraint. But you’re still leaving it up to
SQLite to cr
On Thu, Mar 16, 2017 at 11:19 PM, Bob Friesenhahn
wrote:
> On Thu, 16 Mar 2017, Richard Hipp wrote:
>>
>>
>> Your 664K is a conservative estimate. On my (64-bit linux) desktop,
>> I'm showing 1.58MB of heap space used to store the schema. (Hint:
>> bring up the database in the command-line shell
On Fri, Mar 17, 2017 at 9:30 AM, Simon Slavin wrote:
> On 17 Mar 2017, at 7:49am, Dominique Devienne wrote:
> > Richard, why is SQLite ignoring an attempt to giving these an explicit name?
> —DD
> > […]
> >> sqlite> create table t (id constraint u1 unique);
>
> You are supplying a name for the c
On 3/17/17, Dominique Devienne wrote:
>
> But what prevents SQLite from using the constraint's name, if one is
> specified, from using it for the index?
Backwards compatibility. This would change the file format, rendering
database files that are corrupt in the eyes of older versions of
SQLite.
Thank you very much. You're super quick guys :)
Cheers,
Tomasz M
From: sqlite-users on behalf of
Dan Kennedy
Sent: Thursday, March 16, 2017 8:34:30 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Is it possible to change "synchronous" flag o
Hi,
We've been struggling with a complex query that we have written. One of
the elements of this complex query is a select statement that doesn't
appear to use an index when we *think* it should do. We're not going to
ask you to debug a large complex SQL query (unless you have nothing else
to
Is it time to break out an API for schema lookup? That would seem to be the
least work for the developers and would give people the chance to implement
whatever strategy they need to manage large schemas, including storing them in
the database in a structured manager, or a compressed in-memory r
On 17 Mar 2017, at 10:20am, Rob Willett wrote:
> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC);
>
> […]
>
> As part of the larger more complex query, we are executing the query
>
> ```
> select * from Disruptions where status = 2 OR status = 6;
The schema for t
Simon,
Thank you for the quick response. We'll do this. We're just waiting for
another analyze to finish. Our current DB is 45GB, the query works OK on
smaller databases, but at this size its very slow, thats why we have a
bad feeling that we're tinkering on the edges and we need a new DB
des
>On 17 Mar 2017, at 10:20am, Rob Willett wrote:
>
>> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC);
>>
>> […]
>>
>> As part of the larger more complex query, we are executing the query
>>
>> ```
>> select * from Disruptions where status = 2 OR status = 6;
>
>The sche
>On 17 Mar 2017, at 10:20am, Rob Willett wrote:
>
>> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC);
>>
>> […]
>>
>> As part of the larger more complex query, we are executing the query
>>
>> ```
>> select * from Disruptions where status = 2 OR status = 6;
>
>The sche
Gunter, Simon,
Thanks for the replies, You both seem to be drilling into the collation
sequence as a possible issue. We now have a new index and we have just
run the query again
sqlite> analyze;
sqlite> drop index Disruptions_idx4;
sqlite> CREATE INDEX "Disruptions_idx4" ON Disruptions ("stat
Nerds with chronic byte code affinity like myself would like to see the output
of "explain" (without "query plan"), i.e. the SQLite bytecode produced. I guess
the query with OR will have a subprogram called once for each status value,
whereas I expect the query with UNION ALL to have 2 copies of
Gunter,
I would never presume to describe anybody as a Nerd!
We're just going back to very first position with the 'bad' collation
index so we can do proper timings as we change things so we understand
the speed up (we hope there is a speed up)
We've written a quick script to check each vers
Hello,
Is this (conceptually) correct:
-
.. bind arguments ..
sqlrc = sqlite3_step(stmt);
if(sqlrc == SQLITE_ROW || sqlrc == SQLITE_DONE) {
sqlrc = sqlite3_reset(stmt);
assert(sqlrc == SQLITE_OK);
}
.. bind other arguments ..
sqlrc = sqlite3_step(s
On 3/17/17, Jan Danielsson wrote:
>
>I.e. reset should only be run on ROW and DONE. On step error, the
> statement's state is reset implicitly by sqlite3_step()?
No. Always run sqlite3_reset().
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users
Thanks to everybody for their help earlier today.
As promised here's the results of our various tests. Hopefully they may
be of use to somebody...
We decided to start from a known position and so recreated the original
index with the collation in it. We know this was sub optimal but its our
On 17 Mar 2017, at 5:30pm, Rob Willett wrote:
> echo "select * from Disruptions where status = 2 OR status = 6;" | sqlite3
> tfl.sqlite > /dev/null
>
> twice and each run as 12 mins. So we were twice as quick, which is nice.
Do you actually need all columns ? If not, then specifying the colu
Simon,
We're still benchmarking the various changes we've made during the day.
I'm sitting here watching a tail of a log file waiting for it to finish.
Ah the joys of the internet startup :)
Our plan of action is
1. Have baseline from which we can work from. We now have that.
2. Create the
Ad 4) not quite,but close. If the index used for a join also contains all the
other fields of that table that are referenced in the query, SQLite can use
those values to avoid reading in the corresponding table row. This saves memory
(no storage for table row consumed), CPU cycles (no going thro
On 17 Mar 2017, at 6:22pm, Rob Willett wrote:
> 4. Work through returning just the columns we actually need from our queries.
> We have a recollection that if we can build an index with all the information
> necessary in it, we can do all the work in joins rather than paging out to
> disk. Is
The data for each record is stored in the same order as the fields are defined.
When reading a record's information, SQLite starts at the first field, and
stops at the last field needed to complete the query. The situation where this
makes the most difference is where there's a reasonably sized
Dear all,
I would like to use sqlite from Java, but I am also looking to buy SSE.
however which library would you recommend? how to integrate SSE?
ps: it would be splendid if you could provide the java libs, similar to the
.net version.
Best regard,
Sylvain
__
one solution its use the odbc sqlite brigde
http://www.java2s.com/Tutorial/Java/0340__Database/ConnecttoAccessDatabaseusingJDBCODBCbridge.htm
with the sqlite odbc module
http://www.ch-werner.de/sqliteodbc/
so due i dont see here java (a general great widely use due
portability) facilities, and
Dear all,
We progress steadily forward.
1. We immediately halved our execution time by moving to an updated
index that doesn't use COLLATE. Thats 32mins to 16mins.
2. We then shaved a further six minutes off the execution time by
removing extraneous fields in the select statement, so instead
Change your query to
explain query plan select * from Disruptions where status = 2 collate nocase OR
status = 6 collate nocase;
to match your index.
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rob Willett
> Sent: Friday
On Friday, 17 March, 2017 16:13, Rob Willett
said:
> 3. We have looked through all our indexes and can see that every index
> has a COLLATE against it, even if the column is an integer. We have
> raised a support call with Navicat.
If you want a table field to be non-case-sensitive, the proper
I can't get the LoadExtension() function to work under Visual Studio 2015
Express C#. I have tried to load several extensions that do work under the
SQLite console but they will not load under C#. So I created the minimal
example of an example extension given on sqlite.org using the half.c sour
On 17 Mar 2017, at 10:12pm, Rob Willett wrote:
> 3. We have looked through all our indexes and can see that every index has a
> COLLATE against it, even if the column is an integer. We have raised a
> support call with Navicat.
This might also be something that Navicat should look into. The
I can think of one possibility offhand:
The application is running as 32-bit and the extension DLL is 64-bit (or
vice-versa).
Sent from my iPhone
https://urn.to/r/mistachkin
> On Mar 17, 2017, at 5:02 PM, Phil Pollock wrote:
>
> I can't get the LoadExtension() function to work under Visual S
I forgot to mention I tried all combinations of 32-bit, 64-bit together with
same results.
From: sqlite-users on behalf of
Joe Mistachkin
Sent: Friday, March 17, 2017 8:09 PM
To: SQLite mailing list
Subject: Re: [sqlite] Visual Studio 2015 Express C# LoadExten
One of my cl library paths was mixing 32-bit, 64-bit versions during the build.
Thanks for making me double check.
From: sqlite-users on behalf of
Phil Pollock
Sent: Friday, March 17, 2017 9:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Visual Studio 201
I don't really want to use odbc in Java. I was thinking of
https://github.com/xerial/sqlite-jdbc
but how to use SSE in this case? seems the build is not so easy.
with the sqlite odbc module
>
> http://www.ch-werner.de/sqliteodbc
>
___
sqlite-users mailin
We've just implemented a covering index for the last step (is it
really?) in our quest to get the query execution time down.
To summarise we have gone from 32 mins to 16 mins by updating an index
so it doesn't use collate, we took another six minutes off by removing
extra fields in the select
35 matches
Mail list logo