Whats the maximum sensible size for an Access application?

[Medwar19]

Hi All,

I developed a nice little database for analysis of equipment log files.

The project proved very useful and people started sending me more and more data.

It turned out I ran up against the 2GB limit quickly so I split the db and started a db for each piece of equipment using a front end db with linked table. Life was good.

Currently I have 12GB of data in linked tables and queries running through the front end on 30 million records.

Queries run really slowly (couple hours at least) and clearly will only get worse with more data ...

I'm wondering if Access is now a sensible architecture or if I would get better performance from an SQL server implementation. Our corporate network is really slow so I'm keen to host the data on my PC and if I understand correctly I cant run SQL without a server?

Another thought is to process data into OLAP cubes offline and then query the cubes instead of the tables.

I'm way out of my depth here, only 3 months using Access/VBA and would really appreciate some experienced users perspective of whats sensible.

Thanks,

Mark.

[Delerna]

You will definitely be better off by moving over to SQL server, it is certainly possible to run SQL Server on a PC, I do it on mine.
However you will not gain the full benefit of SQL server if you don't run it on a propper server. The server specs play a large role in any performance gain you will get from SQL Server. Indeed the specs will change according to the use you will put the database to.

If you want to try it out for a while before making a decision then I would suggest that you install SQL Server Express onto your PC along with SQL Server Management Studio Express.

Both of these are freely available from the microsoft site

[TheSmileyCoder]

If your running queries on 30 million records, it may also be worth spending some time on designing your index's properly, and looking into whether you have set all your field types correctly.

12 gb in 3 months sounds like a lot to me (others may think differently).
Have you tried doing a compact & repair from time to time? If your running alot of inserts, specifically in indexed tables, your database can grow quite fast.

Another option could be to split out old data in seperate backends (one for each month, or one for each piece of equipment), depending on what you need.

What information are you storing to get 30 million records?

[Medwar19]

Hey Delerna,

Thanks for the tips. I'd thought SQL Server had to run on a server OS. I'll install the express edition on my PC and see what happens with the dbs in SQL.

Thanks,

Mark.

[Medwar19]

Hey SmileyOne,

It's actually 12GB of raw data.
Each piece of equipment writes 1-2MB of data to a log file each day. Each line of the log file contain date,time, event, codes, additional metrics. Some of the files go back several years so it quickly adds up.

One of the queries locates 1 of ~300 possible event codes, parses data from the log string and collates statistics for causes over all records. Not sure if indexing will help. Will try.

I get much worse performance querying tables using relationships than pre-parsing data into tables and running queries directly on the tables....

Steep learning curve for me on relational dbs. I have no awareness of how many records can be processed in a reasonable time.

Thanks for your interest

Mark.

[Delerna]

Have you tried doing a compact & repair from time to time? If your running alot of inserts, specifically in indexed tables, your database can grow quite fast.

It's been so long since I used access as a backend I had forgotten about that.

[Delerna]

Not sure if indexing will help

Not sure with access as I had moved over to SQL Server before I had become aware of them.

Propper indexing will provide huge boosts to query performance .
I have seen queries running on unindexed tables taking many many minutes reduced to running within seconds, just by putting an index on the table.

Indexing is a topic that will be well worth your time spent getting familiar with if you do move over to SQL Server

[TheSmileyCoder]

I would try putting an index on event codes if that is what you often query for.

One thing I have seen before in log databases, is that some information is stored repeatedly. Most error codes for instance will have a description related to it. Do you store the description in each record, or have you made a seperate table of the descriptions, so that you only store each error description once?

[Marknut]

Sql server is definitely the answer for 30 million records, but you should research propper implimentation. You'll have a more robust tool, but still poor performance withought indexes and primary keys. You should also make sure your tables are holding the correct kind of data.

I.e. if you had a database of the united states, you shouldn't have 3 text fields in a table to identify a state, county, and city. You should have 3 tables, each with a code and text identifier. Then any other tables that reference a state, county, or city should only reference their ids. I hope that makes sense.

[Delerna]

Yes, in line with what Marknut said.
Database normalization is another topic well worth the time you spend getting familliar with

Edit
And in the scheme of things, a good understanding of it should come before indexing.
You may or may not already know these things?

[Medwar19]

OK great!

Thanks for all the replies. I indexed the error codes tonight which didn't seem to make much difference. Will look into your other suggestions over the weekend.

Thanks again, really appreciate your time to reply.

Mark.

[TheSmileyCoder]

Its kinda off the topic about the size of the database, but in your queries do you perform queries on memo type fields? (and do you have memo type fields in your tables?)

[Medwar19]

Hey SmileyOne,

Funny you should ask!

Each line in the log file has date, time, event code and a number of parameters (dependent on the specific code) linked together with descriptive plain text in localized language.
Lines go beyond the 255 text data type, up to 350 chars so I have to use memo data type.

I realized I needed to get into a more efficient format so during the initial log import I parse out the date, time, event codes and params into columns in the table and query on those text/number fields rather than querying the memo field.

I spent the last few days creating relationships to get rid of the text fields, replacing them with IDs to lookup tables see if that improves performance but am finding it difficult...

The lookup tables for equipment (Serial number, location etc) and event codes (12 char text, description, meaning etc) was obvious. I now have this information related to logs by ID number but cant decide how to represent the event code parameters as the number of parameters varies by event code.

Should I have a table for all event code parameters (will waste a lot of space) or a separate table for each event code type (will mean >500 tables to be created and related).

I'm googling for tips on normalization now. My Labor day weekend is shot..



Thanks,

Mark.

[TheSmileyCoder]

I doubt it would be a good idea to create over 500 tables. The amount of work to create it and maintain it would be way to large.

How many columns do you have in your "main" table? And how do you query it? What do you query by(your Where clause for instance)



One thing I suddenly (think I) remember about SQL express is that is has a limit on 4gb per datafile (but if I recall correctly you can split the database in multiple datafiles). Im not really sure on this point, just something you should research in advance.

[Medwar19]

OK thanks. Single parameter table it is.

I'd thought I should get the db setup and working in Access before considering SQL just because of the extra learning curve. I also use a lot of VBA for string parsing and environment setup (tables, linking, formating) so need to think about how all that will happen in SQL.

Thanks again for your help. I'm real close not to something workable.

Mark.