Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

Hello Bart, > Le 28 mai 2017 à 13:03, Bart Smissaert a écrit : > > Using SQLite3 3.19.0 on a Windows machine. > I have some general questions about sqlite3_reset and > sqlite3_clear_bindings: > I am processing data from a 2D variant array (this is VB6). > > 1. I

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

> Calling sqlite3_clear_bindings does the same thing as calling sqlite3_bind_null for all arguments. Yes, I understand that, just thinking about efficiency. > I personnally call sqlite3_reset before sqlite3_clear_bingings with great success I am doing the same now. Probably no difference there

Re: [sqlite] FTS5 performance problem.

John Found wrote: > the execution time increases by a factor of 100 > > select count() from PostFTS where PostFTS match 'innermost' and ThreadID = 6; This is the same as: select count() from PostFTS where PostFTS match 'innermost' and PostFTS match 'ThreadID:6'; It might be better to use a

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp: > On 5/27/17, Thomas Flemming wrote: >> Hi, >> >> I have a table Pois with points of interest (geogr. coordinate, label, >> styleid) where I do regional querys using a rtree-index: >> >> SELECT Pois.* FROM Pois_bb, Pois

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

> Le 28 mai 2017 à 13:24, Bart Smissaert a écrit : > >> Calling sqlite3_clear_bindings does the same thing as calling > sqlite3_bind_null for all arguments. > > Yes, I understand that, just thinking about efficiency. Then I don't know. Your experience will tell. >>

[sqlite] FTS5 performance problem.

I have a FTS5 virtual external content table, defined following way: CREATE VIRTUAL TABLE PostFTS using fts5( ThreadID, Content, content=Posts, content_rowid=id, tokenize='porter unicode61 remove_diacritics 1'); The following query executes for 4..5ms: select count() from PostFTS where PostFTS

Re: [sqlite] FTS5 performance problem.

On Sun, 28 May 2017 14:23:47 +0200 Clemens Ladisch wrote: > It might be better to use a single FTS lookup for both words: > > select count() from PostFTS where PostFTS match 'innermost ThreadID:6'; > Thanks! This is indeed much faster. But I am passing the search query

Re: [sqlite] PRAGMA cache_size=0 increases memory usage

Hi Bob, Den 18 maj 2017 9:15 em skrev "Bob Friesenhahn" < bfrie...@simple.dallas.tx.us>: On Thu, 18 May 2017, Kim Gräsman wrote: > >> The request is issued early on when the connection is first opened so no >> actual queries have been issued at that time. >> > > Then my (black-box) guess is

[sqlite] [Bug] Non-ASCII character is not counted in calculating column width

Using .width x along with .mode columns, any non-ASCII character isn't counted, causing the column to shrink by one. I *think* my analysis is correct, but it also might be counted multiple times by taking a naïve approach and just counting the number of bytes (UTF-8 has multi-byte characters).

[sqlite] No check for invalid constraints

Hi there, I'm using SQLITE version 3.16.0 on macOS Sierra 10.12.4. I'm experiencing a unique bug where if I put in an primary key constraint with underscores then the primary key does not auto increment when I insert values. *For example:* CREATE TABLE sensor_status(status_id INTEGER

[sqlite] sqlite3_reset and sqlite3_clear_bindings

Using SQLite3 3.19.0 on a Windows machine. I have some general questions about sqlite3_reset and sqlite3_clear_bindings: I am processing data from a 2D variant array (this is VB6). 1. I understand that after processing a row (binding all the values in a row of that variant array) I need to do

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

The documentation (https://www.sqlite.org/c3ref/reset.html) seems to suggest that sqlite3_reset can only be unsuccessful if sqlite3_step returned an error. Not sure about sqlite3_clear_bindings. I will do some testing and see if there is any performance gain in leaving these checks out. Very

Re: [sqlite] No check for invalid constraints

On 5/27/2017 9:47 AM, Aflah Bhari wrote: I'm using SQLITE version 3.16.0 on macOS Sierra 10.12.4. I'm experiencing a unique bug where if I put in an primary key constraint with underscores then the primary key does not auto increment when I insert values. *For example:* CREATE TABLE

Re: [sqlite] FTS5 performance problem.

John Found wrote: > Pseudocode: ?1 = keyword + ' ThreadID:' + num2str(threadID); > > select count() from PostFTS where PostFTS match ?1; That works. > ?1 = keyword > ?2 = threadID > select count() from PostFTS where PostFTS match ?1 ThreadID:?2; That is not valid SQL. You have to create a

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

On 28 May 2017, at 12:14pm, Gwendal Roué wrote: > I personnally call sqlite3_reset before sqlite3_clear_bingings with great > success, but I don't know if the order is relevant or not. It makes more sense to move sqlite3_clear_bindings() to before you set parameters

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

> I can’t think of any cases where these would happen if everything was working as planned. I can't think of anything either and this is all fully tested, so I think I can leave these checks out. > From what I can see, you’re executing a long series of queries, each of which you expect to return

[sqlite] CLI option to NOT autocheckpoint WAL databases

I've got WAL databases that I'm trying to do analysis on and DON'T want them modified. I can PRAGMA wal_autocheckpoint=0; to disable auto-checkpoint'ing but when I exit the shell I see auto-checkpoint'ing is done -- foo.db-wal and foo.db-shm are gone and foo.db is modified. That's bad. I can't

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

On 29 May 2017, at 5:33am, Howard Kapustein wrote: > Is it possible to use the CLI to read a WAL database and exit without > modifying the database? I don’t know about the database file itself. I think that one is only read unless you explicitly do writing.