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.

[Iain17]

I am by no means an expert on databases but this is just an idea.

What about two tables. One that holds all of the serial numbers and nothing else. The other table holdsall of the data, with the primary key being the foregin key in the other table.

That may be quicker for checking if serial numbers exist, though i am not sure.

[Iain17]

An extension on my last thought.

You could divide up the serial numbers. Sort of index them

e.g.

table 1

10001234
10001235
10001236
--------

table 2

11001234
11001235
--------

table 3

12001234


In your program you store the range of each table as a constant. Then when you need to check for a serial number you check a much reduced table.

And when you add one, you make sure you insert it in the correct table.

Just a thought.

[Zeee]

lain17

Ok, i'll check it out...but how about the Generating part of the program? The SQL's criteria is a date range (eg SELECT * FROM MyTable WHERE TDate BETWEEN #01/01/2000# AND #06/01/2000#).

[Paul282]

This could be a dumb question, but is the DB indexed? you can create indexes on any or all tables which dramatically increases search time (adds to the file size a little though)

No chance of throwing it into SQL server 7?

At work our billing system is on SQL7.x and the improvement (even over MSSQL 6.5) is huge, searches in fields that are indexed for tables with millions of records take seconds even though the DB is live with software writing to it heavily, I have ASP files that call records based on account number almost instantly. the server just runs on a PC NT system. Searches on non-indexed fields takes a while though.

If you had stored proceedures then performance increases again

If it's important, go to sql server, it's a piece of piss to administer too, any joe average with a 10 minute guide could create a DB and import from access with it.

[frank ashley]

paul282's right, SQL 7 is a doddle to administer (and it looks v.good on your CV).

Is your DB in 3NF? If not it really should be as this is the whole point of relational DBs. This will speed it up hugely.

[Zeee]

Paul282,
Yes, the database is indexed particularly the SerialNumber field and the Date field. Regarding the SQL Server, the company I'm working for does not have the resources to setup one.
Thanks for the info though.

[frank ashley]

Zeee,

You don't have to use the enterprise vn of sql7, you can use the desktop vn.

[Zeee]

frank ashley,
What's a 3NF?

[Paul282]

1 Winnt PC, normal home user spec ($1-2k max)

how big is the company?

[frank ashley]

3NF - Third Normal Form.

Its another TLA thats fantastic to use.

In short, 3nf is the process of separating your data into tables so that each table holds values that are similar, they will therefore be smaller.

[Zeee]

Paul282,
The company who hired me to develop this program is just a small data processing firm with no networking capabilities.


frank ashley,
3NF is one of the processes you use in normalization or data modeling right? BTW, what's TLA?

[Paul282]

We don't really have control of our schema for that system, the telephony hardware vendor dictates that. It's adequate but we would have preferred Oracle on Solaris (Oracle on NT is not that special but it's apparently pretty good on solaris)

Zee, Question.

Access can have a bit of a problem with Memory when the DB get's too big, does the Hard disk work hard (paging) when the select statement is running? If so try upping the system memory and VM of the system. Maybe borrow some from another system temporarily to see if the speed increase justifies the cost of the memory.

How much memory is in the system and how big is the mdb file?

[Zeee]

Paul282,
The system memory is 64MB while the database size is 764MB.

[Paul282]

No chance of taking that to 256?

[frank ashley]

Zee,

yes, db in 3nf = normalised db

A db with one table and 6m+ recs in that table doesn't sound right to me.

tla = three letter acronym!

Isn't there an option in Access to 'compress' the db. Have you tried that?

[frank ashley]

Zee,

You don't have to have a networked pc in order to run sql7 desktop.

I run it at home myself, first on w98 then on nt4 now on w2000.

[Paul282]

don't compress the DB in access, it'll slow it down.

RAM RAM RAM! There's no such thing as too much RAM !

What's the DB schema? Columns, and datatypes?

[frank ashley]

paul282,

as an aside, I thought that 'compress' in access would remove all the crap, or does it 'zip' up stuff?

i don't use access so i'm just curious

[Paul282]

To be honest I haven't bothered to read the help! I just always assumed that it was data compression on the mdb file. If it was just removing white space and fragmentation then I guess you would see improvement.

Still from the description the DB only get's written to in large blocks and no constant updates or deletes so fragmentation would be minimal. (although HDD fragmentation would kill performance, so that should be checked)

I'm a little suspicious of the indexes, if in the right place the effect should be large. A select statement on a few rows in 6 million still shouldn't take more than a couple for minutes in access.

Still think they'd be better of with NT and SQL server. You don't need a network for that. 128MB ram would be nice though.

[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.....

[frank ashley]

Where I am (UK) I don't think that sql7 is bundled with office2k.

What you might be referring to is MSDE. This is the sql data engine. This doesn't come with the fancy front end (enterprise manager) which makes everything drag/drop, cut/paste etc.

Installing sql7 is a breeze, don't worry.

Good luck & have a nice weekend.