SELECT query #first run# is very slow VOL2

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

SELECT query #first run# is very slow VOL2

zakari
hi all,

I have exactly the same problem with topic :
http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html

--

The dbase sitting on linux server, Im accessing the dbase with PDO object.

-connected
-prepare the statement
-execute
here makes =>1min lag, *only the first time*
-executed

in detail this^ running on a php file, which is the pagination.php...

If I bring the dbase locally on windows machine, *there is no lag*...

I have *no indexes*, the dbase structure is very simple.

CREATE TABLE "feeds" ( "feed_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"feed_provider_id" INTEGER, "feed_title" TEXT, "feed_url" TEXT, "feed_date"
TEXT, "feed_timestamp" TEXT )

CREATE TABLE "provider_logs" ( "provider_log_id" INTEGER PRIMARY KEY
AUTOINCREMENT NOT NULL, "provider_id" INTEGER, "feed_type" INTEGER,
"feed_most_recent_feed_date" TEXT, "feed_count" INTEGER, "time2parse" REAL,
"date_rec" TEXT )

CREATE TABLE "providers" ( "provider_id" INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL, "provider_url" TEXT, "provider_enabled" INTEGER,
"provider_once_per_day" INTEGER, "provider_last_run" TEXT,
"provider_headline" TEXT )

feeds rows = 260k
providers= 43
provider_logs = 0

ofc I tried VACUUM, there is no difference.

pasting some logs, Im declaring again this happening only the first time,
afterwards working without problem.
2017-12-17 15:16:23 - execute
2017-12-17 15:17:20 - executed

2017-12-19 14:53:35 - execute
2017-12-19 14:54:32 - executed

2017-12-19 20:14:23 - execute
2017-12-19 20:15:20 - executed

--

the options through *SQLiteDatabaseBrowser* are like that :
<http://sqlite.1065341.n5.nabble.com/file/t8475/vDM6Kz.png>

--

the flow is :
1) prepare the statement "select feed_title, feed_url, feed_date from feeds
where feed_title like :searchTerm or feed_url like :searchTerm  LIMIT :limit
OFFSET :offset"
2) bindValues
2b) log (ex 2017-12-17 15:16:23 - execute)
3) execute
3b) log (2017-12-17 15:17:20 - executed)


any suggestion ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT query #first run# is very slow VOL2

Simon Slavin-3


On 19 Dec 2017, at 8:37pm, zakari <[hidden email]> wrote:

> pasting some logs, Im declaring again this happening only the first time,
> afterwards working without problem.
> 2017-12-17 15:16:23 - execute
> 2017-12-17 15:17:20 - executed
>
> 2017-12-19 14:53:35 - execute
> 2017-12-19 14:54:32 - executed
>
> 2017-12-19 20:14:23 - execute
> 2017-12-19 20:15:20 - executed

I’m missing something here.  All these operations seem to take the same time … around a minute.  Do you have figures for the different one(s) ?

The first time you run it, the table is read into memory.  Subsequent searches don’t need disk access, so they're faster.

If you need to convince your user that all operations are fast, silently execute the SELECT command during startup so that the table is already in cache.

> 1) prepare the statement "select feed_title, feed_url, feed_date from feeds
> where feed_title like :searchTerm or feed_url like :searchTerm  LIMIT :limit
> OFFSET :offset"

There is no way to optimize in SQL for WHERE clause with to LIKE clauses.  The entire table must be read.

It may be that full text searching (SQLite FTS4 extension) would speed things up, but I don’t use it and have no experience.  Perhaps someone who uses it would tell you.  

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users