When will access be slow?

[David_Skov]

I know it sounds like a stupid question but fact is that I'm thinking about making a database with retaildata from multiple retailstores in DK. But will the Access database be slow when dataamount gets beyond 1Gb of data or maybe even before?

Is there a way to avoid this? Can I make several databases an combine them? I ask because I need to know if I should start at all or seek for better solutions.

Can you give som tips on how to optimize speed. And is it better to make several queries instaed of one query with many operations inside? (Here I talk about formulas that are executed as part of the query)

Tx for your help ray:

 

[Norie]

dataamount gets beyond 1Gb of data or maybe even before?

I don't really think you should use Access if you expect that much data.

Are you sure it's actually going to be that much?

What type of data is it going to be?

As to speed that could depend on your computer setup (processor, memory, network connections etc) as well as the amount of data.

EDIT

I've had a look at specifications in Access 97 help:

Attribute                   Maximum
Database (.mdb) file size   1 gigabyte. However, because your database can include
                            linked tables in other files, its total size is limited
                            only by available storage capacity.

 

[David_Skov]

It will be data on monthly periods on SKU basis for each retail. Just to give an idea. I have turnovers for each SKU in 1 retail in DK for 2 month. That makes 2Mb (compacted) which isn't much ofcourse, but I aim to get atleast 6 different retails and for 3 years of data (2002-2004YTD) and forward.

dataamount gets beyond 1Gb of data or maybe even before?

I don't really think you should use Access if you expect that much data.

Are you sure it's actually going to be that much?

What type of data is it going to be?

 

[Norie]

It will be data on monthly periods on SKU basis for each retail. Just to give an idea. I have turnovers for each SKU in 1 retail in DK for 2 month. That makes 2Mb (compacted) which isn't much ofcourse, but I aim to get at least 6 different retails and for 3 years of data (2002-2004YTD) and forward.

But will you be using all this data?

Will some of it not be things like products, product lines etc?

BTW what is SKU, is it Stock Keeping Unit?

 

[bat17]

Also, unless you have split the databse into front and back ends most of that 2mb is probably fixed overhead of the db not stored data.

peter

 

[Norie]

Peter

I think David is referring to the data he receives.

I suppose it could from an Access databse but it could just be raw data.

 

[David_Skov]

Yes, it's very important data in our salesorganisation. Yes SKU is Stock keeping Units

The data we already use is the minimum amount. We have:

Retail - Retailstorenumber (uniqe) - Retailstore name - EAN - SKU - Sales kr. - Sales Units - Margin kr. - Margin in %

All part of the data we use.

Enough info?

But will you be using all this data?

Will some of it not be things like products, product lines etc?

BTW what is SKU, is it Stock Keeping Unit?

 

[Andrew Fergus]

Do you know how many records you expect to receive / download every month?

For some time I worked with a database that was receiving 10,000 records per day and it held together quite nicely for some time - once the main data tables got to about 1.5 million records the performance went downhill very fast. I then reduced the data in the main table by summarising the oldest data, writing the oldest records to an archive database and over-wrote those records in the main data table with summary records to preserve the high level reporting.

There are a couple of things you can do to slow down the build-up of data - you can put the store name into a master table (with the store number) and remove it from the main data table plus the margin% should be a calculated field on any reports you produce.

If EAN is the barcode number then I would question whether there is a one-to-one relationship between the EAN and SKU numbers - do both of these need to be stored in the main data table?

Hope this helps, Andrew

The data we already use is the minimum amount. We have:

Retail - Retailstorenumber (uniqe) - Retailstore name - EAN - SKU - Sales kr. - Sales Units - Margin kr. - Margin in %

 

[David_Skov]

Well, not exactly since I haven't started to gather all into one base

But atleast 50000 records per month if I add up most important retails. That will give me around 3 years of data before it gets really slow ^^

I will try to use your recommendations and since your base recieved 10k each day I suppose you know what You talk about

The SKU name isn't important that's right so that can be removed and the Storename in a small database of it's own sounds like a good idea too

Would it make any sense to create a database for each retail and then gather the data in one query? Is it possible at all?

I mean if possible I would be able to handle data faster or?

Do you know how many records you expect to receive / download every month?

For some time I worked with a database that was receiving 10,000 records per day and it held together quite nicely for some time - once the main data tables got to about 1.5 million records the performance went downhill very fast. I then reduced the data in the main table by summarising the oldest data, writing the oldest records to an archive database and over-wrote those records in the main data table with summary records to preserve the high level reporting.

There are a couple of things you can do to slow down the build-up of data - you can put the store name into a master table (with the store number) and remove it from the main data table plus the margin% should be a calculated field on any reports you produce.

If EAN is the barcode number then I would question whether there is a one-to-one relationship between the EAN and SKU numbers - do both of these need to be stored in the main data table?

Hope this helps, Andrew

The data we already use is the minimum amount. We have:

Retail - Retailstorenumber (uniqe) - Retailstore name - EAN - SKU - Sales kr. - Sales Units - Margin kr. - Margin in %

 

[Andrew Fergus]

But atleast 50000 records per month if I add up most important retails. That will give me around 3 years of data before it gets really slow ^^

Would it make any sense to create a database for each retail and then gather the data in one query? Is it possible at all?

Hi, if I were you I would have a crack at doing all three years in one database. If that didn't work, I would split each year into it's own table or separate database. If you split it by store it will be very difficult to recombine the data for analysis purposes. Mind you, that depends on whether you are analysing per store data or analysing cross store data.

Can the products be grouped in any way? If so, you may look at summarising the data for reporting purposes (like the example I gave in my previous post, e.g. sum the data for product groups and hold only the summary in the main table) and set up your data structures accordingly. The archive data can have all the details you want, the main data tables from which you will create all of your analysis will need to be as slim and trim as possible.

However, all things said and done, you will want a big processor, a fast hard drive, lots of RAM, a big swap file and plenty of patience for the impending system slowdowns from badly written queries (Yes, I did that too, I would often have to kick start my PC after it would hang for an hour :wink: ).

Good luck, Andrew.