[Delphi] Hoe versnel ik deze database-operatie?

[Varienaja]

Hey all. Ik ben al behoorlijk lang bezig met een progje om m'n MP3's te managen. Daarbij zit natuurlijk een database. Bij het openen van een .m3u-bestand (playlist) heb ik dus een query nodig om van de bestandsnamen in de .m3u de informatie te zoeken uit de database.

Op dit moment gaat dit proces als volgt:

• Maak een temp-table, met daarin volgnummer + Upper(bestandsnaam) (vanuit de m3u)
• Maak nog een temp-table, met daarin TrackID + Upper(bestandsnaam) (vanuit de DB)
• Als laatste volgt de dikke query, die alle gegevens ophaalt van bestandsnamen die overeenkomen in de m3u en de DB. Het veldje volgnummer in temp-table #1 wordt natuurlijk gebruikt om de originele volgorde in de m3u te behouden.

Dat is dus hoe 't proces in z'n werk gaat. Nu heb ik een playlist van bijna 300 nummers en een database, met daarin zo'n 7700 tracks, 660 albums, 315 bands en 260 genres. Het inlezen van een playlist vind ik te lang duren.

De eerste stap bestaat uit:

code:

   for i:=0 to Files.Count-1 do begin
    T.AppendRecord([i+1,UpperCase(Files[i])]);
   end;

Hierin is Files een stringlist. Het adden van 300 piepkleine recordjes duurt ongeveer 5 seconden. Het tabelletje is slechts 86Kbytes groot. Dat moet toch veel en veel sneller kunnen? Maar hoe!?

Aan tweede stap kan ik niet bijzonder veel veranderen, maar duurt toch nog 6 seconden:

code:

   Q.SQL.Add('INSERT INTO MC_TEMP2 SELECT TRACKID, UPPER(SHORTCUT) FROM MC_TRACKS');
   Q.ExecSQL;

Eventueel kan ik hiervan een selectie-query maken (de select zelf is binnen 1 seconde klaar), en dan de temp-table vullen dmv appendrecord, zoals hiervoor. (Hopende dat iemand hiervoor iets snellers weet).

De derde stap is gewoon eentje die lang duurt (ook ongeveer 6 seconden). Hieraan is niet erg veel te veranderen denk ik. Voor de volledigheid maar even de bijbehorende query:

code:

   Q.SQL.Add('SELECT B.BANDNAME, A.ALBUMNAME, T.TRACKNR, T.TRACKNAME, T.TRACKLENGTH,');
   Q.SQL.Add('A.ALBUMRELEASEDATE, T.COMMENT, G.GENRENAME, T.PLAYS, T.LASTPLAY, T.SHORTCUT, T.TRACKID, R.IDX FROM');
   Q.SQL.Add('MC_TRACKS T, MC_ALBUMS A, MC_BANDS B, MC_GENRES G, MC_TEMP1 R, MC_TEMP2 S WHERE');
   Q.SQL.Add('T.BANDID=B.BANDID AND T.ALBUMID=A.ALBUMID AND');
   Q.SQL.Add('T.GENRE=G.GENREID AND T.TRACKID=S.TRACKID AND R.SHORTCUT=S.SHORTCUT ORDER BY R.IDX');

Ik draai dit op een P3-800, met 640MB. En de eerste twee stappen MOETEN gewoon sneller kunnen. Als ik in Paradox "SELECT TRACKID, UPPER(SHORTCUT) FROM MC_TRACKS" laat uitvoeren, dan heb ik binnen de seconde het antwoord in de antwoordtabel :PRIV:answer staan. Waarom duurt het bij "INSERT INTO MC_TEMP2 SELECT TRACKID, UPPER(SHORTCUT) FROM MC_TRACKS" dan 6 seconden voordat de antwoordtabel MC_TEMP2 klaar is!!!?

[Verwijderd]

Ik heb even geen zin om er diep om in te gaan maar je zou wat waarschijnlijk wel goed werkt is een ClientDataSet te gebruiken, je kan deze op verschillende manieren gebruiken, een daarvan is een inmemory table.

Hierin kun je heel snel velden aanmaken en records toevoegen en sorteren. Tevens kun je hem koppelen aan een normale tabel en als je klaar bent met de wijzigingen dan laat je hem in een keer alle wijzigigen doorvoeren naar de echte tabel. Dit is meestal veeeeel sneller dan echte tabelen gebruiken.

Soms is het zelfs sneller om met eigen code de query op een dergelijke tabel te simuleren dan het met sql te doen.

Kijk in ieder geval maar eens naar de ClientDataSet.

[stekkel]

wat betreft insert snelheid: lees het volgende maar.

http://www.mysql.com/doc/I/n/Insert_speed.html

Zijn die tabellen van jou wel geindexeerd? dat wil namelijk nogal schelen.

[mjax]

Heb je al geprobeerd om de tweede stap d.m.v. een TBatchMove component te implementeren? Deze is geoptimaliseerd voor dit soort tabel-kopieer-acties.

[RickN]

Ik snap zowiezo niet zo goed waarom je die 2de temp tabel gaat maakt. Je kunt al die koppelingen die je in de WHERE clause maakt toch ook direkt op die MC_TRACKS tabel maken?

