Re: [sqlite] Speed issue of SELECT in my application

On Wed, 17 Jan 2018 17:59:22 + Simon Slavin wrote: > Folks. Nick published a figure of 60ms for his search. That?s not > unusually slow. There was no request to shave every last millisecond > off that figure. There wasn?t even a statement that it was too > slow. No

Re: [sqlite] Speed issue of SELECT in my application

> On Jan 17, 2018, at 6:16 PM, Nick wrote: > > Jens, I totally agree with your opinion of profile. I have tried to find some > useful tools to profile applications using sqlite A CPU profiler works on arbitrary code, so it shouldn’t need to know anything about

Re: [sqlite] Speed issue of SELECT in my application

No worries, I had figured you meant this applied to multiple read statements. RBS On Thu, Jan 18, 2018 at 9:24 AM, R Smith wrote: > > On 2018/01/17 4:26 PM, Bart Smissaert wrote: > >> 3. Start a transaction and hold the DB read locks for the duration of >>> >> your

Re: [sqlite] Speed issue of SELECT in my application

On 2018/01/17 4:26 PM, Bart Smissaert wrote: 3. Start a transaction and hold the DB read locks for the duration of your application (again, if it won't need writing) I had a look at this but couldn't see a speed increase. This was for a single statement, so that is repeated (in a loop)

Re: [sqlite] Speed issue of SELECT in my application

Thank you all. As Simon said, 60ms may be a reasonable figure and I am trying to focus on the detail of my service according to all your suggestion. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] Speed issue of SELECT in my application

Jens, I totally agree with your opinion of profile. I have tried to find some useful tools to profile applications using sqlite and all I found is sqlite3_profile()(gettimeofday) and vdbe_profile(hardware time) inside sqlite. I also know a little about Time Profile of Instruments but I am using

Re: [sqlite] Speed issue of SELECT in my application

Folks. Nick published a figure of 60ms for his search. That’s not unusually slow. There was no request to shave every last millisecond off that figure. There wasn’t even a statement that it was too slow. No need to scare the guy by mentioning twenty complications of SQLite which may be

Re: [sqlite] Speed issue of SELECT in my application

> On Jan 17, 2018, at 6:08 AM, Dominique Devienne wrote: > > you gain much faster table scans, because you no longer have to read those > large columns when going from row to row during a scan. I don’t see how this helps Nick's specific query, though, since it fetches

Re: [sqlite] Speed issue of SELECT in my application

On Wed, Jan 17, 2018 at 3:44 PM, David Raymond wrote: > sqlite_autoindex_t1_1 is the index created by the unique constraint in the > schema, it's not a temporary index. Thanks. That name fouled me indeed. --DD ___

Re: [sqlite] Speed issue of SELECT in my application

To: SQLite mailing list Subject: Re: [sqlite] Speed issue of SELECT in my application PPS: Still, the fact your query plan uses an automatically-generated (transient) index means none of the persistent indices is suspect, and your overlapping UNIQUE indices is also strange, FWIW

Re: [sqlite] Speed issue of SELECT in my application

> 3. Start a transaction and hold the DB read locks for the duration of your application (again, if it won't need writing) I had a look at this but couldn't see a speed increase. This was for a single statement, so that is repeated (in a loop) sqlite3_step, sqlite3_column_xxx etc. In what

Re: [sqlite] Speed issue of SELECT in my application

On Wed, Jan 17, 2018 at 1:06 PM, Nick wrote: > What is more important is that, I think it is a better way to establish my > tables according to however the source data is. Because SQLite stores all cells in-row, including large text and blob columns, a common

Re: [sqlite] Speed issue of SELECT in my application

I am confused about your table t2. It will be faster to query the table t1, but I need the content of column e and h when I query the data which means I need a extra SELECT from the table t2, is it right? At the same time, I guess it is more complicate to INSERT data into both t1 and t2. What

Re: [sqlite] Speed issue of SELECT in my application

Thank you Smith. The table is preseted with some data in my Android system. And I guess my APPLICATION is more like a SERVICE which will be started at system boot time and speed issue happens at the time. According to the some other reasons, I have to use wal+normal journal and sync mode. >3.

Re: [sqlite] Speed issue of SELECT in my application

On Wed, 17 Jan 2018 10:48:10 +0200 R Smith escribió: > Hang on a second - True, there is nothing to be done to improve the > speed of that specific Query - BUT - there is plenty to be done to > improve the speed of the database for your specific use case. The usual >

Re: [sqlite] Speed issue of SELECT in my application

On 2018/01/17 8:48 AM, Nick wrote: Thank you Simon. As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the performance of the SELECT. I find "SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b>?)" when I use EXPLAIN QUERY PLAN, so I do not need to add any index, right?

Re: [sqlite] Speed issue of SELECT in my application

OK. Thank you for your help. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed issue of SELECT in my application

On 17 Jan 2018, at 6:48am, Nick wrote: > As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the > performance of the SELECT. Correct. It’ll make the database file bigger, and it’ll slightly slow down INSERT/UPDATE/DELETE but have only a trivial

Re: [sqlite] Speed issue of SELECT in my application

Thank you Simon. As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the performance of the SELECT. I find "SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b>?)" when I use EXPLAIN QUERY PLAN, so I do not need to add any index, right? Um, I guess I have nothing to do to

Re: [sqlite] Speed issue of SELECT in my application

On 17 Jan 2018, at 3:52am, Nick wrote: > b INTEGER NOT NULL UNIQUE, […] > UNIQUE(b, i) The second constraint is redundant. If values of b are unique, then so is anything that includes values of b. > And I’ve got some speed issues when I query the db:

Re: [sqlite] Speed issue of SELECT in my application

Vague. Some thoughts: How long is the text? A million? A billion? If a million, does SQLite take what you consider a long time to receive/display results from a TEXT row? SELECT printf('%100s'); --... Run Time: real 0.854 user 0.016000 sys 0.008000 --vs: INSERT INTO t1(e) SELECT