Being an old database guy it seems to me the database updates from eQSL and LOTW are slower than they should be.
Are these updates wrapped in a transaction? That normally makes group updates notably faster.
Mike W9MDB
Database speed
-
IW3HMH
- Site Admin
- Posts: 2925
- Joined: 21 Jan 2013, 14:20
- Location: Quarto d'Altino - Venezia (ITA)
- Contact:
Re: Database speed
Hi Mike
Some activities works under transaction, but not this one.
There are several check made by the code when informations from LOTW are downloaded, for each QSO retrieved.
We update references, awards and other informations making queries to the database prior to save updated informations.
Anyway this operation is pretty fast. Slow reports usually comes from database stored somewhere in the network or in slow resources (like USB Keys), or exceptionally big log (>150k QSO)
Some activities works under transaction, but not this one.
There are several check made by the code when informations from LOTW are downloaded, for each QSO retrieved.
We update references, awards and other informations making queries to the database prior to save updated informations.
Anyway this operation is pretty fast. Slow reports usually comes from database stored somewhere in the network or in slow resources (like USB Keys), or exceptionally big log (>150k QSO)
Daniele Pistollato - IW3HMH
Re: Database speed
I have about 12,000 QSOs.
I'm a software person too and have been working database stuff for decades now.
I don't understand why all this can't be done under a transaction.
Any failure would simply roll back and you wouldn't update the "QSL Rcvd since"
All queries during transactions are done against the transacated data so you'd get the same results...just a LOT faster since it simply doesn't do the final commit until you're done.
Not running a transaction means your doing an atomic commit on every QSO would is quite slow by comparison.
"Slow" to me is simply "slower than it should be".
I recently did a resync with eQSL and it took FOREVER (hours) to retrieve and sync 12,000 QSOs (had to do it in batches as was hitting timeouts). It was the database update that was notably slow...not eqsl's retrieval. eQSL apparently has a problem with dates in certain situations...e.g. add a QSO that you missed and it apparently uses the QSO date and not the QSO Received Date.
Mike
I'm a software person too and have been working database stuff for decades now.
I don't understand why all this can't be done under a transaction.
Any failure would simply roll back and you wouldn't update the "QSL Rcvd since"
All queries during transactions are done against the transacated data so you'd get the same results...just a LOT faster since it simply doesn't do the final commit until you're done.
Not running a transaction means your doing an atomic commit on every QSO would is quite slow by comparison.
"Slow" to me is simply "slower than it should be".
I recently did a resync with eQSL and it took FOREVER (hours) to retrieve and sync 12,000 QSOs (had to do it in batches as was hitting timeouts). It was the database update that was notably slow...not eqsl's retrieval. eQSL apparently has a problem with dates in certain situations...e.g. add a QSO that you missed and it apparently uses the QSO date and not the QSO Received Date.
Mike
-
IW3HMH
- Site Admin
- Posts: 2925
- Joined: 21 Jan 2013, 14:20
- Location: Quarto d'Altino - Venezia (ITA)
- Contact:
Re: Database speed
It's not under transaction because of the modular structure of the database management.
When i pass an array of QSO, this array will work in a single transaction. When i pass a single QSO this is not under transaction, because the transaction is atomic.
Choice is related to the process that reads QSO, checks informations and save the QSO into the database.
DB time is little compared to all other operations. Keeping the transaction open will prevent other database activities and updates, so it's used only when necessary and only into the database layer logic.
There are no transaction that involves activities on database and user interface/external services
When i pass an array of QSO, this array will work in a single transaction. When i pass a single QSO this is not under transaction, because the transaction is atomic.
Choice is related to the process that reads QSO, checks informations and save the QSO into the database.
DB time is little compared to all other operations. Keeping the transaction open will prevent other database activities and updates, so it's used only when necessary and only into the database layer logic.
There are no transaction that involves activities on database and user interface/external services
Daniele Pistollato - IW3HMH
