Quantcast

sqlite3 query really slow with version > 3.7.5

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

sqlite3 query really slow with version > 3.7.5

Owen Kaluza-2
Hi,

After upgrading my OS I noticed a huge delay loading my application, I
narrowed it down to my system libsqlite3 being upgraded from 3.7.4 to 3.7.7
I did some quick testing with different versions and it seems the change
comes about between 3.7.5 and 3.7.6.2
The query difference is huge:

Open database successful, SQLite version 3.7.5
... loaded 8 rows, 11300352 bytes, 0.2000 seconds
... loaded 2 rows, 160000 bytes, 0.0100 seconds
... loaded 2 rows, 3390352 bytes, 0.0500 seconds
... loaded 2 rows, 1131968 bytes, 0.0000 seconds
... loaded 2 rows, 1126736 bytes, 0.0100 seconds
... loaded 0 rows, 0 bytes, 0.0000 seconds
0.2800 seconds to load 16 geometry records from database

Open database successful, SQLite version 3.7.6.2
... loaded 8 rows, 11300352 bytes, 5.4300 seconds
... loaded 2 rows, 160000 bytes, 5.2200 seconds
... loaded 2 rows, 3390352 bytes, 5.2100 seconds
... loaded 2 rows, 1131968 bytes, 5.0000 seconds
... loaded 2 rows, 1126736 bytes, 4.9900 seconds
... loaded 0 rows, 0 bytes, 4.9800 seconds
30.8400 seconds to load 16 geometry records from database

As you can see from the last row the same delay occurs even when no records
are found!
Queries to other tables in the db cause no problem, it's only the table that
holds the majority of the data (in blob fields), example query:

SELECT
timestep,rank,idx,type,data_type,size,count,width,minimum,maximum,dim_factor,units,labels,data
FROM geometry WHERE object_id=1 AND timestep=0  ORDER BY idx,rank;

The delay occurs in the read loop on: sqlite3_step(statement);
Same delay if I issue this query in the sqlite3 shell (~ 5 seconds).

Table schema:
CREATE TABLE geometry (id INTEGER PRIMARY KEY ASC, object_id INTEGER,
timestep INTEGER, rank INTEGER, idx INTEGER, type INTEGER, data_type
INTEGER, size INTEGER, count INTEGER, width INTEGER, minimum REAL, maximum
REAL, dim_factor REAL, units VARCHAR(32), labels VARCHAR(2048), properties
VARCHAR(2048), data BLOB, FOREIGN KEY (object_id) REFERENCES object (id) ON
DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (timestep) REFERENCES timestep
(id) ON DELETE CASCADE ON UPDATE CASCADE);

Any ideas what has changed that could cause this and if there's a way I can
work around it?

Thanks in advance,
Owen.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 query really slow with version > 3.7.5

Dan Kennedy-4
On 10/17/2011 11:05 AM, Owen Kaluza wrote:

> Hi,
>
> After upgrading my OS I noticed a huge delay loading my application, I
> narrowed it down to my system libsqlite3 being upgraded from 3.7.4 to 3.7.7
> I did some quick testing with different versions and it seems the change
> comes about between 3.7.5 and 3.7.6.2
> The query difference is huge:
>
> Open database successful, SQLite version 3.7.5
> 0.2800 seconds to load 16 geometry records from database
>
> Open database successful, SQLite version 3.7.6.2
> 30.8400 seconds to load 16 geometry records from database
>
> As you can see from the last row the same delay occurs even when no records
> are found!
> Queries to other tables in the db cause no problem, it's only the table that
> holds the majority of the data (in blob fields), example query:
>
> SELECT
> timestep,rank,idx,type,data_type,size,count,width,minimum,maximum,dim_factor,units,labels,data
> FROM geometry WHERE object_id=1 AND timestep=0  ORDER BY idx,rank;
>
> Table schema:
> CREATE TABLE geometry (id INTEGER PRIMARY KEY ASC, object_id INTEGER,
> timestep INTEGER, rank INTEGER, idx INTEGER, type INTEGER, data_type
> INTEGER, size INTEGER, count INTEGER, width INTEGER, minimum REAL, maximum
> REAL, dim_factor REAL, units VARCHAR(32), labels VARCHAR(2048), properties
> VARCHAR(2048), data BLOB, FOREIGN KEY (object_id) REFERENCES object (id) ON
> DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (timestep) REFERENCES timestep
> (id) ON DELETE CASCADE ON UPDATE CASCADE);


Likely you are hitting a problem causing SQLite to create an automatic
index for this type of query. Fixed here:

   http://www.sqlite.org/src/ci/27c65d4d9c?sbs=0

Updating to 3.7.8 should fix it.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sqlite3 query really slow with version > 3.7.5

Owen Kaluza-2
On 17 October 2011 17:51, Dan Kennedy <[hidden email]> wrote:

>
> Likely you are hitting a problem causing SQLite to create an automatic
> index for this type of query. Fixed here:
>
>  http://www.sqlite.org/src/ci/**27c65d4d9c?sbs=0<http://www.sqlite.org/src/ci/27c65d4d9c?sbs=0>
>
> Updating to 3.7.8 should fix it.
>
>
That's it!, I've set
PRAGMA automatic_index = false;
and it's back to normal speed.

Seems a very long hold up just for creating an index though - It's a very
small table really, < 100 rows.

Anyway happy enough with a workaround for now, appreciate your help.

Cheers,
Owen.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Loading...