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

[Eduardo Morras]

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 need to scare the guy by mentioning twenty complications of
> SQLite which may be irrelevant.

... I read "Speed issue..." in Subject, but you're rigth,
60ms is fast enough on common deployment configuration.

> If Nick needs to save more time he?ll need to post more details of
> what he?s doing.
> 
> Simon.

P.S. I sent the mail from my current contractor mail, sorry for that.

---   ---
Eduardo Morras 
___
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

[Jens Alfke]

> 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 SQLite. (Although some profilers rely on instrumenting the code, so you 
have to compile with some special compiler flag, which means you’d need to 
compile sqlite3.c as part of your program.)

> So, what is the tool you mentioned such as ‘sample’ tool?

‘sample’ is Mac-specific. I don’t know what kind of CPU profiling tools exist 
for Android; that’s off-topic here but I’m sure there’s a lot of Android info 
out there…

—Jens

___
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

[Bart Smissaert]

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 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 situation should this increase read speed?
>>
>
> Apologies, should have been more clear - this will increase the speed
> between queries FOR consecutive queries, not so much inside any single
> query.
>
> To see why is easy, the loop amounts to either:
>
> // Slower loop:
> for each q in queries do {
>   Acquire read lock;
>   Prepare;
>   Loop query results;
>   Release readlock;
> }
>
> - OR -
>
> // Faster loop:
> Start Transaction;
> Acquire read lock;
> for each q in queries do {
>   Prepare;
>   Loop query results;
> }
> Release readlock;
> End Transaction;
>
> (This is very simplified and not technically 100% accurate how SQLite does
> it, but you get the idea).
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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

[R Smith]

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) sqlite3_step, sqlite3_column_xxx etc.
In what situation should this increase read speed?


Apologies, should have been more clear - this will increase the speed 
between queries FOR consecutive queries, not so much inside any single 
query.


To see why is easy, the loop amounts to either:

// Slower loop:
for each q in queries do {
  Acquire read lock;
  Prepare;
  Loop query results;
  Release readlock;
}

- OR -

// Faster loop:
Start Transaction;
Acquire read lock;
for each q in queries do {
  Prepare;
  Loop query results;
}
Release readlock;
End Transaction;

(This is very simplified and not technically 100% accurate how SQLite 
does it, but you get the idea).




___
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

[Nick]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Nick]

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
Android.

So, what is the tool you mentioned such as ‘sample’ tool?
And do you mean CPU profiler (gperftools og Google) is useful to profile
sqlite? As I am not familiar about this tool.

Thanks.  



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

[Simon Slavin]

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 irrelevant.

If Nick needs to save more time he’ll need to post more details of what he’s 
doing.

Simon.
___
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

[Jens Alfke]

> 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 all 
of the text columns. And with the auto-index on ‘b’, there’s no table scan 
going on.

Nick — you haven’t said what API you’re using to access SQLite. If it’s not the 
raw C API, there may be overhead involved in that layer, especially dealing 
with large strings. What I would do is run a CPU profiler during the query and 
see if there are any hot-spots you can do something about.

(In my opinion, profiling is greatly underused. I keep coming across people 
complaining about performance or asking how to speed something up, and usually 
they haven’t even considered profiling. Maybe I’m spoiled because in the 
Mac/iOS environment both the Instruments app and the ‘sample’ tool are easy to 
use, but even if the profiler is tricky to learn, it’s a worthwhile investment.)

—Jens
___
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

[Dominique Devienne]

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
___
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

[David Raymond]

sqlite_autoindex_t1_1 is the index created by the unique constraint in the 
schema, it's not a temporary index.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dominique Devienne
Sent: Wednesday, January 17, 2018 9:08 AM
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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Bart Smissaert]

> 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 situation should this increase read speed?

RBS




On Wed, Jan 17, 2018 at 8:48 AM, R Smith  wrote:

