Author Topic: Database Maintenance  (Read 868 times)

Offline jcmore

  • Newbie
  • *
  • Posts: 17
    • View Profile
Database Maintenance
« on: February 01, 2020, 11:27:07 PM »
Hello Ronald...I have a customer who has been using EZpower for 10 years(currently at V13). When they try to pull a report, say for example, sales of a product over the last month, it literally takes forever to produce the report and slows down the tills. I'm thinking this is a result of the size of the database but am unsure. They have never cleared or exported sales over that that 10 year period. I've read in the forum about exporting sales but mainly with reference to version upgrades. Is there any recommended database maintenance that can help to alleviate their problem?

Offline ronaldrwl

  • Administrator
  • Hero Member
  • *****
  • Posts: 1625
    • View Profile
Re: Database Maintenance
« Reply #1 on: February 02, 2020, 05:13:05 AM »
Go through this check list:
1, The hard drive is very important on the server.  Try doing a defrag.
2, The network is important on multi-station setups.  Do they have a good router?  They are very cheap.
3, Firewall and security software can slow things down more than anything.  Make sure the POS.exe program (ezPower) has full access privileges with any security software.  Otherwise, every file access can be checked and slowed down.  This has become more common in recent years.
4, Version 13 is limited but there are some tools you can use.  Open the Tools / View Optimize Points.  You can use the Clean button.  Also, look at the unpaid/open records.  If you have open records from early years that requires a lot more work for the software.  Closing up open sales records can help.

Offline jcmore

  • Newbie
  • *
  • Posts: 17
    • View Profile
Re: Database Maintenance
« Reply #2 on: February 06, 2020, 07:03:46 AM »
Thanks Ronald...I've gone through the items on this list and everything checks out up to step 4. Clicking on Clean identified a bunch of open records from years ago but there doesn't appear to be any way to deal with them from the tool and I couldn't find any info about this tool in the manual. Am I missing something? I ended up using Sales Control to list and print them all so I have all of the record numbers, dates, amounts and till numbers for them but can't figure out how to get rid of them. I tried opening a few on a Sales terminal and they do open, but the Void Sale button is greyed out. What would be the best method to get rid of them all?

Offline ronaldrwl

  • Administrator
  • Hero Member
  • *****
  • Posts: 1625
    • View Profile
Re: Database Maintenance
« Reply #3 on: February 06, 2020, 07:30:30 AM »
With version 13 you have to fix the sales directly from the sales screen.  Version 14 added more tools to close out old sales from the Optimize screen.
The Void option is grey if the sale is still open.  You can use the Clear option for those.
The sales that are greyed out have been closed but maybe not paid for.  For those sales your options are the Pay, Void or Edit them.

Offline jcmore

  • Newbie
  • *
  • Posts: 17
    • View Profile
Re: Database Maintenance
« Reply #4 on: February 06, 2020, 02:10:52 PM »
Thanks Ronald
There are many open records(about 100) so would like to be able to remove them in bulk. Does ver 14 have that ability? If so, is there a way to get rid of them without affecting inventory? I tried manually voiding a few from the Sales screen and found that it re-adds the inventory that was sold on the original record which means I have to go in and adjust the inventory back to the correct amount. A lot of work for 100 records so I just want the records gone. Is that possible with version 14? If so, I will order it immediately.

Offline ronaldrwl

  • Administrator
  • Hero Member
  • *****
  • Posts: 1625
    • View Profile
Re: Database Maintenance
« Reply #5 on: February 06, 2020, 03:46:53 PM »
It's easier in 14 but for closed sales you still have to view each one and click yes in the Optimize window.  There is no bulk delete on closed sales.  On open sales I believe it removes them automatcially.

Offline jcmore

  • Newbie
  • *
  • Posts: 17
    • View Profile
Re: Database Maintenance
« Reply #6 on: February 24, 2020, 06:45:47 PM »
Hi Ronald
Thanks for the tips. I have been going through open records and clearing/voiding them but found some weird ones. They are all record number 00000 and showing under register 0(which they don't have) and the year is 1899. I've attached a picture. Any idea what these are and how to get rid of them?

Offline ronaldrwl

  • Administrator
  • Hero Member
  • *****
  • Posts: 1625
    • View Profile
Re: Database Maintenance
« Reply #7 on: February 25, 2020, 05:52:55 AM »
Those are some bad records.  Did anything ever happen to the database, like moving it or importing sales?  Or bad crashes?
One brute force solution is to replace the harddrive with a Solid State Drive.  I've seen system using them and they are super fast. 

Offline jcmore

  • Newbie
  • *
  • Posts: 17
    • View Profile
Re: Database Maintenance
« Reply #8 on: March 15, 2020, 06:35:17 PM »
Hi Ronald
I've now completed all of the things your suggested and cleared all of the open records(except for the record numbers showing as 00000). The server has had a SSD drive in it for some time. There is no router in the network but instead have a 1GB switch and all devices are operating at 1GB. The firewall is configured to allow EZpower traffic but it still takes forever to generate a report(even a small one). Any other ideas drastic or otherwise?

Offline ronaldrwl

  • Administrator
  • Hero Member
  • *****
  • Posts: 1625
    • View Profile
Re: Database Maintenance
« Reply #9 on: March 16, 2020, 07:46:59 AM »
10 years old, it may just need a upgrade, computer and software.
Note: the servers we set up only have a SSD.  There is no mechanical Harddrive.
« Last Edit: March 16, 2020, 07:49:49 AM by ronaldrwl »