Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] sqlite with Java

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

Re: [sqlite] Visual Studio 2015 Express C# LoadExtension() not working?

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

Re: [sqlite] Visual Studio 2015 Express C# LoadExtension() not working?

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

Re: [sqlite] Visual Studio 2015 Express C# LoadExtension() not working?

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()

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

[sqlite] Visual Studio 2015 Express C# LoadExtension() not working?

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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:

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] sqlite with Java

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

[sqlite] sqlite with Java

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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 ?

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] reset after failed step

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] reset after failed step

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 =

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

>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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Compressed schema in memory?

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

[sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

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

Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS pager?

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:

Re: [sqlite] Compressed schema in memory?

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

Re: [sqlite] Compressed schema in memory?

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

Re: [sqlite] Compressed schema in memory?

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

Re: [sqlite] Compressed schema in memory?

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

Re: [sqlite] Compressed schema in memory?

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