> 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?
>>
>> Um, I guess I have nothing to do to improve the performance.
>> Thanks.
>>
>
> 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 suspects that
> comes to mind are:
>
> 1. Use a faster Journal mode (Have to weigh speed against
> power-loss-data-integrity-protection),
> 2. Use an in-memory DB (especially if you only reading from it)
> 3. Start a transaction and hold the DB read locks for the duration of your
> application (again, if it won't need writing)
> 4. Ensure there are no other time-consuming bits of code in the sqlite api
> RESET()-STEP() loop. (And if you are accessing it through a wrapper, stop
> that and use the api directly).
>
> We could and would probably give more useful direct advice if we know a
> lot more about your specific use case, platforms, access-method and setup.
>
> Cheers!
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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

[Dominique Devienne]

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 advise from the community is to denormalize your schema and put
those large columns in a separate table. That forces you to join the tables
when
you want to access columns from both tables, and to manually manage the
lifetime
of the separate rows which are logical a single row, but then 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.

Rows belong to pages, and when a row no longer fits into a page, "overflow"
pages are
used, in a forward-linked-list manner if you want, so a scan must still
read all pages to
find the next row on the next row-page that follows all those overflow
pages (this is just
a high level description from someone that does *not* know to exact
details...). From
this it's obvious smaller rows can avoid a log of IO and speed things up.
In not using a
separate table, at least put "big" columns at the end, again to avoid have
to skip other
them to read a small column at the end. --DD

PS: There's a special mode related to AUTOVACUM where SQLite adds special
pages that
keep track of other pages that sometimes allow it to "jump over" pages, but
that's niche
and I'm not even sure it applies here.

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.
___
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

[Nick]

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 is more important is that, I think it is a better way to establish my
tables according to however the source data is. 



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

[Nick]

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. Start a transaction and hold the DB read locks for the duration of 
your application (again, if it won't need writing)
I only have several single SELECT at boot time and I guess it is not
necessary to use TRANSACTION? And there are still some write ops in my
service.

>4. Ensure there are no other time-consuming bits of code in the sqlite 
api RESET()-STEP() loop.
I think I only use sqlite api in some normal ways. 

And I do not know more details about what happens during the boot time.




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

[Eduardo]

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 
> suspects that comes to mind are:
> 
> 1. Use a faster Journal mode (Have to weigh speed against 
> power-loss-data-integrity-protection),
> 2. Use an in-memory DB (especially if you only reading from it)
> 3. Start a transaction and hold the DB read locks for the duration of 
> your application (again, if it won't need writing)
> 4. Ensure there are no other time-consuming bits of code in the sqlite 
> api RESET()-STEP() loop. (And if you are accessing it through a wrapper, 
> stop that and use the api directly).
> 
> We could and would probably give more useful direct advice if we know a 
> lot more about your specific use case, platforms, access-method and setup.

What about db normalization?

Change:

CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTOINCREMENT, 
b INTEGER NOT NULL UNIQUE, 
c INTEGER NOT NULL, 
d INTEGER, 
e TEXT, 
f INTEGER, 
g INTEGER, 
h TEXT, 
i INTEGER
);

To: 

CREATE TABLE t1 (
a INTEGER PRIMARY KEY AUTOINCREMENT, 
b INTEGER NOT NULL UNIQUE, 
c INTEGER NOT NULL, 
d INTEGER,  
f INTEGER, 
g INTEGER, 
i INTEGER,
e INTEGER,
h INTEGER,
FOREIGN KEY (e) REFERENCES t2(j),
FOREIGN KEY (h) REFERENCES t2(j)
);

CREATE TABLE t2 (
j INTEGER PRIMARY KEY
k TEXT
);

You don't need to have in t2 e and h columns, only k one. Table t1 will have
fixed row size (not exactly true) and lot less table size; t2 will store each
text only once, if some e and some h originally had the same content.

You can set pragma foreign_keys = ON to force sqlite check the constraint, or
disable it with pragma foreign_keys = OFF if you trust your data input; e, h
have always values in t2.j. 

HTH

-- 
Eduardo 
___
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

[R Smith]

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?

Um, I guess I have nothing to do to improve the performance.
Thanks.


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 
suspects that comes to mind are:


1. Use a faster Journal mode (Have to weigh speed against 
power-loss-data-integrity-protection),

2. Use an in-memory DB (especially if you only reading from it)
3. Start a transaction and hold the DB read locks for the duration of 
your application (again, if it won't need writing)
4. Ensure there are no other time-consuming bits of code in the sqlite 
api RESET()-STEP() loop. (And if you are accessing it through a wrapper, 
stop that and use the api directly).


We could and would probably give more useful direct advice if we know a 
lot more about your specific use case, platforms, access-method and setup.


Cheers!
Ryan

___
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

[Nick]

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

[Simon Slavin]

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 effect on 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 improve the performance. 

I think you got it right, and I don’t see any obvious improvement.

Simon.
___
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

[Nick]

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 improve the performance. 
Thanks.



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

[Simon Slavin]

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:
>   SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 1;
> 
> It needs almost 60ms as there are about 100 records with some long TEXT data
> in the TEXT columns.  
> 
> I am wondering if it is needed to add ANY INDEX to improve the performance
> of the SELECT?

60ms is a reasonable figure here.  600ms might make be wonder what’s going on.

In order to enforce your "b UNIQUE" constraint, SQLite should have made up its 
own index, and it should be using that index when executing your SELECT.  
Here’s how to make sure.  Do the following in the SQLite command-line tool.

1) If possible, put some plausible data in the table.  If this is not 
appropriate, that’s okay, just leave the table empty.

2) Execute "ANALYZE;".  The results will be saved with the database file.  
There’s no need to do "ANALYZE;" again unless you add or delete tables or 
indexes.

3) Add "EXPLAIN QUERY PLAN " to the beginning of your "SELECT …" command and 
see what SQLite shows.

EXPLAIN QUERY PLAN SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 1;

SQLite should output something which includes either "search" or "scan".  If it 
includes "search" then it’s using an index to access the right records, so 
it’ll be fast.  If it includes "scan" then it’s reading an entire table or 
index, so it’ll be slow.

In your case, you did it right.  You will see that the index it chose includes 
just the values of b, so long TEXT values aren’t a problem here.

Simon.
___
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

[petern]

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 printf('%100s');
SELECT * from t1;
--...
Run Time: real 0.884 user 0.008000 sys 0.008000



On Tue, Jan 16, 2018 at 7:52 PM, Nick  wrote:

> I have a table below in my application:
>
> CREATE TABLE t1 (
> a INTEGER PRIMARY KEY AUTOINCREMENT,
> b INTEGER NOT NULL UNIQUE,
> c INTEGER NOT NULL,
> d INTEGER,
> e TEXT,
> f INTEGER,
> g INTEGER,
> h TEXT,
> i INTEGER,
> UNIQUE(b, i)
> );
> And I’ve got some speed issues when I query the db:
> SELECT b, c, d, e, f, g, h FROM t1 WHERE b >= 1;
>
> It needs almost 60ms as there are about 100 records with some long TEXT
> data
> in the TEXT columns.
>
> I am wondering if it is needed to add ANY INDEX to improve the performance
> of the SELECT?
>
> Thanks.
>
>
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users