Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Thanked 1,603 Times in 1,487 Posts
Re: Is Microsoft Access a 'proper' database?
I'm going to toss another stink-bomb into this conversation because in reviewing, I saw a statement that just ain't so. The comment was made that you might not want to use Access to host a database because of speed considerations. Speed vs. what?
Most systems I know do not compile SQL, they just semi-compile it into some emulation code that looks like, but is not, machine code - because there is no physical machine behind it with that instruction set and also because the "instructions" of this putative machine are not primitives - they are complex macro-instructions - like SELECT or INSERT INTO or UPDATE or DELETE that affect whole groups of data at one time. No, SQL is at most interpreted (not compiled) on even the ORACLE systems. So that leaves us with the question of "which brand-name DBMS interprets text faster?" That depends more on the amount of overhead in each interpreter than on the language being interpreted.
Both Access and ORACLE use tables with indexes for faster lookup. ORACLE's advantage isn't speed, here - it is SIZE. The indexes that "point" to data can be more than 2 Gb of equivalent address space. But the bigger address space actually SLOWS DOWN the way the "big boy" jumps around - because it has to manipulate that longer address a bit more - and in a way that depends on the O/S. The speed of searching, selecting, and JOIN operations depends on the efficiency of the search, selection, and JOIN code routines behind the scenes. ORACLE might do more (in the way of triggers and such), but if you want to compare apples to apples, you turn off the extra stuff before you run comparision operations. Odds are, Access will be able to keep up.
Both Access and the "big boys" use ANSI SQL so they interpret the same features. Whether you name something as a VIEW or a SELECT QUERY, it has the same purpose and still needs to be interpreted. The "interpretation" depends on the efficiency of the pseudo-code emulator. (More about that kind of speed in a moment.)
When you design forms or reports, you make lists of items that are visited by an image driver to draw text boxes, labels, combo boxes, toggle buttons, etc. This is common to both systems. When you use Access FE/BE or when you use VB FE/something else BE, you interpret each page / pane / screen of data - because you DON'T hard-code every control. You build a data structure, define the properties, and call subroutines with subject data structures as the actual arguments, and the subroutines (which ARE compiled) examine the object properties in order to display it. You don't use VB to directly draw any control. The "collection enumeration" algorithms in Access do the same thing - visit the collection of controls and dispatch to the correct drawing routine based on the control-type field. This sounds like I am belaboring the obvious - but I am wading through the weeds to make the point that the various methods every system uses all match up one for one at this level of detail. It is all more or less the same thing being done by different vendors' code. Where is the speed difference?
The "speed difference" between Access and the "big boys" is a chimera - a non-existent creature. Think about it: When you think about fast databases, you do you think about Access hosted on someone's PC? but do you at the same time think about "the big boys" on a multi-processor high-end dedicated server? The speed difference is in the platform and the O/S settings, not in the databases. I've seen the "big boys" run on small PCs and they are no faster than Access would be on the small PCs. When you take away the ability of the BE to use parallel processing, you see the REAL comparison and there is where you find the real speed differential, which is miniscule.
In closing, remember that whether you write in FORTRAN, BASIC, PASCAL, COBOL, P/L-1, or some glorified assembler, it takes the about the same number of compiled instructions to move data from point A to point B regardless of your environment. So when you want to tout the speed of your database system, remember that it is the platform, not the software, that gives you the appearance of speed.
Having said that, I acknowledge that if you ran Access on the hottest PC you could find, it would still be single-threaded because it was not written to use multiple CPUs or cores (unless it now does that and I just missed the advent of that option.)
I think I've become a sadistic sodomistic necrophile, because I'm pretty sure I've beaten this horse to death. (Apologies to Woody Allen and What's up Tiger Lily?) But I just couldn't let the "speed" chimera out there without challenging it.
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.