CC V3.4.2 has been published

[HarryT]

Wow - that's an amazing improvement! There does seem to be something that's being seriously messed up by the database update in the recent release, by the sound of it. Do you just send it SQL commands to update the database schema? "ALTER DATABASE...", etc?

[chaley]

Originally Posted by HarryT

Wow - that's an amazing improvement! There does seem to be something that's being seriously messed up by the database update in the recent release, by the sound of it. Do you just send it SQL commands to update the database schema? "ALTER DATABASE...", etc?

Stock SQL. Since you seem to know this stuff, here are the statements.

V14 to V15: remove a constraint on a column. SQLite cannot do that with an ALTER TABLE so you must create a new table and copy the info.

	db.execSQL("create table if not exists " + "newCats" + " (" +
			C.KEY_CAT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			C.KEY_CAT_GROUP + " TEXT, " +
			C.KEY_CAT_NAME + " TEXT, " +
			C.KEY_CAT_NAME_SORT + " TEXT COLLATE LOCALIZED, " +
			C.KEY_CAT_FL + " TEXT COLLATE LOCALIZED);");

	db.execSQL("INSERT INTO newCats SELECT * FROM " + C.TABLE_CATEGORIES + ";");
	db.execSQL("DROP TABLE " + C.TABLE_CATEGORIES + ";");
	db.execSQL("ALTER TABLE newCats RENAME TO " + C.TABLE_CATEGORIES + ";");

V15 to V16: add the new date_added_to_cc column.

	db.execSQL(buildQuery("ALTER TABLE", C.TABLE_BOOKS, "ADD COLUMN", C.KEY_DATE_ADDED_TO_CC, "TIMESTAMP;"));
	db.execSQL(buildQuery("UPDATE", C.TABLE_BOOKS, "SET", C.KEY_DATE_ADDED_TO_CC, "=", C.KEY_DATE_CHANGED));

One thing occurs to me. When I developed the 14->15 upgrade I checked to see if the indices attached to the original table remained after the rename. Perhaps I missed something and they were dropped. If so then that would account for the loss of performance, turning every query into a full table scan.

I will build and populate a V3.3.6 install, upgrade it to V3.4.2, then do QUERY EXPLAIN to see if the indices are still in use.

[HarryT]

There's certainly nothing obviously amiss with those SQL commands that I can see. An indexing issue does seem quite likely - with 3500 books in my database, a full table scan (especially if it's doing joins to other tables) could be very slow.

[chaley]

It is indeed almost certainly the (lack of) indices.

Here is the query plan after upgrade from V3.3.6 to V3.4.2.

EXPLAIN QUERY PLAN FOR getAllBooks  SELECT id FROM  Categories , Books , cat_books_link WHERE cat_group =? AND cat_name =? AND  cbl_category = cat_id AND id = cbl_book ORDER BY author_sort ASC,title_sort ASC,title_sort ASC  ;
QUERY PLAN getAllBooks: 0:0:0:SCAN TABLE Categories (~10000 rows)
QUERY PLAN getAllBooks: 0:1:2:SEARCH TABLE cat_books_link USING AUTOMATIC COVERING INDEX (cbl_category=?) (~7 rows)
QUERY PLAN getAllBooks: 0:2:1:SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
QUERY PLAN getAllBooks: 0:0:0:USE TEMP B-TREE FOR ORDER BY

Note the first QUERY PLAN line. It is using a scan.

Here is the query plan for V3.4.2 after recreating the indices

EXPLAIN QUERY PLAN FOR getAllBooks  SELECT id FROM  Categories , Books , cat_books_link WHERE cat_group =? AND cat_name =? AND  cbl_category = cat_id AND id = cbl_book ORDER BY author_sort ASC,title_sort ASC,title_sort ASC  ;
QUERY PLAN getAllBooks: 0:0:0:SEARCH TABLE Categories USING INDEX cat_group_fl (cat_group=?) (~2 rows)
QUERY PLAN getAllBooks: 0:1:2:SEARCH TABLE cat_books_link USING COVERING INDEX cbl_lines (cbl_category=?) (~10 rows)
QUERY PLAN getAllBooks: 0:2:1:SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
QUERY PLAN getAllBooks: 0:0:0:USE TEMP B-TREE FOR ORDER BY

