Max database size?

[siwalker]

Our Actinic access order database is now 145Mb and some operations are slow (eg. printing invoices) and some are downright flaky (eg. searching past orders frequently fails to find orders that we know are there).

Is this now too big for comfort? Our orders go back to when we started around 2 years ago. While we could archive off stuff older than 1 year, a quick sales analysis suggests that this would represent only 20% of the total orders, and at our rate of growth we'll soon be at the point where the last 12 months is more than 145Mb anyway.
I'm reluctant to archive off at all as customers regularly request a repeat of a previous order; and it's always useful to see what regular customers are buying.

I have run a database compaction (result: down from 160Mb to the present 145Mb).

What does the panel think?

Cheers,
simon

[leehack]

Archive all orders over 3 months old into a separate database, which is easily accessible and if you do need to take a look at one or two, then it is a quick process.

How often do you have the need to go back to 3-month old orders?

You have some space to go yet, but i'd definitely archive your orders.

Remove orphaned images too, they can build up taking unnecessary space.

[jont]

If you have developer you could set up a archive site and a trimmed "working" site.

May also be worth checking out some of Jan's stuff (mole end) as there may be something suitable to querying the full archive database for past orders without the need for opening Actinic (I don't use any so not entirely sure what they are capable of)

[pnagames]

Simon

depending on how fast your orders are growing it maybe worthwhile to investigate the option of "Replacing" the access database with an SQL server.

you can create all the tables of the database in a SQL server and link into them through the access db.

another option is to create a graphical interface in access similar to the actinic and have it linking to the "Archived" database

if you need any explanation on my suggestions please mail me.

[siwalker]

Hi Lee,
Thanks for your comments.

Archive all orders over 3 months old into a separate database, which is easily accessible and if you do need to take a look at one or two, then it is a quick process.

By switching mdb files under Actinic? Is there a better way, as we use the active order system pretty much all day every day.

How often do you have the need to go back to 3-month old orders?

At least once a day, usually 2 or 3 times.

You have some space to go yet, but i'd definitely archive your orders.

Why, as a matter of interest? Do you also see poor performance with large databases?

Remove orphaned images too, they can build up taking unnecessary space.

Presumably only in the general site diretcory, and they're not affecting actual Access performance. The size I quoted is purely for ActinicCatalog.mdb.

Cheers,
simon

[siwalker]

Simon

depending on how fast your orders are growing it maybe worthwhile to investigate the option of "Replacing" the access database with an SQL server.

you can create all the tables of the database in a SQL server and link into them through the access db.

Yikes. I already attach Actinic access tables to SQL Server to run some of my own reports and run some custom bulk upload code, but vice versa? Do you mean you can dispense with Actinic altogether and point the Actinic ODBC driver straight at SQL Server, or do you get Access to point at the SQL Server tables? If the latter then I'd be surprised if Access was able to manage this with good performance. Is this something you've done yourself? At a wild guess it's not supported by Actinic!

another option is to create a graphical interface in access similar to the actinic and have it linking to the "Archived" database

That would be nice, but I just don't have that amount of development time on my hands. My efforts on custom reports has proved that it's extremely hard work writing code against the eccentrically-structured Actinic database tables.

Cheers,
simon

[Jan]

Do you mean you can dispense with Actinic altogether and point the Actinic ODBC driver straight at SQL Server

This will not work unfortunately. Access and SQL server SQL are not fully compatible.

May also be worth checking out some of Jan's stuff (mole end) as there may be something suitable to querying the full archive database for past orders without the need for opening Actinic (I don't use any so not entirely sure what they are capable of)

You can use my One Stop Order Processing to look at Actinic sites quickly, it opens sites quite quickly and has some really nice order finding search facilities. You can also use one of my batch order processors to quickly mark orders over a certain age for purging, rather than having to do this in batches in Actinic.

Regards,

[Kermy]

My advice would be to simply install Actinic on another machine, dump a copy of your existing database on there, and then start anew with your live machine. You'll be as fast as can be and still be able to access old orders the few times you need to.

[leehack]

By switching mdb files under Actinic? Is there a better way, as we use the active order system pretty much all day every day. At least once a day, usually 2 or 3 times.

Create your own database which solely stores the old orders, from memory the orders table stores all you require, i am speaking from memory though as this has been discussed on the forum a few times before.

If you need to go back to previous orders, you just go to the orders database you have created, rather than the whole actinic database.

Why, as a matter of interest? Do you also see poor performance with large databases?

Access has its limitations with size and performance, most databases start on access and end up on SQL if they are growing substantially.

