Optimize SQL query - how? (was: SQlite and primary key)

Just starting out? Need help? Post your questions and find answers here.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

If you have a UNIQUE key (or PRIMARY key) on the field CHEMIN_ET_TITRE_COMPLET_EPURE, then you can reduce this to only one INSERT:

Code: Select all

;= Chercher si cette musique est déjà dans la base de données

ChercherDureeMp3(CheminEtTitreComplet)
Duree=DureeMp3
Requete= "INSERT OR IGNORE INTO MUSIQUES (CHEMIN_ET_TITRE_COMPLET_EPURE, DUREE, GENRE) VALUES (" +
         "'" + CheminEtTitreCompletEpure  + "'," +
         "'" + Duree  + "'," +
         "'" + Genre + "'" +
         ")"
DatabaseUpdate(Base, Requete)
And if you do this in a loop,
then you can use the already mentioned transaction at start/end of the loop to speed it up a bit more.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

Like this:

Code: Select all

DatabaseUpdate(Base, "BEGIN")

Repeat
  ;= Chercher si cette musique est déjà dans la base de données
  
  ChercherDureeMp3(CheminEtTitreComplet)
  Duree=DureeMp3
  Requete= "INSERT OR IGNORE INTO MUSIQUES (CHEMIN_ET_TITRE_COMPLET_EPURE, DUREE, GENRE) VALUES (" +
           "'" + CheminEtTitreCompletEpure  + "'," +
          "'" + Duree  + "'," +
           "'" + Genre + "'" +
           ")"
  DatabaseUpdate(Base, Requete)
  
Until ...
  
DatabaseUpdate(Base, "COMMIT")
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

ok thank you infratec
Post Reply