donderdag 22 oktober 2015

Excel: performance of hoe optimaliseer ik grote spreadsheets II

Excel en performance

Ik krijg de laatste tijd herhaaldelijk verzoeken om een blik te werpen op Excel spreadsheets die te traag en onhandelbaar zijn geworden. Men vraagt mijn dan als VBA expert, maar is VBA altijd de (enige) oorzaak?

Laten we eens de factoren op een rijtje zetten die invloed hebben op de snelheid.

Grootte van het bestand.

Dit is misschien wel de belangrijkste factor. Zodra een bestand erg groot wordt, kan het opslaan en openen erg lang gaan duren. Het XML formaat heeft bestanden weliswaar flink verkleind, maar tegelijkertijd is het grotere aantal rijen en kolommen ook een uitnodiging geweest nog veel meer gegevens op ter nemen.

Herkomst van de data.

Data kunnen verschillende herkomst hebben, bijvoorbeeld:

  • In Excel zelf; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.625 kb.
  • Koppeling met een ander Excel bestand.
  • Via Microsoft Query en ODBC; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.414 kb.
  • Via Power Query en SQL Server; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.536 kb.
Wat betreft grootte ontloopt het elkaar dus niet zo veel. Wel zijn data die via externe verbindingen binnenkomen zo te filteren dat Excel ook alleen die data bevat die uitgefilterd zijn. SalesOrderDetail gefilterd op ProductId 777 is nog maar 35 kb groot. Via een Excel eigen filter filteren maakt voor de bestandsgrootte vrijwel niets uit.

Excel formules.

Er is een aantoonbaar verschil in performance tussen verschillende functies die wel het zelfde resultaat hebben. Zo is de functie INDEX sneller dan VERT.ZOEKEN. Kunnen we het een en ander testen? Jawel! De voorbeelden zijn op snelheid van uitvoering te vergelijken met behulp van VBA routines. 

Excel draaitabellen

Draaitabellen kunnen een Excel bestand veel groter en trager maken. Een simpel voorbeeld met een draaitabel op basis van de tabellen Product en
SalesOrderDetail uit Adventureworks. Direct in Excel: grootte bijna 17 MB. Via PowerPivot: iets meer dan 7 MB.

VBA routines.

Veel VBA maakt een bestand niet veel groter en trager. Wel maakt het uit wat er met VBA gedaan wordt. Zo maakt het wat betreft snelheid enorm uit of we een kolom met getallen via een VBA loop procedure optellen of dat we daarvoor de reguliere Excel functie SOM gebruiken. Zo kan het binnen VBA veel schelen via WorksheetFunction een beroep te doen op reguliere Excel functies. Ook kunnen we de werklast slim verdelen: het rekenwerk in Excel zelf en de variatie van bijvoorbeeld parameters met VBA.

Bestandsformaat.

Excel kent de formaten XLSX, XLSM en XLSB. Ze zijn vergelijkbaar in die zin dat ze allemaal ZIP bestanden zijn. We kunnen de inhoud zichtbaar maken als we de extensie vervangen door ZIP en ze vervolgens openen. XLSB is niet XML-gebaseerd based maar in een binair formaat. En dat is vooral voordelig als je met grote bestanden werkt. 

XLSX en XLSM bestanden laden vier keer langzamer dan XLSB en het opslaan ervan durt twee keer zo lang. Het uiteindelijk bestand is anderhalf keer zo groot.  Getest met een Excel bestand met tienduizend rijen en duizend kolommen met simpele formules.

Bij een Excel bestand dat zijn gegevens via PowerPivot ophaalt, kon ik geen verschil zien tussen opslag in XLSB of XLSX.

Het enige echte nadeel van het XLSB formaat is compatibiliteit: als er gewerkt met software die XML nodig heeft in plaats van binair.

Alle formaten ondersteunen dezelfde functionaliteit.

Tips.

  • Volgens mij is het belangrijkste punt bij Excel dat je de inhoud beperkt moet houden. Doe je dat niet, dan kan zeker in combinatie met bepaalde berekeningen, kunnen de bestanden heel erg traag worden.
  • Als je gebruik maakt van externe gegevens uit een database, laat dan - in het geval grote hoeveelheden data - de berekeningen zoveel mogelijk in de database doen en niet in Excel zelf. Als we Excel zelf de berekeningen uit laten voeren zal bij elke verandering vaak heel veel formules opnieuw doorgerekend moeten worden.
  • Draaitabellen kunnen wel tot honderd keer of meer sneller rekenen dan functies als SOMMEN.ALS.
  • Gebruik PowerPivot voor draaitabellen. Uiteraard zorgt dit instrument ervoor dat de data in de database blijven. De draaitabel maakt het Excel bestand niet veel groter. 
  • Een normale draaitabel maakt het bestand wel veel groter maar dit is aan te passen door bij de Optie voor draaitabel de optie Brongegevens bij bestand opslaan uit te zetten.
  • Opslag als binair bestand, XLSB, verkleint grote bestanden aanzienlijk en maakt laad- en opslagtijd korter. 
Voor verder Excel tips klik hier.

Een reactie posten