I wanted to remove and re-install a Storage which contained 710 items.
Removing the Storage was not allowed because it contained items. (I think it would be MUCH better to offer the user the option at that point to remove the items, rather than forcing the user to remove the items first.)
BFN
CAD
I believe the application was not actually frozen, but the delete action took so long that the bug report tool (madExcept) thought it was. madExcept sends a message to the main thread every few seconds, and if the message has not been processed within a minute, the application is regarded frozen - however, this also happens then the main thread is performing a very long operation so it cannot process any messages for more than one minute. From my experience, DELETE operations on the SQLite backend can be very slow.
madExcept suggests that you call a function named ImNotFrozen from loops which are known to trigger false “Application seems to be frozen” messages. I will do so for the deletion loop. I will also add some sort of progress bar, but only in v3.1.
By the way, even when that false message appears, the application will continue running, and when the main thread eventually returns from its loop and processes the ping message from madExcept, the message box will disappear again.
Yes, all of that is correct.
Including the puzzlingly slow performance of SQLlite. :-\
I don’t know what SQL is being run to do the deletion, and whether there’s any scope for optimising that? Presumably you build a temp table of the IDs of the items selected for deletion, then execute several statements like:
DELETE FROM table WHERE table.ID IN temptable.ID
BFN
CAD
I’m a big fan of optimization, too, and the temporary table thing is a nice idea, but it seems to be overkill in this case, doesn’t it?
I mean, honestly, how often do you need to delete that many items in a row?
And would you have filed a bug report if the process had taken such a long time but with a nice progress bar?
I wouldn’t say so. Even for (say) five records, it’s usually MUCH faster than deleting each of the five records individually; and it’s the ‘standard’ way we used to do things like multiple deletions when I was writing commercial SQL code. The benefit being that it scales up beautifully from 1 to n records. 
Since there is currently no method to delete an entire Storage without removing its items first, then I’d say: quite frequently!
Even MS Access does a 710-record delete factorially faster (in only a few seconds) on the same PC.BFN
CAD
Finally implemented a solution (into the latest v3.1 snapshot) which is very similar to what you proposed, Cad. Uses a temporary table. I can now delete 5000 items from my networked database in ~10 seconds.
Performance for moving items around has also improved.
That’s excellent news.
Happy to help with that (it’s a non-obvious solution unless you’ve written production SQL databases ;)).
Although, it would still be useful to have a ‘one-click’ way to remove an entire Storage.
Maybe the answer would be a new Toolbar button named Delete Storage…, which is Enabled only when a Storage is selected in the tree? Clicking this would display a Question MessageBox reading:
[b]If you delete usually-quite-long-Storagename,
you will also remove the number-of-items items it contains from the database.
This cannot be undone.
Are you sure you want to delete usually-quite-long-Storagename?[/b]
[b]Yes No[/b]
The underlying SQL would delete all the items in the Storage first, then actually remove the Storage.
This might not require a temporary SQL table (DELETE FROM items WHERE item IN Storage, or similar: you get the idea!).
Here’s the scenario I’m thinking of:
You have a drive containing say 15,000 tracks, which you plan to split across several drives for performance reasons. Having created the new folders, shares and network drive letters, you move the files (and MMD files, if any ;)) to their designated new locations. You then Synchronise the old Storage to ‘pick up’ the new locations and presumably the new Storages are created as part of this process (I can’t try this here, so I don’t know).
So, once it’s been emptied by that nice screwdriver button in the Synchronise dialog, your original Storage is now rather sad :’( and not needed any more. Unfortunately, you can’t delete it, even though it’s now empty.
I could imagine that sort of thing happening quite often, especially when a station is getting started.
BFN
CAD
mAirList can not move items between storages. The “fix renamed file” feature in the Sync dialog does only work within a single storage.
When you delete an entire storage, all metadata stored in the database will be lost, unless you create MMD files for each item first.
Moving folders from one storage to another (or making a folder a storage of its own, after it has been moved to a different location) can be performed with a few lines of SQL though (in item_filenames, update the “storage” field and modify the “filename” field). I’m not sure if it’s a good idea to offer this as a feature from the GUI, because you might easily break something.
Well, it is obvious to someone who spent a few years of his life teaching database systems and SQL at a university
But after all, it is not trivial that you actually gain performance. Because creating and filling the temporary table takes time, and you never know if the database system will handle the (implicit) JOIN in the delete statement efficiently - it might just perform nested loops, which is as slow as the old dumb method. To prevent this, I made the ID field of the temporary table PRIMARY KEY. This will create an index for it which will help to speed up the process.
Of course, I could have used EXPLAIN first to investigate the performance, but it seems to work fine and fast anyway
True. What I should have said was you would need to Add your new storage, then to Remove the old storage, you would first need to (manually!) remove the items in it first. (Yes, I found the Remove Storage button eventually.)
So really what I’m meaning is: if you click Remove Storage, it should offer to delete any remaining items in that storage from the database, as part of the same one-click processing; rather than the lame message box saying words to the effect of ‘you’ll need to empty the storage first.’
BFN
CAD