Now the first query in the plan uses an index. In addition, the second part of the query now uses a permanent index instead of an automatic one that will be dropped from time to time.

There are similar examples with other queries.

The categories table is large, one line per unique item (author, publisher, tag, etc). Scanning that table on a large database is certainly suboptimal.

I will add a rebuild of the indices to the database vacuum function. I will also be sure to rebuild the indices on the next CC release.

[HarryT]

Excellent - delighted to hear you've found the cause of the problem. It's probably not going to affect many people as badly as it affected me, because I have the very worst combination of factors: a large book library, and a VERY slow device with little RAM. As I noted in an earlier post, the performance of 3.4.2 on my Nexus 7 (a much, much faster CPU and a lot more RAM) is absolutely fine, with grouping nodes opening instantly.

[kaufman]

I have found a bug in 3.4.2.

There are three ways to do a sort:

1) Select the sort in the sort dialog.
2) Select a grouping with an autosort
3) Click on a link on the book details screen

If you select a sort in the sort dialog, it does four things:

1) It changes the order of the books in the book list
2) It shows the current sort in the Grouping drawer
3) If you pop up the sort dialog, the sort will be checked
4) If you are doing a sort other then title or date It will show the sort key in the book list

It appears that when sorting on a custom column, if you tap on the link in the book details screen or select a group with an autosort, it does 1 and 2, but not 3 and 4.

I don't think it needs a release to fix it, but maybe for the next release.

Note: Actually, now that I think about it, I actually would rather not see it anyhow. So if you do fix it, I will probably hide it in when you do "extended information in list view".

[chaley]

Originally Posted by kaufman

I have found a bug in 3.4.2.

There are three ways to do a sort:

1) Select the sort in the sort dialog.
2) Select a grouping with an autosort
3) Click on a link on the book details screen

If you select a sort in the sort dialog, it does four things:

1) It changes the order of the books in the book list
2) It shows the current sort in the Grouping drawer
3) If you pop up the sort dialog, the sort will be checked
4) If you are doing a sort other then title or date It will show the sort key in the book list

It appears that when sorting on a custom column, if you tap on the link in the book details screen or select a group with an autosort, it does 1 and 2, but not 3 and 4.

I don't think it needs a release to fix it, but maybe for the next release.

Confirmed the bug in 3.4.2, and confirmed that it has already gone away in my development version.

Note: Actually, now that I think about it, I actually would rather not see it anyhow. So if you do fix it, I will probably hide it in when you do "extended information in list view".

I had no intention of allowing someone to hide "standard" information. I was going to permit adding more information. I am quite sure that I will keep that point of view.

What I will consider is an option not to display the sorted-by line in the book list. This would apply to all sorted-by lines, not to individual sorts.

[kaufman]

Originally Posted by chaley

What I will consider is an option not to display the sorted-by line in the book list. This would apply to all sorted-by lines, not to individual sorts.

That works for me. Thanks.

[chaley]

Originally Posted by chaley

What I will consider is an option not to display the sorted-by line in the book list. This would apply to all sorted-by lines, not to individual sorts.

I ended up doing it on a sort-by-sort basis.


Also, I have added changing the order in the grouping drawer and the sort menu.

[kaufman]

Thanks, that look great. If this going to be in an upcoming 3.4.3, or are you just starting with the changes for 3.5?

You should know that you have zero credibility with me when you say you aren't willing to do something, because typically after you say that, your next post is about how you did it already.

[chaley]

Originally Posted by kaufman

Thanks, that look great. If this going to be in an upcoming 3.4.3, or are you just starting with the changes for 3.5?

It won't be in 3.4.3. That is going to be a bug fix release, probably made available in early Sept.

Whether the new functions I am doing now will be 3.4.X or 3.5 I don't know. I can make an argument either way.

You should know that you have zero credibility with me when you say you aren't willing to do something, because typically after you say that, your next post is about how you did it already.

I actually worry about that.

This time it turned out to be the same amount of work to do an option per sort as a single option. I didn't see much point of doing the single option simply to be consistent.

[kaufman]

Originally Posted by chaley

I actually worry about that.

Well, I wouldn't worry too much. I say you have no credibility, but the truth is that each time you do it I am pleasantly surprised.