[VBA] optimalisatie van search functie

[gizmo9003]

Ik heb een functie geschreven, die zoekt naar de waarde van een bepaald attribuut (searchtag) in een XML bericht (message) en dat teruggeeft. Het is geen optie om de XML in excel zelf te mappen, aangezien het om duizenden berichten gaat, die verschillend opgebouwd kunnen zijn (niet alle attributen / nesting zijn hetzelfde, kan per bericht anders zijn).

De code werkt prima, maar ik vroeg me af of hier nog iets aan te optimaliseren valt. Voor zo'n 5000 berichten gaat het nu nog aardig (+- 10sec), maar ik verwacht in nabije toekomst veel meer berichten te krijgen waar deze functie op losgelaten moet worden.

code:

Public Function search(Searchtag) As String

        startpos = InStr(1, Message, Searchtag) + Len(Searchtag)  'Set position to first character after the found SearchTag
        Length = InStr(1, Mid(Message, startpos, 33), "</") - 1      'Look for the closing "</" tag within 31 characters after start (31=max length of attribute value)
        If Length <> -1 Then                                         'If tag was not found, length will be -1
            search = Mid(Message, startpos, Length)                  'return the result in the
        End If
        
End Function

Ideen voor optimalisatie? (mag ook in een andere oplossingsrichting liggen)
Googelen op de gebruikte standaard excel functies (zoals Instr) leert me dat er optimalisatie mogeiljk is als je de startpositie kunt verhogen, maar op voorhand heb ik daar geen kennis over.

[RobIII]

Allereerst: XML benader je / behandel je met een DOM of desnoods SAX parser. Niet als een string. End-of-story. Dat is vragen om problemen.
Daarbij zegt ~10 sec over 5000 berichten weinig als je niet aangeeft hoe groot de berichten bijv. zijn noch waar ze vandaan komen (SSD of bijv. ergens van het web?).

Ik heb met enige regelmaat >1.000 tot zelfs 10.000 (of rond die koers) XML berichten / sec. van ~20KB op een doorsnee workstation met 16GB ram en een Xeon 2.3Ghz en SSD verwerkt zonder al te veel problemen met een doodnormale DOM parser in C#. Voor VBA zul je waarschijnlijk moeten kijken naar MSXML.

Wat houdt die "searchtag" verder precies in?

[ Voor 17% gewijzigd door RobIII op 21-12-2016 23:31 ]

[Gomez12]

Wat moet je functie doen?

Want wat ik hier lees zoekt niet naar een attribuut binnen een xml, maar naar een stuk text in een iets waarbij er binnen 31 tekens een vrij random volgorde van karakters moet volgen.

Als ik het goed lees is het volgende gewoon goed volgens jouw code :
<tag>Dit hoort geen searchtag te zijn maar <b>enkel</b> een stukje text met wat bold tekens erin</tag>
Terwijl het volgende volgens je code niets zou opleveren terwijl je het wel wilt volgens mij:
<tag attr="searchtag"/>

Maar om te beginnen zou ik eens naar een andere taal gaan kijken dan VBA. Afaik kent VBA geen echt makkelijk te gebruiken XML-componenten, terwijl dat zo ongeveer de basis is : Je probeert XML te lezen, gebruik daar XML tools voor.
En met die nieuwe toolset vervalt ook alle commentaar op je huidige code want die komt compleet te vervallen.

Ik kan nu wel gaan vragen hoe je een message inleest (dat is zeer waarschijnlijk nu je meest vertragende factor, het openen en compleet inlezen van een message om het daarna weer te sluiten en volgende te pakken) of als micro-optimalisatie (nog niet boeiend bij 50.000 berichten, maar bij 500.000 berichten en verder kan het opeens wel gaan meetellen) dat je geen onnodige length variabele moet definiëren als je die in de volgende regel weer kwijt raakt.
Sowieso definieer al je variabelen voordat je ze gebruikt, scheelt de computer gewoon een paar lookups.
En zo kan ik nog wel even doorgaan, alleen het is zo zinloos want je basis is momenteel gewoon fout.

[BarôZZa]

Hoewel de oplossing niet ideaal is en ik geen VBA expert ben kunnen we natuurlijk wel conceptueel uitleggen hoe je het kan optimaliseren .

Simpelweg een XML parser gebruiken zou het namelijk niet versnellen. Grote kans dat het zelfs nog trager wordt als je simpelweg ipv een eenvoudige string search de XML gaat parsen.

De traagheid komt doordat je bij elke search door alle files heenloopt en zoekt naar één tag.

1. Optimalisatie 1 - de waarden in het geheugen opslaan

En efficiëntere manier is om eenmalig door de files te lopen en de waarden ophaalt van alle tags zodat je een lijst krijgt van messages met daaronder de tags en de waarde daarvan. Je moet zelf even uitzoeken wat het meest geschikt is om voor de lijst te gebruiken in VBA: multidimensionale arrays, arrays van objecten, collection oid.

Als de structuur niet uitmaakt en het puur om de value gaat dan moet dat nog makkelijk kunnen met de string functies. Kijk ook eens naar split en regex. Waarschijnlijk dat als je 'regex xml text values' googlet dat je al een complete regex krijgt plus comments dat je XML niet op die manier moet uitlezen . Maar als de XML files eenvoudig blijven kan je natuurlijk gewoon yolo denken voor een keertje (zeker voor een eenvoudige search die alleen intern gebruikt wordt).

Als je zoekt loop je door de lijst van messages heen en kijk je of die tag erin staat. Waarschijnlijk zit je dan al ruim onder de seconde per search. Uiteraard moet je zorgen dat deze lijst met informatie zo gedeclareert dat deze buiten de scope van de functie valt die het genereert, zodat deze te benaderen is door de functie die erin zoekt en niet weggegooid wordt zodra de functie die het genereert klaar is.

Maar uiteraard kan het nog sneller en beter:

2. Optimalisatie 2 - indexje

Bij 1. loop je nog steeds door een lijst en kijk je per item of de tag voorkomt, dat is uiteraard niet zo efficiënt. Een index is hetzelfde als een inhoudsopgave in een boek, dus wat je doet is dat je nog een lijst maakt. Waar je bij 1. een lijst met messages en inhoud hebt genereer je nu een lijst met de tags en onder elke tag zet je de messages(de key uit de lijst van 1.) waarin het voorkomt.

Bij het zoeken kijk je dus eerst in de index welke messages de tag bevatten en haal je daarna die op uit de lijst van 1.

Voordeel is ook dat je een lijst hebt met de beschikbare tags, dus die kan je in het zoekformulier stoppen in een dropdown, selectbox of tekstveld met autocomplete.

Uiteraard kan je op deze manier het ook eenvoudig uitbouwen zodat je niet naar de tag zelf zoekt, maar dat je ook naar de waarde ervan kan zoeken. Zodat je bijvoorbeeld kan zoeken naar wat er in de messages staat of van wie het is.

[ Voor 3% gewijzigd door BarôZZa op 22-12-2016 01:12 ]