Prestanda i sökningar i FileMakerdatabas med fem miljoner poster

Jag fick en förfrågan nyss angående hur bra prestanda FileMaker har när det gäller sökningar i många poster, alltså flera miljoner poster.

Jag kan rapportera att sökningarna är ögonblickliga, jag kan inte mäta tiden från det att jag trycker på enter tills de 11 poster jag räknar med skall finnas där finns där.

Så här testade jag:

1. Jag använde mig av det manus jag skapat tidigare i denna tråd

http://www.99.se/microsoft/245671-skapa-bokstavsserie-f-r-numrering.html

Det manuset producerar en textfil med 456.976 unika värden.

2. Jag skapade en liten loop i en FM-databas som ser ut enligt bilden nedan som importerar samma fil om och om igen. Jag hade redan importerat den två gånger, så nio importer till gav 5.026.736 poster.

3. Första sökningen är väldans långsam, FileMaker måste då indexera fältet, så det tar ett par minuter.

4. Varje sökning jag sedan gör är alltså sekundsnabb.

Med instruktionerna i denna tråd så kan du alltså bygga en test-databas själv och prova med och visa chefen sedan.

Bilder av importmanuset och ett sökresultat:

Fick en rolig följdfråga på problemet jag inledde denna tråd med. Frågan gällde vad blir prestandan på sökningar där man söker på wildcards. Säg att man behöver söka på tex chassinummer eller registreringsnummer på en bil, serienummer på en pistol eller liknande där man bara har tillgång till ett par siffror i rätt positioner, men inte övriga.

I FileMaker finns det två typer av wildcards:

* Som betyder ett eller flera tecken
@ Som betyder endast ett tecken (jepp, det är helt idiotiskt att ha @ som sådan symbol, det har de säkerligen ångrat några gånger, men som vanligt finns det workarounds på det med).

En sökning på en wildcard O*LA gav svar på 10-15 sekunder (jag räknade sekunder, hade inget tidtagarur). En sökning på O@A@ gav svar på ungefär samma tid. En sökning på O@AA (ett wildcard) tog ca 30 sekunder

Detta var ett intressant problem så jag bestämde mig för att kolla om man kunde få snabbare sökningar via relationer. Ett antal uppdateringar av index i 5 miljoner poster senare, kan jag rapportera att det går att få ögonblickliga sökresultat även på detta.

Lösningen
Först skapade jag fyra fält som "plockade ut" bokstaven för den positionen (position 1-4), så att fältet bara innehåller en bokstav. Använde funktionen Left och Middle. Jag kallar de för char1-char4.

I posten där ordet är tex SBFG så finns alltså S i ett fält, B i ett annat osv.

Sedan skapade jag fyra globala sökfält, ett per position. I dessa skall man då kunna skriva S i position 1, lämna position 2 tomt och fylla i F i position 3 och lämna det sista tomt.

Sedan skapade jag fyra beräkningsfält som fungerar sålunda: Om sökfältet för positionen innehåller något, så returneras sökfältets innehåll. Är det tomt returneras istället hela alfabetet och alla siffror på varsina rader.

Sedan skapade jag en självrelation (en relation till samma tabell) baserad på sökfälten (beräkningsvarianten av dem) på vänster sida och på höger sida char1-char4-fälten.

Slängde in dessa fält och en portal i en layout och jag kan nu söka och även här är resultaten ögonblickliga. I portalen får jag fram alla poster som innehåller de bokstäver jag anger i de olika positionerna direkt.

Coolt!

Återkommer med bilder när databasen indexerat ytterligare en grej...

Här kommer en bild av hur det kan se ut. Att skapa ett fält som skall räkna ut antalet hittade poster har varit svårt. Även om jag ställer in att indexeringen på det skall vara global, så försöker FileMaker göra någon update på alla poster ändå, vilket, även om datorn får pyssla med det hela natten, inte blir klart.

Jag trror en förändring i fältet triggar en "tag bort något"-funktion, vilket som Richard rapporterar är sjukt långsamt i FileMaker. Man saknar verkligen SQL-kommandot Truncate table eller tag bort index som också finns i SQL.

Här kommer lite bilder av databasen sökfält och portal för att visa resultatet, fältdefinitioner, relationsdiagrammet och hur relationen är inställd. Resultatet är nästan ögonblickligt, någon sekund från det man lämnar ett sökfält tills portalen visar vad den hittat.

Beräkningen som inte syns i sin helhet i fältet RelationsSökningsBokstav01_C ser ut så här:

If ( IsEmpty ( SökfältBokstav01_G ) ;
"A¶B¶C¶D¶E¶F¶G¶H¶I¶J¶K¶L¶M¶N¶O¶P¶Q¶R¶S¶T¶U¶V¶X¶Y¶Z¶Å¶Ä¶Ö¶0¶1¶2¶3¶4¶5¶6¶7¶8¶9¶0¶" ; SökfältBokstav01_G )

Nja, jag har ju för mycket att göra egentligen, men du har rätt, jag börjar se något samband här...

Jag förstår dock inte riktigt varför den inte skulle vara applicerbar, man kan ju skapa olika typer av sökningar baserat på samma relation. Du kan ju lägga till ett sökfält som innehåller hela numret med en * för varje position man inte har, sedan splittas det inmatade upp av ett manus och de olika "riktiga" sökfälten fylls i som de skall. Längden, vilka grupper numret har osv, kan man ju låta det manuset avgöra? Eller missar jag något?

Att denna lösning finns hindrar en ju förstås inte från att söka på * och @ i hela fältet som jag visade tidigare. Att vänta 10 sekunder för några miljoner poster kanske inte är något större problem?

Appropå prestanda i Filemaker Pro databaser som sägs vara långsamt så hittade jag just denna roliga video. En FileMaker-utvecklare har laddat ner hela Wikipedia (ca 18 Gb), importerat den till ett antal tabeller i Filemaker som sedan relaterats till varandra.

Sedan har han använt en egen liten programvara för sökningar (som alltså bygger på funktioner inuti Filemaker, ingen plugin, möjligtvis på något liknande sätt som ovan), just för att bevisa att Filemker INTE är långsamt på att söka*.

SeedCode: fmSearchResults : Searching Wikipedia in FileMaker (2.12 min)

Vill man göra något liknande själv så finns allt att ladda ner här:
wiki.dbpedia.org : About

* Att radera poster är däremot sjukt långsamt...)

Tillägg: Vi har även diskuterat prestanda även i denna tråd:
http://www.99.se/filemaker/247628-optimera-foer-hastighet.html