Archiving checkout database
I assist my sister-in-law in the use of checkout for her business. Her current checkout database (database.checkout) is 913.5MB and updates such as the latest update to the system which was supposed to take a while actually took 3 days to complete!
I would like to archive the checkout database and all information contained within it and then recreate the database (presumably empty) and of smaller size. I intend to do this by downloading / exporting the essential data out of the current database, renaming the database.checkout file to something else, then starting up checkout, presuming it will recreate the database. I will then recreate my store and import the data I want to transfer across such as products etc. I should then have a database with no customer or invoice records but products and suppliers. My plan is to do this on a quarterly basis and store the archived databases on an external drive or in the cloud.
I do appreciate that if I want to go back to any of the archived databases then updates will need to be applied to them before it will work with checkout, but my reasoning is that if I archive on a quarterly basis the database should never grow so large that it takes 3 days to apply an update.
So the question for this forum / support is ... is this sensible? What issues do you see with this? Is there a better way to do this that we have overlooked?
Thank you
2 Posted by tom on 15 Oct, 2012 05:52 PM
Hi Karen,
That is a massive database, mine is still @ 140 mb
I've tried to do the same ( start clean) but right now we can't export variations.
So I'm waiting for a new version that can do just that.
If you don't use variations you can start clean right away.
I think it makes very much sense to get a smaller database but that upgrade that took that long was that the special one with all the database optimization?
Perhaps you have a lot of large pictures in the database ?,if you go into the checkout directory with all the pictures you can easily see if there is a bunch that is too large. Just open them and re-size them, save them and then re-link them. Perhaps this would take a lot of MB's away.
kind rgds, Tom
3 Posted by karen on 15 Oct, 2012 06:32 PM
Hi Tom,
Thanks for your reply. There are no pictures in the database. (didn't even know you could add them). Checkout is used for a deli grocery shop where there are up to 75 customer transactions in a day and the product inventory is around 500 products. The database has every single transaction for the last 2 years and 6 months. so that's 68,400 customer transactions with an average of 5 product sales per transaction so that is 342,000 product sales. Would this account for the size of the database?
It does sound like a clean database is the way to go.
Thanks again. Other comments and feedback welcome.
Karen
4 Posted by tom on 15 Oct, 2012 07:04 PM
Hi Karen,
Pictures in the database are used for the sister program called Enstore.
This to get an presence/ or full web store on the internet.
I would stay away from that with a database your size ;)
My invoice number is now at 17000 so it still looks a little high but I do have way less products per invoice. Also I don't use checkout for purchase orders.
Support Staff 5 Posted by CO Michele on 15 Oct, 2012 08:23 PM
Hello Karen,
Based on the figures you have provided, I'm not sure why the database would be that large, or is taking 3 days to complete the upgrade. You can backup this database and save for future use to be restored from the external drive to the computers hard drive for reporting/viewing purposes. You can create a new store. However, at this time the only items that can be imported into Checkout are customers and products. Anything else such as vendors, would need to be entered manually.
We hope this information was helpful to you.
Kind Regards,
Michele
Checkout Support Team
6 Posted by karen on 15 Oct, 2012 09:39 PM
Thanks Michele. I do have a couple of questions for you.
is a vendor the same as a supplier. I have suppliers in the database. There are reports for suppliers but I have not come across vendors.
By saying "you can create a new store" do you mean create a new database as in my proposed solution surely if I create a new store using the same database it will just increase the size and not solve the problem (which is the enormous size of the database).
Clarification would be helpful.
Thank you
Karen
Support Staff 7 Posted by CO Michele on 16 Oct, 2012 12:39 PM
Karen,
Yes, a vendor is the same as a supplier. In Checkout these are suppliers, I apologize for any confusion. To setup a new supplier in Checkout, click on the Stock Room, then Manage Suppliers, then click + Icon at the bottom to create a new supplier.
You create a new store which is also a new database. To do this in Checkout, from the Welcome to Checkout Window, click on Manage Stores, then click Create to setup a new store.
I hope this helps.
Michele
Checkout Support
8 Posted by karen on 16 Oct, 2012 03:07 PM
Michele,
Thanks for the clarification. It is helpful.
I have a question about databases and stores. I currently have 6 different versions of my store visible when I open Checkout. This is due to the fact that I use Checkout on a mini-mac and a macbook. When I am in the physical location of the shop then both systems can obviously use the same database / store, however when I leave the shop in the evening. I make a backup of the store and restore it into my local drive so I can work on inventory etc at home or off site. When I return I then backup the store from the macbook and restore it into the mini-mac so the database / store with the overnight updated inventory is being used. There are some stores that I closed off purposefully for my end of year accounts so for example I have a store called 2010 and one called 2011.
My question is: Is there one database that contains many stores or does each store create it's own database. If so what does the file called database.checkout contain. Surely if each store creates its own database then I should have 6 copies of the database file.
Maybe this is why the database.checkout file is so large (913Mb) and terribly slow to work with.
Again I would appreciate clarification on this so I can decide how to proceed in archiving my database / store.
Thank you
Support Staff 9 Posted by CO Michele on 16 Oct, 2012 06:15 PM
Karen,
All Checkout stores created will all reside in the database.checkout file. This could be why your database.checkout file is 913.mb. However, the size of the database.checkout file will not affect a stores performance, and the size of one store will not affect the performance of another. store. The only things that will affect the performance would be the number of transactions, products, customers, suppliers etc. or having the stock room open while performing other transactions, all in that store only. You would backup (archive) each store individually, not the database.checkout file.
When the Checkout software is updated, this is done on each computer (Checkout Software installation). The upgrade process is an upgrade of each store created.
I hope this helps.
Regards,
Michele
Checkout Support
10 Posted by karen on 01 Nov, 2012 04:08 PM
An update to this situation.
We removed old stores from the database and this reduced the file size to 387 MB. (still large). We then exported the product and supplier data. We closed checkout. Renamed the database.checkout file, then restarted checkout. Checkout recreated the database.checkout file (30MB), we imported the product (not totally sucessfully - I have started another thread about that). The database is now 88MB.
Once we are happy that everything is up and running properly we will apply the new update.
Thank you, Michele for your assistance with this matter.
11 Posted by tom on 03 Nov, 2012 10:34 PM
Thanks for the update Karen !
Now that it is 88 mb big is it really a lot faster in everyday use ?
Love to hear if it really improved a lot.
I noticed that some of the fast sellers in my database got slow to checkout.
I 'solved' it by deleting them and recreate them. But I haven't checked if this has been solved in the latest versions. I'm still on the August release.
rgds, Tom