Click
here to return to The Fortress. I've been thinking about it and, while I am something of a database fundamentalist myself (ie I don't store any persistent data in Excel if I can help it, only in relational databases) I can see the practical benefits in some situations of Excel databases. Bottom line: people time is more valuable than processing time.
But, how's about a practical test of performance?
I have put together an experiment that anyone who wishes can download and fool around with. Here's the situation:
1) Two copies of a data set with 16,000 records. One copy is in an indexed table in an Access 7.0 database. The other copy is in an Excel XLA add-in.
2) A small workbook with two data retrieval routines. One uses DAO to grab a record from the Access table and to display it on the sheet. The other uses AdvancedFilter to grab the same record out of the XLA workbook. Both routines take their search criteria from a cell in the sheet. Elapsed times are displayed down to the 1000th of a second.
I've been passing this around locally and we have gotten some very interesting and surprising results.
The file, DBTEST01.ZIP, weighs in at about 605 Kb. Just unpack all three files to a single directory with PKUNZIP or the equivalent and open DBTEST01.XLS in Excel 7 or Excel 8. All the VBA is there in a single module to look at and take apart. Some interesting stuff in the VBA, check it out if you are wondering about the basics of data retrieval i Excel.
Your cost: 0. My liability: 0. Have fun and let me know if you download it, if it works for you and if you see consistent differences in performance between DAO and AdvancedFilter.
Sign The Fortress Guestbook
, while you're at it.Click
here to return to The Fortress.