Help in accessing 6M records.

[Zeee]

Good Day!

I have a program which accesses an Access2000 database
which consists of 6 million plus records(and still growing..) in a single table. It takes more than 30 mins. to do a SELECT statement eventhough the database is in the local computer's hard disk. The SELECT statement's criteria is a date range and I don't think making the date field an index will help either. The archiving for this database is annually and the 6 million records are only for the months Jan.-June! The program by the way, is a Serial Number checker, you input a serial number and it checks if that serial number already exists in the database. I don't
think that dividing the single Table into smaller tables (on a per month basis) will help since the program must search for all the records in a whole year! Basically, this program's main functions are Search and Generate data. I'm using ADODB.Recordset.

Is there any solution to this problem? Please note that I will still use the current setup(i.e. the program will execute an SQL statement on a local database on a local computer).

Your comments are highly appreciated.

[kgersbach]

What are your reasons for not indexing your search criteria?

[Zeee]

kgersbach,
I had already indexed the search criteria, still it's very slow.

[joedoer]

I don't understand something here.
You say you're inputing & searching for a
serial number but the select statement is for
a date range?

[Zeee]

joedoer,
Aside from inputting and searching Serial Numbers, I still need to generate records where the criteria is a date range(eg SELECT * FROM MyTable WHERE TDate BETWEEN #01/01/2000# AND #06/01/2000#). It's another feature of my program.

[joedoer]

My experience with Access is that it just does not
handle large amounts of records very well.

Are the serial nbrs and dates added sequentially?
That is, can you see that serial nbr 100 is on a date
before serial nbr 200, etc. consistently.

If so, why not break the tables into logical groups
by date and use some logic to tell the sql statement
which table to run against.

Otherwise, there's always SQL Server, as people always tell
me.

[Zeee]

joedoer,
Thanks for the info....i think i'll try this tip...but if you have any ideas aside from breaking up the table then do tell me ok?

[JasonGS]

If it were me and there is no possible way to break up tables (which could be seamless with some basic SQL) I would migrate to Microsoft SQL Server or Oracle (personal edition free to download).

[Zeee]

Everyone,
Basing on your feedbacks, I think I'll try using SQL 7(desktop version, I assume its the same as the one bundled with Office 2000 right?) and install it in the local machine. To add, can you tell me any sites where I can get instructions on how to set-up SQL 7.....I really need the info....Thanks a lot to all those who shared their thoughts.....

[JasonGS]

It is honestly really simple, it might be a good idea to purchase a SQL Server 7 / VB book, but as far as connecting to the database, all you have to do is change your ADO connection string to something similar as below... (assuming you have referenced MS ActiveX Data Obj. 2.x to your project...

Dim adoConn As New ADODB.Connection
adoConn.Open "Driver={SQL Server}; Server=COMPNAME; Database=mydatabase", "Username", "Passw0rd"