Om precies te zijn vervang in de FROM clause "MC_TEMP2 S" door "MC_TRACKS S". (Nu heb je dus 2x MC_TRACKS in je FROM staan, onder 2 verschillende namen.) Dan kun dat hele vullen van die tweede temp tabel achterwege laten. Misschien dat hierdoor die laatste select statement iets trager wordt, maar dat weet ik zo niet. Je bespaart iig een hoop tijd door die temp tabel niet meer aan te maken.

[Varienaja]

Op donderdag 27 december 2001 11:54 schreef RickN het volgende:
Ik snap zowiezo niet zo goed waarom je die 2de temp tabel gaat maakt...

Omdat "C:\Liedje.mp3" <> "C:\LIEDJE.MP3" zet ik eerst elke bestandsnaam in hoofdletters. Deze hoofdletters wil ik niet in de DB zelf hebben staan, dus daarvoor heb ik die tweede TEMP-table nodig.

edit:
Ik lees nu je post even wat beter: je hebt daar een punt te pakken ff uitproberen!

Ik kan hem niet in de laatste query verwerken, omdat ik niet kan zeggen: WHERE TEMP1.Shortcut=UPPER(T.Shortcut), er kan geen UPPER gebruikt worden in de where.

Stekkel: Ongeindexeerde tabellen zijn juist sneller bij records toevoegen.

Later vandaag ga ik die ClientDataSets eens uitproberen. Bedankt tot zover, en jullie horen nog van me.

[joepP]

Welke DB gebruik je?

Kan je je queries niet queuen en daarna in 1x allemaal uitvoeren?

Waarom gebruik je uberhaubt een database, is een eigen bestandsformaat niet veel sneller in dit geval?

[RickN]

Op donderdag 27 december 2001 12:12 schreef Varienaja het volgende:

[..]

Ik kan hem niet in de laatste query verwerken, omdat ik niet kan zeggen: WHERE TEMP1.Shortcut=UPPER(T.Shortcut), er kan geen UPPER gebruikt worden in de where.

Ja, daar had ik dus ff geen rekening mee gehouden.
Je ZOU kunnen overwegen (hopelijk lezen de database ontwerp goeroes dit niet ) om een extra veld in je database op te nemen waar je de bestandsnaam in hoofdletters in opslaat...

[Varienaja]

Op donderdag 27 december 2001 12:24 schreef joepP het volgende:
Welke DB gebruik je?

Kan je je queries niet queuen en daarna in 1x allemaal uitvoeren?

Waarom gebruik je uberhaubt een database, is een eigen bestandsformaat niet veel sneller in dit geval?

1. Paradox/BDE
2. Nee. Dat heeft zowiezo geen zin, ze worden nu ook vrijwel direct na elkaar uitgevoerd.
3. Voor het matchen van bestandsnamen uit een playlist met een veldje shortcut uit de DB is misschien veel tijd nodig, maar het gebruiken van een 'echte' DB is wel weer heel handig voor allerlei andere dingen in mijn progje.
Misschien wel leuk om erbij te vertellen: mijn allereerste muziek-DB-progje maakte gebruik van een eigen bestandsformaat. Had ik mooi zelf B-bomen geimplementeerd, etc. Geen bitje werd verspild. Dat werkte nou niet dat je zegt echt flitsend, en al helemaal niet bij operaties waar duizenden records beinvloed werden. De versie erna maakte ik gebruik van vaste-lengte records, die tijdens het runnen van het programma in het geheugen werden geladen. Ging stukken vlotter natuurlijk, maar door allerlei gepruts ben ik nogal eens m'n data kwijtgeraakt (denk aan crashes).

Eigen bestandsformaten zijn echt heel leuk hoor, maar Paradox werkt gewoon, en ik ben van het gelazer af

TBatchmove is trouwens trager dan een INSERT INTO query. Ik probeer nu even de ClientDataSet.

[joepP]

Ok.

Ik had hetzelfde probleem bij het aanmaken van een .dbf file via ADO/ODBC/Delphi Database Meuk. Ook een maximale performance van zo'n 50-100 records per seconden (bij INSERT operaties), dat schiet dus gewoon echt NIET op als je 5000 records moet overpompen. Ik heb echt alles geprobeerd, maar het wilde gewoon niet sneller.

Maar niet getreurd, er is een oplossing: native file support. Ff zoeken bij www.torry.net naar een componentje dus! Mocht het daar niet te vinden zijn kan je het altijd nog zelf schrijven, ik denk dat het paradox formaat wel goed gedocumenteerd is...

Suc6!

[Varienaja]

Nou, ik ben er uit. Helaas heb ik niks gevonden om insert-operaties sneller te maken. En ik kan er nog steeds niet bij dat: INSERT INTO MC_TEMP2 SELECT TRACKID, UPPER(SHORTCUT) FROM MC_TRACKS in Paradox in 1 tel klaar is, terwijl het onder Delphi secondenlang duurt.

Ik doe het nu als volgt:

code:

Voor iedere file in de playlist doe
   Zoek (mbv een index) en zo goed mogelijk matchende track
   Als Uppercase(bestandsnaam)==Uppercase(GevondenInDB)
    Voeg TrackID toe aan Temptable
   End
End

En daarna komt de grote query weer in actie, die nu slechts 1 temp-table nodig heeft. Waar het eerst rond de 20 seconden duurde, kost 't nu 2 tellen