mherger wrote: > > Are you storing your tables in library.db, too? > > I was wondering about the dropping of tables (or truncate), too. But > Andy left that note: > > -- Use DELETE instead of TRUNCATE, as TRUNCATE seems to need unlocked > tables. > > I'd expect a drop table to be as restrictive at least. Did you never > encounter any problems? Because yes, delete is slow. > Does SQLite have a TRUNCATE command ? Isn't this done with SQLite using a DELETE without WHERE ?
Anyway, my problem is that I really need to use DELETE with a WHERE directive so TRUNCATE wouldn't work. What I do is: If more than 40000 rows matching the WHERE criteria -- CREATE TEMPORARY TABLE (temporary table).... AS SELECT ... FROM (original table) WHERE (inversed criteria) -- DROP TABLE (original table) -- CREATE TABLE (original table) -- INSERT INTO (original table) SELECT ... FROM (temporary table) -- DROP TABLE (temporary table) If less than 40000 rows matching the WHERE criteria -- DELETE FROM (original table) WHERE ... (criteria) mherger wrote: > > Just a few comments (and keep in mind I'm no SQL expert at all, only > just read quite a few performance FAQs :-)): > > > SELECT id,url from tracks where audio=1 limit 1 offset ? > > Wouldn't it be faster to create a statement handler with this query > (without the limit & offset) and iterate it? > It might be faster but it will leave the transaction open during the whole scanning process which results in gigantic WAL files if I remember correctly. I did it the way you suggested first, but I had to change it because it caused problem when the WAL feature was activated in SQLite. mherger wrote: > > > ... > > from tracks,contributor_track,contributors > > where > > tracks.audio=1 and > > tracks.id=contributor_track.track and > > contributor_track.role=1 and > > contributor_track.contributor=contributors.id[/color] > > Could you change the order of the where statements? If I understood the > > FAQs correctly (http://www.sqlite.org/optoverview.html), only the first > > statement's index would be used to narrow down the selection of records. > > In your case that's the "audio=1", which would probably still return > >>90% of all tracks. If you used the tracks.id first, that might > probable speed up the processing? > Thanks, will give a try as soon as I get some time. mherger wrote: > > TBH: I still don't understand why vacuum would have such a large impact, > > as I'd expect the DB to be smart enough to only read those parts of the > > data which it requires. Thus the file _size_ shouldn't matter, should > it? > No idea, but apparently it helps a lot for people with big libraries. Erland Isaksson ('My homepage' (http://erland.isaksson.info)) (Developer of 'many plugins/applets (both free and commercial)' (http://wiki.slimdevices.com/index.php/User:Erland). If you like to encourage future presence on this forum and/or third party plugin/applet development, 'consider purchasing some plugins' (http://license.isaksson.info)) *Interested in the future of music streaming ? 'ickStream - A world of music at your fingertips' (http://forums.slimdevices.com/showthread.php?98467-Pre-Announcement-ickStream&p=743516)*. ------------------------------------------------------------------------ erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=101469 _______________________________________________ beta mailing list [email protected] http://lists.slimdevices.com/mailman/listinfo/beta
