Re: [sqlite] sqlite3 query really slow with version > 3.7.5

[Owen Kaluza]

On 17 October 2011 17:51, Dan Kennedy  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
>
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 query really slow with version > 3.7.5

[Dan Kennedy]

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

[sqlite] sqlite3 query really slow with version > 3.7.5

[Owen Kaluza]

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, 16 bytes, 0.0100 seconds
... loaded 2 rows, 3390352 bytes, 0.0500 seconds
... loaded 2 rows, 1131968 bytes, 0. seconds
... loaded 2 rows, 1126736 bytes, 0.0100 seconds
... loaded 0 rows, 0 bytes, 0. 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, 16 bytes, 5.2200 seconds
... loaded 2 rows, 3390352 bytes, 5.2100 seconds
... loaded 2 rows, 1131968 bytes, 5. 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users