Slow queries
Posted: 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
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