If your product lines are not growing vastly, in theory your access database can stay around the same size if you archive orders. Orders are the only thing that grow substantially on a site with a lot of orders, therefore if they are archived into an easy to access archive database, the performance and size of your main database remains the same and your archive orders database is the only thing growing.

As the archive grows, you just have separate databases, maybe one for each year or two etc.

Basic setup:

Actinic Database with past 3 months of orders.
Order Archive Database - Jan 2004 - Dec 2006.
Order Archive Database - Jan 2006 - Dec 2008.

Something along those lines anyway.

[siwalker]

You can use my One Stop Order Processing to look at Actinic sites quickly, [...]

Downloaded and reviewing now...

Cheers,
simon

[siwalker]

you just go to the orders database you have created, rather than the whole actinic database.

You mean using Access? - but you can't really review the order properly in that case. Especially when you're expecting non-IT staff to do it, which is an issue.

Access has its limitations with size and performance, most databases start on access and end up on SQL if they are growing substantially.

It's Actinic's handling of it that concerns me more - 145Mb doesn't seem a large amount of data, but there are some worrying glitches in Actinic.
Ending up on a proper RDBMS isn't an option here.

If your product lines are not growing vastly, in theory your access database can stay around the same size if you archive orders.

Fortunately our order volumes are growing all the time so it's an ever-growing issue! Product lines will probably also triple this year from 1500 to 4-5000.

I just hate the concept of archiving orders - fiddling about with archive databases has too many limitations. You can't create an order from a previous one; you can't see at a glance a customer's order history; you can't quickly find out what discount you previously gave customer X for his bulk order of Y. We're talking < 20,000 orders here which should be peanuts in any database.

Cheers,
simon

[leehack]

You mean using Access? - but you can't really review the order properly in that case. Especially when you're expecting non-IT staff to do it, which is an issue.

I just hate the concept of archiving orders - fiddling about with archive databases has too many limitations. You can't create an order from a previous one; you can't see at a glance a customer's order history; you can't quickly find out what discount you previously gave customer X for his bulk order of Y. We're talking < 20,000 orders here which should be peanuts in any database.

If the database is solely storing orders, then i agree it is peanuts, however the actinic database as a complete DB is managing many other things also - that is the issue. We all want added features and functionality, this has a knock on effect to size and performance.

The final result may be that you will outgrow actinic and have to move onto other solutions. Alternatively, or temporarily, why not create your own front end on the archive database, making a much more user friendly view and providing the funtionality your staff might require. A form allowing a search on a number of different fields and some queries detailing previous orders would be a great improvement.

[pnagames]

[QUOTE=Jan]This will not work unfortunately. Access and SQL server SQL are not fully compatible.

QUOTE]

Jan i am pretty sure that all access' sql commands can be replicated in an sql server.

sql commands / function on the sql server however are a different story (i found this in a very painfull way)

[pnagames]

You mean using Access? - but you can't really review the order properly in that case. Especially when you're expecting non-IT staff to do it, which is an issue.


It's Actinic's handling of it that concerns me more - 145Mb doesn't seem a large amount of data, but there are some worrying glitches in Actinic.
Ending up on a proper RDBMS isn't an option here.


Fortunately our order volumes are growing all the time so it's an ever-growing issue! Product lines will probably also triple this year from 1500 to 4-5000.

I just hate the concept of archiving orders - fiddling about with archive databases has too many limitations. You can't create an order from a previous one; you can't see at a glance a customer's order history; you can't quickly find out what discount you previously gave customer X for his bulk order of Y. We're talking < 20,000 orders here which should be peanuts in any database.

Cheers,
simon

simon

for what you mention here i would say your only option would be to move to a SQL server. (as your order will be growing constantly)

i will probably give a call to actinic tomorrow and see what can be done in relation to sql server and access and i will let you know.

[siwalker]

If the database is solely storing orders, then i agree it is peanuts, however the actinic database as a complete DB is managing many other things also - that is the issue.

Only the order-related tables are in use by order processing. In fact, as has been mentioned, all the data is in one just one order table. The fact that the design tables are present should have no bearing on the performance of invoice creation etc. The Actinic software is not managing other things concurrently and neither is the underlying Access database.

If I had the time and/or budget to commission custom code I'd simply implement my own order system in SQL Server - sucking the data out of Access into SQL Server is trivial, but writing the code to correctly and reliably do something with it is not.

I'm sure we're still relatively small-fry in Actinic terms and I'm still unconvinced that Actinic/Access has any fundamental limitation such that 20,000 orders should present a performance issue.

Looks like I need to rattle Actinic Support's cage...

Cheers,
simon