Slow queries

Post Reply
w9mdb
Old Man
Posts: 192
Joined: 13 Jul 2014, 12:05

Slow queries

Post by w9mdb » 23 Jun 2020, 19:06

The LOTW upload query is a good example of the lack of normalization in the database.
This query has to retrieve the entire database so it can parse the QSL field for LOTW QSO's to upload.
This same every-qsl-in-a-field-regardless-of-source also slows down other queries.
The lot Log4OM does this same operation MUCH faster since the LOTW status field can be queried directly.

SELECT qsoid, callsign, band, mode, qsodate, address, stationcallsign, dxcc, arrlsect, qsocomplete, age, aindex, antaz, antel, antpath, antenna, arrlcheck, bandrx, callsignurl, `class`, cnty, comment, cont, contactassociations, contactedop, contestid, country, cqzone, distance, eqcall, email, forceinit, freq, freqrx, gridsquare, ituzone, kindex, lat, lon, maxbursts, msshower, myassociations, mydxcc, mylat, mylon, mycity, mycnty, mycountry, mycqzone, mygridsquare, myituzone, myname, mypostalcode, mystreet, myrig, mysig, mysiginfo, mystate, name, notes, nrbursts, nrpings, pfx, `operator`, ownercallsign, precedence, propmode, programid, programversion, qslmsg, qslvia, qsoenddate, qsorandom, qth, rxpwr, sig, siginfo, rstrcvd, rstsent, satelliteqso, satmode, satname, sfi, srx, srxstring, state, stx, stxstring, swl, txpwr, qsoconfirmations, contactreferences, myreferences from log ORDER BY log.QsoDate DESC

User avatar
IW3HMH
Site Admin
Posts: 2645
Joined: 21 Jan 2013, 14:20
Location: Quarto d'Altino - Venezia (ITA)
Contact:

Re: Slow queries

Post by IW3HMH » 24 Jun 2020, 07:58

I know that and it's a design choice.
There are 3 ways to store confirmations (and other data structures).

1) store into a JSON structure will allow me to add future services without changing the database structure. It's a bit slower but everything else is performed in memory. Single access to database, both reading and writing. That's my choice.

2) store each confirmation into a dedicated field. A change in the list of supported services will require a change on database. That will make database no more compatible with newer versions.

3) Normalize database. That would create a large number of tables. This will require separate multiple queries and in-memory data merging, as a single query will produce several cartesian products. That solution will be the more elegant. Much slower (multiple queries on DB - that reside on disk), in-memory merge. Yes it's faster on LOTW UPLOAD/DOWNLOAD phase, but only there and few other parts.

Log4OM works very little with database. Everything is moved into classes and then moved through different parts of the application.
Daniele Pistollato - IW3HMH

Post Reply