Fastest MDB Database Access from VB4/32 
Author Message
 Fastest MDB Database Access from VB4/32

I've got VB4/32. I use the following code snippet to load databases.
It's the fastest way that my pea-brain knows how to load an MDB. (Note:
this is only a local application and not meant to be run over a
network.) However, this Microsoft DB technology is improving--I just
have a hard time transferring this new technology onto my own examples.
There's ADO, OLE DB, ODBC, RDO, etc. If I buy a book on this, I feel I
might buy one that's out of date and not use the fastest technique. So,
if anyone out there is kind, could they give this code a look and see if
I have opportunities for speeding it up? Also, opportunities to do fast
db queries would be useful. Right now, I use SQL in a string, SQL$, as
in Set TBL = DB.OpenRecordset(SQL$, dbOpenRecordset).

Public Sub ReadDatabase()
    On Error Resume Next
    Dim DB As Database, TBL As Recordset
    Set DB = Workspaces(0).OpenDatabase(a$)
    Set TBL = DB.OpenRecordset("ListOfNames", dbOpenSnapshot)
    TBL.MoveFirst
    If Err Then
        MsgBox "Database empty."
        Set TBL = Nothing
        On Error GoTo 0 'clear the previous error state if there was one
        Exit Sub
    End If
    Do
        Form1.ListBox1.AddItem TBL("Names"): TBL.MoveNext
    Loop Until TBL.EOF
    TBL.Close: DB.Close
    Set TBL = Nothing 'clear these two objects out of RAM
    Set DB = Nothing
    On Error GoTo 0 'clear the previous error state if there was one
End Sub



Mon, 26 Jul 1999 03:00:00 GMT  
 Fastest MDB Database Access from VB4/32

Hello,

IMOA

I understand that queries are started faster if they are saved as
querydefs in the database. This is because jet does not have check the
syntax of the statement. I have never done any 'time trials' myself to
see if this is true or not.

Could the database could have been opened exclusive?

If the recordset is going to contain few records or contains no
primary keys ( and is not required for update! ), then use a Snapshot
object. Else use a dynaset object, as this will use any primary keys
for access, i.e, does not return all the required fields back, thus
uses less memory. Open a dynaset type recordset dbReadOnly if it is
not required for update.

In your example you could have used the dbForwardOnly option when
opening the recordset. This makes access quicker, but as the const
name applies, can only be used for recordsets that will move forward
only, like the one in your example.

         Alan Davis, Southampton, England



Wed, 28 Jul 1999 03:00:00 GMT  
 Fastest MDB Database Access from VB4/32

Quote:

> I understand that queries are started faster if they are saved as
> querydefs in the database. This is because jet does not have check the
> syntax of the statement. I have never done any 'time trials' myself to
> see if this is true or not.

> Could the database could have been opened exclusive?

> If the recordset is going to contain few records or contains no
> primary keys ( and is not required for update! ), then use a Snapshot
> object. Else use a dynaset object, as this will use any primary keys
> for access, i.e, does not return all the required fields back, thus
> uses less memory. Open a dynaset type recordset dbReadOnly if it is
> not required for update.

> In your example you could have used the dbForwardOnly option when
> opening the recordset. This makes access quicker, but as the const
> name applies, can only be used for recordsets that will move forward
> only, like the one in your example.

>          Alan Davis, Southampton, England

Fantastic help, Alan. Man, I needed that. I understand that on the
Internet, you have to give a little to get a little. That's why I try to
answer a few questions and ask a few questions.

What I gleamed from your message was that I am not that far off track. I
have now switched to dbReadOnly and dbForwardOnly in use of the
dbOpenSnapshot when I can. As for saving the querydefs, I have never
been introduced to those. I suppose these are done through the VB 4 Data
Manager, which is where I create my tables, but nothing else. My
databases are never relational--no need right now--and the queries I do
are extremely simple, very flat-file. However, if I ever need to do
something like "find all with first name Jane," I will try to do what
you say here.

There are also two arguments I have to contend with in my push to get
out a shareware product. One says that if the database is small (no more
than 500 records, usually), I should stick to using binary file
databases instead of Jet. The other argument says that I should use ODBC
API access to my database if I want to get raw speed. For you and
everyone, here's why I've chosen not to implement either of those...

Binary File Databases -- how they work.
- When I say Binary file databases, I mean where one table of records
with fields are stored in a separate file (let's call it *.DBX), along
with a companion index file (let's call it *.IDX) that shows what you
display in your app's report view of the database, along with one hidden
field for the binary "index" byte of the DBX file.
- What do you do if you have variable size records? To contend with
this, I would think that you have two choices. 1. Copy the current
database file out as a different file each time a record grows larger
than what you had originally alotted. 2. Or, "tombstone" the current
record by marking it's binary index byte in the IDX file as byte 00,
which means don't read. Next, you create the new and larger record at
the end of the DBX file and change the IDX from saying byte 00 to now
saying byte xx where the new record is stored. On the next compaction,
tombstone record bytes would be forgotten about as the new database file
is created and the old one is deleted.
- What do you do if you delete a record? To contend with this, one
usually uses a "tombstone" method by marking the index in the IDX file
as byte 00, meaning don't read. On the next compaction, tombstone
records are expunged.

Binary File Databases -- Problems.
- The problems with this format is that advanced queries are slower than
Jet databases. Also, you have little means of data recovery if the bytes
get off hook. When I mean data recovery, I mean pulling it up in Access
and choosing repair, or by manually cleaning it up yourself. Also,
there's a long ramp-up development time as you try to iron out a
reasonable method of storage and retrieval.

ODBC API - Problems.
- ODBC API is something new I've read about in VBPJ. Basically, instead
of making DAO calls with "Jet objects" that get loaded and unloaded, you
override and use what these Jet objects use in the first place--ODBC
API.
- The problems with this are many, however. 1. The whole point of
encapsulation into Jet objects is so that the code is readable, less
complex, provides less chance for error, and the code can get passed on
easily when you work in teams. Using ODBC API makes the code into
spaghetti code with large chance for error, causing a possible GPF or DB
corruption. 2. Large ramp-up time as you try to bring meaning to the
ODBC API by developing it into a class module (aka "object") by which
you call (hopefully) proven methods. 3. I'm not certain, but there may
be no means for datarecovery if you use the ODBC API by accessing the
data in Access. (Someone should check on that.)

So, this is why I avoid ODBC API and binary file database programming,
unless someone else in comp.lang.basic.visual.misc gives me insight as
to how I might be "misled." (I have been before, surely.)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Mike

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Wed, 28 Jul 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Blazingly Fast VB4/32 Binary Database Example

2. Convert 32-BIT MDB Database to 16-bit database

3. VB4 32-Bit -> VB4 16-Bit Access 2.0 MDB -> Access 97 MDB

4. sequential add to access 97/vb4/32 database

5. Deleting a table from Access database - VB4 32 Pro

6. Unable to open Access 2.0 database from VB4-32

7. Blazingly Fast VB4/32 Binary DB Example

8. Blazingly Fast VB4/32 Binary DB Example

9. Can't delete all records from some tables - VB4/32 - NWind.mdb sample

10. How do I start an access database (mdb) from VB4 using OLE automation

11. How do I start an access database (mdb) from VB4 using OLE automation

12. VB 4.0 (32-bit) accessing password protected Access 7.0 database

 

 
Powered by phpBB® Forum Software