View Issue Details

IDProjectCategoryView StatusLast Update
00020621 - BacklogMaintenancepublic2019-06-15 11:30
ReporterK7ZCZAssigned To 
PrioritynormalSeverityminorReproducibilityhave not tried
Status newResolutionopen 
Summary0002062: Logbook performance is poor with a large database
DescriptionI think a bit of rework of the database schema could help make the Logbook perform much faster.

The Logbook often does queries with "COL_CALLSIGN LIKE '%/K7ZCZ/%". Such a filter is not anchored; no index can be used to satisfy the query, so a full table scan is necessary to satisfy the query. That is slow.

Queries of this form seem to occurr because we're concerned with countyr prefixes on callsigns; in Canada, I might be "VE9/K7ZCZ", for example -- but we still want that to match a search for my naked "K7ZCZ" call.

I think a bit of restructuing in the database would make this a lot faster. The problem affects this tab, but this query also runs when the DX cluster is active (to populate the worked markers) and when using the Awards window.
Steps To Reproduce1) Open the logbook; debug build in the debugger
2) Open a large database
3) Double-click any log entry to get an ALE
4) I the ALE window, activate the "Worked" tab
5) after a pause, the Worked tab will populate.

The pause is because the database is being asked to scan all the rows in the large database you've opened.

The debugger logs the queries, they are here:

2017-06-21 16:13:39: ALE:Worked:Refresh: Call="KC3DS", Country="United States"
2017-06-21 16:13:39: LOG> WorkedStatusEx, Select: (COL_CALL = 'KC3DS') OR (COL_CALL LIKE '%/KC3DS/%') OR (COL_CALL LIKE '%/KC3DS') OR (COL_CALL LIKE 'KC3DS/%')
LogfileView.cpp(231) : atlTraceGeneral - SHOW> DBG 1, WorkedStatusEx, Select: (COL_CALL = 'KC3DS') OR (COL_CALL LIKE '%/KC3DS/%') OR (COL_CALL LIKE '%/KC3DS') OR (COL_CALL LIKE 'KC3DS/%')
LogbookFullLookup.cpp(147) : atlTraceGeneral - Callsign "KC3DS" = KC3, KC3DS,


TagsNo tags attached.
ModuleLogbook
Sub-ModuleGeneral
Testing Not Started

Relationships

related to 0002197 assignedK7ZCZ 1 - Backlog Logbook open and close performance is slow 
parent of 0002219 closedK7ZCZ Ham Radio Deluxe Logbook: Loading an XML backup is very slow due to the use of a DOM parser 

Activities

K7ZCZ

2017-08-18 09:52

administrator   ~0004032

This change set adds code which measures some of the performance events in this code path:
https://hrdsoftware.visualstudio.com/HRD/_versionControl/changeset/3779

See issue 2220 for more details

Issue History

Date Modified Username Field Change
2017-06-21 18:22 K7ZCZ New Issue
2017-08-11 17:28 K7ZCZ Relationship added related to 0002197
2017-08-16 16:52 WA9PIE Category Enhancement => Maintenance
2017-08-16 16:52 WA9PIE Steps to Reproduce Updated View Revisions
2017-08-16 16:52 WA9PIE Testing => Alpha Failed
2017-08-17 12:50 WA9PIE Testing Alpha Failed => Not Started
2017-08-18 00:06 K7ZCZ Relationship added parent of 0002219
2017-08-18 09:52 K7ZCZ Note Added: 0004032
2019-06-15 11:30 WA9PIE Project 2 - Next Dev List (Holding Area) => 1 - Backlog