Re: Slow queries 2
Posted: 24 Jun 2020, 12:45
by w9mdb
The debug log doesn't show the total time...just the query time. I've got over 34,000 QSOs in the log.
The actual query I've tested and by itself takes a little over 2.5 seconds evenly split between query and fetch according to MySQL Workbench timing.
In order to get any QSL status in this database settup you have to fetch the entire database and apparently somebody is then doing LINQ queries on the field which is another database operation and not nearly as fast as an indexed database field.
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
Then LOG4OM takes almost another 2.5 seconds to process the records. I get 100% cpu sage for just under 5 seconds trying to select LOTW records to upload.
Under database normalization the query/fetch/display should take less then 300ms but since you can't query or index the QSL fields can't be done that fast.
Under Log4OM1 the same LOTW operation doesn't even create a blip on the performance graph and I see 227ms doing the LOTW query on that side.
SELECT qsoId, address, age, AIndex, AntAz, AntEl, AntPath, ArrlSect, ArrlCheck, TheClass, CreditSubmitted, CreditGranted, TenTen, MyAntenna, Band, bandRx, log.call, comment, cont, contactedOp, contestId, country, Cqz, Distance, dxcc, eqCall, email, eqslQslrdate, eqslQslsdate, eqslQslRcvd, eqslQslSent, ForceInit, Freq, FreqRx, gridsquare, iota, iotaIslandId, Ituz, KIndex, lat, lon, MaxBursts, msShower, myLat, myLon, lotwQslrdate, lotwQslsdate, lotwQslRcvd, lotwQslSent, mode, myCity, myCnty, myCountry, MyCqZone, myGridsquare, myIota, myIotaIslandId, MyItuZone, myName, myPostalCode, myRig, mySig, mySigInfo, myState, myStreet, Name, notes, NrBursts, NrPings, pfx, theOperator, ownerCallsign, precedence, propMode, publicKey, programid, programversion, qslmsg, qslrdate, qslsdate, qslRcvd, qslRcvdVia, qslSent, qslSentVia, qslVia, qsoComplete, qsoDate, qsoDateOff, qsoRandom, qth, rig, Sfi, rstRcvd, rstSent, satMode, satName, sig, sigInfo, srx, srxString, state, stationCallsign, stx, stxString, swl, timeOff, timeOn, validatedCallsign, manualDateTime, mobile, qrp, portable, callsignUrl, homepageUrl, contest, cnty, submissionstatus, ClubLogQsoUploadDate, ClubLogQsoUploadStatus, AwardSubmitted, AwardGranted, Fists, FistsCc, HrdlogQsoUploadDate, HrdlogQsoUploadStatus, MyFists, MySotaRef, MyUsacaCounties, MyVuccGrids, QrzcomQsoUploadDate, QrzcomQsoUploadStatus, RxPwrDecimal, Skcc, SotaRef, SubMode, TxPwrDecimal, UsacaCounties, VuccGrids, QsoAwards FROM LOG WHERE (lotwqslsent = 'R' OR lotwqslsent = 'Q') ORDER BY QsoDate DESC, TimeOn DESC;
You'll have to forgive me...I'm a speed freak and when I know something should be more than 10 times faster it tends to stick out to me.