Slow queries

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

Slow queries

Post by w9mdb »

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: 2925
Joined: 21 Jan 2013, 14:20
Location: Quarto d'Altino - Venezia (ITA)
Contact:

Re: Slow queries

Post by IW3HMH »

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
w9mdb
Old Man
Posts: 434
Joined: 13 Jul 2014, 12:05

Re: Slow queries

Post by w9mdb »

JTAlert does a query which involves having to do "LIKE" on the qsoconfirmations field which is also a very slow query.

What could be done is a single field for each logger type named like CONFIRMED_LOTW, CONFIRMED_EQSL, which is a boolean and correlates to the "R" field in qsoconfirmations.
That would be just two extra fields (that's the only two loggers that confirm, right?) and result in a much faster query and something that can be indexed too.

Mike W9MDB
Post Reply