Deleting a simple record is brutally slow (> 100 seconds)

Frit

Hi folks,

UPDATE MAR 5: I have 800 items in the library, not 300.

WinXP Pro FF 2.0.0.12

I've got a P3-700/512MB laptop and a 1GB AMD X2 4800+ desktop. Maybe 300? records in my Zotero library which is about a 53MB zotero.sqlite. I link to a bunch of pdfs of various sizes. Needless to say the laptop is much slower but the desktop is also slow(er than it should be?).

Why does it take over a minute and a half to delete the simplest of records on my laptop? I.e. I add a new book section (initially blank) then give it a title "hfhfhf" and then delete it. About 105 seconds later, it completes the deletion. The Task Manager shows still >100MB of physical RAM not allocated so it doesn't appear to be a resource issue aside from the slower processor.

I got the instructions from another page to get a console printout and can tell you that Zotero is sitting for 95 seconds just after it prints to the console the following sequence:

zotero(5): Decreasing transaction level to 0

zotero(3): Notifier.trigger('delete', 'item', [14695]) queued

zotero(5): SELECT creatorID FROM creators WHERE creatorID NOT IN (SELECT creatorID FROM itemCreators);

zotero(5): Transaction in progress -- increasing level to 1

zotero(5): SELECT tagID, tag, tagType FROM tags WHERE tagID NOT IN (SELECT tagID FROM itemTags);

zotero(5): Decreasing transaction level to 0

zotero(5): DELETE FROM fulltextWords WHERE wordID NOT IN (SELECT wordID FROM fulltextItemWords)

==== PLAY JEOPARDY MUSIC HERE ======


Any ideas?

I love Zotero but these performance issues are killing me. (The other issue is that Zotero locks up the machine after I type the first letter in the search box. Say I'm searching for 'Fred'. I type F and some time later (many tens of seconds) I can complete the word). I guess that is 'search as you go'? If so, it's unusable and frustrating. I'd like to be able to default to the search type I get when I use double quotations.

Ken

MTBradley

The unusability of the search box is an issue when I use Zotero for citations. My library is so large that visually scanning it takes some attention, but that's the only option since it would take five minutes to type in a search term. The problem is compounded by the fact that there is no option to expand the box in order see more than a few words of the entries during my visual search of the records.

Is this program really going to work significantly faster on Firefox 3? As in 5x-10x (or more) faster rather than twice as fast?

dstillman

Is this program really going to work significantly faster on Firefox 3? As in 5x-10x (or more) faster rather than twice as fast?

Actually about 13x faster at last count. After some optimizations, a quicksearch on a test library with 7,000 items has gone from 82 seconds to 6 seconds.

scot

Wow, that's excellent.

Frit

What have tests shown for deleting an item such as the case I mentioned above?

dstillman

Certain delete options should be much faster, since in some cases the delay after deleting was just from having to refresh the items list.

However, your debug output seems to suggest that it's stalling on the purge of the fulltext word index. A full purge definitely doesn't need to happen on every item deletion, but I'm also surprised it's taking as long as it seems to be for you. Can you tell if it's disk-bound or CPU-bound?

If you have the time/inclination, could you grab the SQLite command line client, open a copy of zotero.sqlite, and run these two commands:

SELECT COUNT(*) FROM fulltextWords;
SELECT COUNT(*) FROM fulltextItemWords;

On my ~7,000-item test library, the results are 49,185 and 326,818, respectively, and that DELETE statement takes about 2 seconds. You could try that last DELETE statement on a copy of your database and see how long it takes.

Frit

Thanks for the reply, Dan.

Here you go.

Output from first statement - 123,886
Output from second statement - 1,154,093

I imagine the difference from your results might be that I've got links to a bunch of sizable pdfs??? Just a guess.

I just tried deleting a real entry, this time a webpage that I had "create[d] a new item from web page"

This took 2 minutes to delete and it is CPU-bound. Firefox was taking ~96% of CPU for the two minutes, RAM was about 340MB used of 512MB. Disk was not being accessed much. Idle CPU is about 5%.

Note sure what you mean by running the DELETE op. If you mean run a command line DELETE, then I don't know how to do that for a specific record.

Ken

dstillman

Ken: Thanks for the info. It may very well just be the size of your fulltext word index. We'll likely be reworking this in Firefox 3, which gives us SQLite's new fulltext indexing system.

But as a test, could you open a copy of the database and run this command:

DELETE FROM fulltextWords WHERE wordID NOT IN (SELECT wordID FROM fulltextItemWords);

This is to see if it's that command that's taking so long or whether the delay is somewhere else.

Frit

Dan: Took about 60 seconds to run that one command.

Ken

dstillman

Took about 60 seconds to run that one command.

Wow. That seems far too slow (though maybe not for the P3).

Have you tried running your database through the DB Repair Tool? It's possible one of the fulltext word indexes just got corrupted and is not being used for the query, which could cause that query to be so slow. If you'd like to send the Upload ID the tool provides to support@zot....org, I can also take a look.

marcellop

I had the same problem and a similar setting (some large pdfs, >100K fulltextwords, >1M fulltextitemwords, and the db was not corrupted). After reading your conversation I decided to delete the fulltext index, had to wait about 6 hours, but finally, deleting a record is now a matter of seconds. I don't miss the fulltext capability since I'm using an external desktop search tool, and I feel more confident in using Zotero as my sole reference manager. Thanks a lot!

Frit

Dan: did as you suggested and have sent you an email with uploadID.

The massaged file that I downloaded seems to take longer to delete (2min 25 sec. ~25% longer than before)) than before but I have a added a few - not many - more refs since I timed it last. I'm going back to my backup sqlite file.

Cheers
Ken

dstillman

Frit: I took a look at your database. On a PowerBook G4, the DELETE statement took about a minute. On a Mac Pro, it took 5 seconds. So, the take-home point is that performance may vary wildly based on system/disk speed.

We'll likely be switching to SQLite's native fulltext indexing system in Zotero 1.5, which we're hoping will help quite a bit with performance. For the 1.0 branch, I've created a ticket to not purge the fulltext word index on every item deletion.

Re: the downloaded file taking longer, the repair tool regenerates your database with auto-vacuuming turned on, which saves disk space at the potential cost of a bit of speed during deletions, so that might account for the difference. Auto-vacuuming has been enabled on new Zotero installs since early betas but wouldn't be enabled on databases created by very early versions of Zotero.