Image of Navigational Panel mapped to Contents / Home / Search Jet Engine
Tech Support Question
Image of Line Break

Q. I have a speed issue with the Jet Engine. I have a simple VB program that searches for part numbers. To start... the part number file was a text file with about 30000 records. The speed of the search was extremely fast (about 1 second).

Then I moved everything to the Jet Engine. I made my text file into an access file and opened it as a dynaset. Now it takes 30 seconds for each search. Is there any cache settings or anything to put the ENGINE back on the JET?

Thanks,

From: Darren Mathis
Email: darrenm@ix.netcom.com

A. You need to modify the table in question so it has a ts (timestamp) field, Your problem probably stems from the fact that you are using a Dynaset to do your search. There are much more efficient ways to do this. The first thing you should do is have a look at the table that holds all these 30000 rows. You need to make sure that anything you are going to be searching on has an index. This will help speed up the time to look for these things.

Secondly you should be opening the table directly as a table object instead of using a dynaset. When you open a dynaset you lose the information that the JET engine keeps about the table to help it work quickly with it. This includes index information.

Additionally the use of a Table object will allow you to use the Seek method on the table which is the most efficient way to do a search. For example, if you wanted to search for a value in a field called ID, then you would create an index for the field ID, let's say this index is called "IDSearch". You could then execute the following code to do a very rapid search of the table: 

    Set BigTable = MyDB.OpenTable("tblLarge")
    BigTable.Index = "IDSearch"         ' Set the current table index
    BigTable.Seek "=", 15               ' Perform the search

This will search for the first record where the value in the ID field is equal to 15. If there is no record that matches your search then the NoMatch property of the table will be set to true. This is definitely the fastest way to search through a table. It's also very flexible if you define the right indexes.

Image of Arrow to Previous Article Image of Arrow to Next Article

[TECH SUPPORT TOC]
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]